Home / server / Questions / 175593
In Process
jack
Asked: 2010-08-29 01:28:05 +0800 CST

MySQL stop responsing when modify main tables

  • 772

It's an InnoDB database. The query cache has 1.2GB in size with 300k queries in the cache. Most of the cached queries are related to 3 major tables (260MB data length plus 600MB index length altogether). Everytime I tried to insert new entry or update existing entries in these 3 tables, MySQL slowed down a lot just like the server is dead. I got "Timeout" if I open my website in browser.

I check the Munin stats, it shows 95% of the query cache are cleared after the operation so I guess the sudden overload was caused by inserting entries to heavy-read tables. And during the overload, Munin just shows a white strip on the graph so you may get an idea on how bad the situation was.

How can I optimize this?

mysql optimization cache update query

2 Answers

  1. Download mysqltuner.pl and run it against your database server:

    http://blog.mysqltuner.com/download/

    Something sounds horribly mis-optimized.

    • 0

  2. If your tables are all in InnoDB format, you should first tune the innodb_buffer_pool_size setting. Usually I tune this value to be about 80% of my system memory. Also, check the value of innodb_flush_log_at_trx_commit. If it's set to the default of 1, it will flush at every transaction commit which can cause a lot of disk I/O (which appears to be what you're suffering from). If you don't require your database to be 100% ACID compliant, you should set this value to 0 or 2. Here's the relevant snippet from MySQL pages:

    If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

    I usually set this to 2. Good luck!

    • 0