Re: Performance trouble at 35M rows

[Harrison Fisk]

Hi,

When creating indexes and doing bulk imports on empty tables (LOAD DATA INFILE and such) on MyISAM tables there are a few settings you can tweak that make a very large difference.

First is increasing myisam_sort_buffer_size. You will want to make this as large as possible without going over a limit set by your available RAM or OS. Increasing this number temporarily to something like 1GB or 2GB before you create an index is a fairly normal thing to do.

Another couple settings that ties into this is myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Again set these to large sizes (10gb+) assuming you have free disk space.

When those 3 variables are set to large sizes, that allows MyISAM to do an optimization called Repair by sorting. The alternative method of doing things is called Repair by keycache. Repair by sorting is often hundreds of times faster, but does require more resources so it is only done if those variables are set high enough. If you check with SHOW PROCESSLIST; while the ALTER is running, it will show which of the two are going on.

Finally the last variable to be set is myisam_repair_threads. This controls how many CPUs it can use at the same time. Of course there are limits to this, but setting it to > 1 will allow it to use more CPU usage in general.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com