This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we’re working on upgrading that. But this is what we have.
I just ran a few queries with large inserts and complicated joins that caused load to spike to 6, from where it usually is at 1.10. People started reporting that the website was becoming sluggish. These are all MyISAM table, but this is not about row vs table locking – the performance loss was on an entirely separate set of tables from what was being operated on. This is not about ‘can’t execute other queries’, this is about ‘everything else on the system slows down’.
I’ve been trying to google this and can find no other resource saying a query is supposed to spike server load like that. So I’m starting to wonder: Is it just me? If so, do you have any suggestions? And if not, do you have any mitigation suggestions?
For any given query, once a tmp table reaches 16M in RAM, it performs the following migration
- the query suspends operation
- mysqld will migrate the entire 16M of tmp table data onto disk as a MyISAM table
- the query continues until completion
You must also keep in mind where the disk-based tmp tables will land. By default, the folder where disk-based tmp tables will land in defined in tmpdir.
- For Windows, this would be
- For Linux, this would be /tmp
If the mysqld is competing for tmpdir usage with the OS, your should feel it rather quickly because the OS would start swapping to find the elbow room in needs in the system temp folder while the query in question might be trying to force a tmp table into the same OS tmp space.
You may want to look into one or more of the following
- mounting /tmp to a RAM disk
- mounting /tmp to another disk away fromthe root partition
- setting tmp_table_size and max_heap_table_size to 2K (that’s not a typo, I SAID 2K) to force tmp tables to goto tmpdir quickly, thus shortening tmp table memory-to-disk migration time
- I wrote about this in StackOverflow
UPDATE 2012-03-13 15:11 EDT
Setting tmp_table_size and max_heap_table_size to 512M is dangerously high. In the event of bad queries (especially the Cross Join (aka Caretsian Join)), mysqld will spend time migrating a 512M MEMORY table to a disk-based MyISAM table before the query continues processing. You may need to lower that number significantly to force tmp tables into disk faster. This is imperative since tmp_table_size and max_heap_table_size are set per DB Connection.
I’m not sure about the server load. is it in percents?
If you are doing inserts or updates with joins, all rows of joined tables participating in the joins are being locked for update (InnoDB). In case of MyISAM whole table is locked for update and insert too.
Web clients are waiting for the lock to release if they need to do an update/insert on involved tables.
SHOW PROCESSLIST — to see what is running when it has troubles.
Don’t set query_cache_size bigger than 50M — else it will chew up cycles on QC maintenance.