Finding and fixing load problems

How do you tune load problems on MySQL? You might get lucky and see that there are a few long running statements when running SHOW PROCESSLIST. You might be working on a small system where you know all of the people and/or SQL statements. Unfortunately, these approaches don't work for me most of the time. My first approach was to run SHOW PROCESSLIST many times and aggregate the results by account and by statement. This is a simple and occasionally effective profiler. Aggregating by statement worked better after normalizing the query text by replacing all literals with a a constant. Eventually we implemented support for account, table and index monitoring. The results are reported with SHOW USER_STATISTICS, SHOW TABLE_STATISTICS and SHOW INDEX_STATISTICS. The features are described here.

I use the output from these commands in several ways. I generate nightly reports that rank accounts number of seconds running SQL statements (Busy_time in SHOW USER_STATISTICS). I look at that report on a weekly basis to see if there is a change in the most busy accounts. I also look at that report when new applications are launched to see if there are SQL statements that must be adjusted. I should generate reports that contain the change in values from day to day, but I have yet to do that. Many of my reports are generated with a mix of Bash, Awk and SQL statements and my reporting capabilities are limited.

When there is an immediate problem, I use a tool that displays the change in values from SHOW USER_STATISTICS over intervals that I define so I can see the load per account over the last 10 minutes rather than since the server was started. It would be great to roll this kind of functionality into innotop. But there is not much point in doing that until the new monitoring code is in MySQL.

Occasionally people ask if patches for this feature are available on a particular release. Unfortunately, they are limited to 5.0.37 and 4.0.26 for now, and all changes are in one patch. If that doesn't work for you then update the feature request for this here. And if you don't want to wait, I am sure you could hire someone from the MySQL community to port it for you.