I have several large MyISAM tables (data file around 1.5 GB) which I need to optimize on a semi-regular basis because the index files are getting huge. The OPTIMIZE operation takes a really long time. The status of the query while it is running is "Repair by sorting". How can I improve the performance of an OPTIMIZE statement? Will increasing the sort_buffer help?
+1
A:
try setting myisam_sort_buffer_size in your connection, e.g.:
set session myisam_sort_buffer_size = 52428800
jspcal
2009-12-27 04:53:48
Is there a particular reason why this needs to be set during the session as opposed to the my.cnf configuration file? If I set the value to 1GB, would this 1GB be ALWAYS reserved, even if no optimize is in progress?
2009-12-27 05:04:53
Any operation that could use the memory offered would use it. You don't want to have more than around 80% of your RAM committed to MySQL (good rule of thumb). If you have tons of unused RAM you could change the setting in my.cnf, but chances are good that the RAM would be better used by other parts of MySQL (e.g. query cache). If you make all of your buffers large and the worst-case hits where all of the buffers really are used, the DB can grind to a halt as the OS starts to heavily swap RAM to disk.
Eric J.
2009-12-27 05:14:55
If I make a DB connection in a PHP script, then execute the SET session statement, then execute the optimize command, would this use the new value of myisam_sort_buffer_size? After the script is finished, would the myisam_sort_buffer_size go back to it's default value?
2009-12-27 05:24:21
yeah eric j is totally right.. and each connection may reserve a certain amount of memory based on the settings in my.cnf, so when dealing with special cases, i would rather do it on a per-connection basis
jspcal
2009-12-27 05:25:29
the setting would last for that connection until you change it back. usually the mysql connection is automatically closed at the end of the script (unless using pconnect).
jspcal
2009-12-27 05:27:18
Thanks for all the the info. I plan to benchmark this and will post the results. From what I can tell, the default buffer size is reset after the script exits, even though I AM using pconnect.
2009-12-28 00:42:18
Increasing the myisam_sort_buffer_size to 2GB did not seem to change anything. After doing some digging, I see that there is another variable (myisam_max_sort_file_size) that I may need to use as well. Does anyone have any experience with this?
2009-12-28 03:28:15