April 4, 2006
MySQL Performance Tuning Underway
I got fully immersed in performance tuning on the databases at work this morning. There was a seriously overloaded MySQL machine. No time like a crisis to have the new guy try his hand at resolving a performance issue. I was in a meeting for part of the time where I couldn't do much more than watch and I noticed over time that there were anywhere from 2 to 10 queries stuck writing to temp tables. This was happening over and over for more than an hour. Somewhere halfway through the meeting a query came through that was taking many minutes, mostly stuck in a mode of writing to a temp table.
Upon further inspection I noticed that the myisam_sort_buffer_size was set fairly large, but the sort_buffer_size was quite small. To my knowledge, the myisam_sort_buffer_size is for table maintenance and optimization, but isn't used during normal database operations. The sort_buffer_size is used for sorting for normal operations. After getting consensus from other folks I upped the sort_buffer_size and have seen marked improvement in the lingering queries writing to temp tables. I need to do some additional research to look at the specific queries and determine if there are other tuning needs, but for now we're doing OK.
Quick summary of MySQL sorting. MySQL sorts data by loading it into a memory buffer where it sorts based on the ORDER BY clause. Pre 4.1 the sort data is put into the buffer with a pointer to the row for sorting. After the buffer is sorted the records are retrieved from the data file. This requires that the records be read twice (first to get the sort data and second to get the records). In 4.1 and beyond, the sort puts the sort data, the row position, and the columns required by the query. Thus there's no need to go back to the table as the columns can be read from the sort buffer.
In either case, if you don't have a large enough sort buffer for sorting the data MySQL does a quicksort of the data in the buffer and then sticks it in a temporary table to free up the memory buffer for more sorting. That operation is expensive compared to keeping things in memory.
This is covered pretty well in the MySQL internals doc on sorting.
Posted by mike at April 4, 2006 12:56 PM