[ACCEPTED]-How To Avoid Repair With Keycache?-mysqldump

"Repair by sorting" uses the filesort routine, which 15 in turn creates several temporary files 14 (usually) in your tmpdir.

If your tmpdir 13 does not have enough space for them, it 12 will revert to "Repair by keycache". This 11 is extremely bad as it's much slower AND 10 creates less optimal indexes.

There are some 9 other conditions but I haven't identified 8 them.

Working out the size of tmpdir you 7 need for filesort() is nontrivial; the format 6 data are stored in the filesort buffer is 5 not the same as MYD files, it typically 4 uses a lot more space.

So if your tmpdir 3 points at a small /tmp (or tmpfs), you might 2 want to change it to a larger /var/tmp - if 1 that exists.

MySQL will use repair by keycache for MyISAM 10 tables whenever the maximum possible size 9 of the tables indexes are greater than the 8 value for the variable myisam_max_sort_file_size.

You 7 can calculate the maximum size of the index 6 by adding up the byte size values for all 5 keys in all the indexes and multiplying 4 that by the number of rows in your table.

Increase 3 the myisam_max_sort_file_size and your index 2 will be rebuilt using sorting on disk, rather 1 than with the slow keycache method.

I accidentially ran a repair table quick 51 on a new database which I had not set up 50 to be fast reg. myisam_max_sort_file_size 49 which was way too small compared to the 48 .MID file (which is 88279393280 byes large, about 47 88GB). The data file is 85GB. The table 46 is 1.2 billion records, consisting of an 45 ID, two dates, a tinytext ,a few bigints 44 and a double. My server (2GB virtual linux 43 running in a box under windows7) only have 42 one core of the 4 on the windows server, but 41 it is running 3+ GHZ. I was fearing this 40 "repair by keycache" event would take forever 39 - given horror stories with far smaller 38 tables.

Fortunately it "only" took 1 day 37 , 10 hours and 20.72 seconds to complete 36 the repair table quick operation.

What I 35 miss the most is some way of knowing how 34 far into the operation that mysql is, and 33 how soon it might be finished. This is still 32 unknown to me.

I have now changed my my.ini 31 file and double checked with df that I have 30 ample disk space for those large temporary 29 files.

Anyway.. my main point, which might 28 be very useful knowledge to the next guy 27 who falls into this trap.. is in fact... don't 26 panic! it might be slow, but it is possible 25 on rather sub-par hardware to get 1+ billion 24 records sorted out within a day or two. Got 23 three indexes, one on a date field, one 22 on a bigint field, and one primary on the 21 ID field.

I would've posted this as a comment 20 to one of the solutions, but I can't seem 19 to figure how to do this, with the user 18 interface here, so I'll drop it off as a 17 solution. Don't upvote me, it's just a note 16 that I would have loved to have here, I 15 was almost going to kill my "sort by keycache" thread 14 as I thought it could take a week or more. 2 13 days per billion records is manageable..

Edit: And 12 now, a repair table on the same database, but 11 with a large enough mysiam_max_sort_file_size 10 setting took 10 hours, 20 minutes using 9 repair by sorting. The most diskspace used 8 was about 250GB, but i had set myisam_max_sort_file_size 7 a lot higher, reflecting how much disk space 6 is actually free on the server.

Tracking 5 progress is hard. Disk space went up and 4 down while the individual indexes were built, but 3 there were hour long pauses where no changes 2 were made reg. disk space usage (as reported 1 by df).

Thanks Mark, Yes that is exactly what I 13 ended up trying and am seeing from the logs 12 that that's the reason it switched to "Repair 11 with keycache", was an out of space error.

This 10 is what I did to get my solution in place 9 as I will not go through the fact that it 8 was pointing to /tmp/mysqltmp/, which only had a max of 7 2MB.

So I did this:

mkdir /home/mysqltmp

chown mysql:mysql /home/mysqltmp

changed my tmp dir in my.conf to tmpdir=/home/mysqltmp/

Now if I use df -h /home/mysqltmp, what I see is that dir 6 has 285 GB available, so that really was 5 a nice sight to see, had plenty of free 4 space, plus I could see mysql was wanting 3 20GB easily. So what was taking me 12 hours 2 before now is complete in 20 minutes, that 1 is over 3 million records insert to index.

None of the solutions here worked for me: no 11 matter how much I increased the myisam_sort_buffer_size variable 10 or where I made the tmpdir variable point to, the 9 table always got repaired with keycache.

What 8 worked was to use the commandline utility 7 myisamchk:

myisamchk --sort-recover --sort_buffer_size=14G /path/to/table

where:

• /path/to/table is the path of the database file 6 without its extension (so, without the .MYI at 5 the end). It is by default located in directory 4 /var/lib/mysql/your_database.

• Change the buffer size from 14G to whatever 3 free space available you have.

As an added 2 bonus, it also displays the on-going progress 1 as it churns the data.

According to the MySQL Reference Manual, disk 9 space must be available "in the file system containing the directory where the original index file is located" (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_max_sort_file_size) -- this 8 applies to (at least) v5.0 and above. This 7 contradicts some of the above answers, that 6 claim that increasing the disk space for 5 the tmp directory would help.

I can confirm 4 the behaviour described in the Reference 3 Manual: temporary disk space is used where 2 the table's data (*.MYD) & index files (*.MYI) are 1 stored, but not in tmpdir.