5

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?

Improve this question
2
  • well you could performance tune the queries you were running
    – HLGEM
    Commented Mar 13, 2012 at 17:38
  • This isn't about specific query performance. This is about the fact that some queries cause the entire server to slow down. Does this happen to other people? It seems kind of off if a single query can slow an entire machine down.
    – Andrew
    Commented Mar 13, 2012 at 18:16

3 Answers 3

Reset to default
4

Complicated joins can be a precursor to temp table usage. By default, tmp_table_size is set to whatever the value max_heap_table_size is (16M).

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 C:\TEMP or C:\TMP (environment variable TEMP or TMP)
  • 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.

Improve this answer
2
  • Hm. Both of those variables are already set to 512MB.
    – Andrew
    Commented Mar 13, 2012 at 18:22
  • So what you're saying is, not to increase it from 16M, but to keep it there or even decrease it? Is there a statistic that you know of that will tell me how often MySQL hits the tmp/max_heap_table_size, meaning I need to adjust it?
    – Andrew
    Commented Mar 14, 2012 at 15:43
0

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.

Cheers.

Improve this answer
2
  • I've said in the question this isn't about locking, as it's slowing down queries running on other tables.
    – Andrew
    Commented Mar 14, 2012 at 14:13
  • strip the insert part and do the explain on the select query to see if it is using indexes.
    – Imre L
    Commented Mar 14, 2012 at 15:28
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.