MySQL Forums
Forum List  »  Performance

Re: Performance trouble at 35M rows
Posted by: Harrison Fisk
Date: November 27, 2004 09:21AM


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

Options: ReplyQuote

Written By
November 05, 2004 11:47PM
November 07, 2004 09:02PM
November 10, 2004 06:55PM
November 26, 2004 11:57PM
November 19, 2004 04:49AM
November 26, 2004 01:43AM
Re: Performance trouble at 35M rows
November 27, 2004 09:21AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.