[TriLUG] MySQL Runaway

[Igor Partola]

Here's my go-to guide on figuring WTF MySQL is doing:

1. Gather basic info. What is the normal load on the server, how much
memory does it normally consume, how full are the disks, how much IO is
there typically? You can do this manually, or install something like Munin
and gather the stats remotely.

2. How many active connections does MySQL typically have? Use `SHOW
PROCESSLIST;` and `SHOW FULL PROCESSLIST;` to see what it does.

3. Are you primarily using InnoDB or MyISAM? If InnoDB, check to see how
much memory you devote to the InnoDB buffer pool. Make sure it's around 2/3
to 3/4 of the total available RAM. If you are using MyISAM, check how much
memory you are devoting to it (see docs for details). Do not overcommit the
amount of RAM you have: leave at least 20% for the OS.

4. Tune MySQL. This *might* make some kind of a difference if you have lots
of small connections or lots of tables/databases. You could increase the
size of the query cache, the size of the table cache, and the size of the
thread cache. In my experience, the thread cache can make a big impact on
giving you predictable performance and low latency when dealing with 500+
simultaneous connections. Also, tune the number of simultaneous
connections. Also, make sure to use the file_per_table InnoDB option. Still
no idea why that's not the default.

5. Enable the slow query log and see if you can spot a pattern in there.
You can try to catch things manually via `SHOW PROCESSLIST;` but this is
better.

6. The behavior you describe is typically caused by one of three things: a
large data import/export, a complicated report, or a backup. Backups are
one of the worst things to happen to a busy server, since they typically
hold lots of locks and use up the disk IO bandwidth. If that's the case,
consider adding more dedicated disks to your server.

7. If the issue is with a complex report or data import/export try to
optimize it. For reports, optimize the queries by removing complexity,
doing some work on the other server (in some cases doing a small join in
PHP/Python/Ruby is much cheaper than inside MySQL), adding indecies to the
tables and/or optimizing the tables via the ANALYZE/OPTIMIZE commands. Read
the docs of what this does to InnoDB vs MyISAM.

8. Once you have identified what queries are causing the issue, optimize
your applications. 99% of the time it's not possible to tune MySQL into
giving you an order of magnitude growth in performance. Instead, consider
making your application more robust and add lots of caching. For the cache
consider not just memcached, but also projects like MongoDB or CouchDB.
They have the benefit of being a bit more persistent and you can do things
like index the values and query by them. For example, this would be great
for any type of auto-complete search.

Hope that helps.

Igor