I've inherited a LAMP server running centos, which randomly hangs from time to time despite low web traffic.

I've installed Server Density to monitor what's going on, and have found during a hang the following happens:

  • There is a spike in iowait to 100%.
  • The number of mysql "temporary disk tables" fell from 140,000 to 0.
  • There is no load on apache (the server doesn't even respond)

To my untrained eye it looks mysqld is periodically having a purge and locking up the whole server whilst it does the disk IO (which is pretty slow as it's a VM).

How do I change the mysqld config to prevent this?


The number of mysql "temporary disk tables" fell from 140,000 to 0.

Are these temporary tables created by the application (i.e. 'CREATE TEMPORARY TABLE....') or are they created by the database (used when recreating indexes / sorting large datasets).

Tweaking the config to allow more sorting in memory / more threads should help (how you do this depends on which engine the tables are implemented in). mysqltuner.pl should make sensible suggestions.

But you'll get a lot more benefit from tuning your queries.

1) make sure that none of your code is disabling/enabling indexes on very large tables (this improves performance when initially populating a table, and can improve turnaround on adding rows to a table - but at the expense of a loss of concurrency).

2) Use this script to strip out literal values from queries in the slow query log then prioritize based on the queries with the highest cumulative processing time.

| improve this answer | |

Run: mysqladmin -u root -p ext -ri 30 | grep Created_tmp_disk

First run will tell you how many on-disk temporary tables were created since last restart of MySQL. Then it will tell you how many are created in the 30 seconds time window (until you Control-C out of it).

If the value is large and keeps getting bigger then you can put MySQL's tmpdir on a ramdisk to mitigate the high iowait.

Better yet, would be to optimize your queries as symcbean suggested. Look here for the conditions that result in temporary tables.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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