This would be another quick post. I have encountered this problem few times and I hope that this could help other people making database recovery faster and more reliable.
If you see long running process 'repair with keycache' in MySQL 'process list' during database recovery that means your mysqld have insufficient resources to use standard method - 'repair by sorting'. Repair by Keycache is very slow - 5x - 10x slower (or even worse) depending on your setup, hardware performance and database contents.
You have to change you configuration following way:
1. Your setting 'tmpdir' in my.cnf points to file-system with low space storage. Be sure that you have at least 2x or more availabe space than database backup alone (in some scenarios maybe more). On some systems, administrators creating separate file-system for /tmp dir, which is good idea, but mostly it's very small, therefore insufficient for large database recovery. In this case just change your setting for example to /var/tmp/mysql
2. Check permissions on file-system of 'tmpdir'. Usually mysqld running under 'mysql' user. This user need to have r/w permissions to that directory.
3. Another variables need to be checked:
myisam_max_sort_file_size (several times larger than biggest table inside database)
myisam_sort_buffer_size (try 64MB or more)
I'm not explaining everything. I'm just trying to advice which things need to be tweaked.
Any comments appreciated.
No comments:
Post a Comment