Mysql – Find problematic MySQL users and databases

MySQLmysql-5

We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server.

As you might imagine, sometimes one user's database causes problems for all the rest. However, I am finding it difficult to tell which process or account is causing the problem. Using innotop shows me a bunch of queries that are taking a long time to finish, but that could be because other queries are using up all the server's resources.

What I want is an automated (or semi-automated) way to track down the databases or accounts that are using too many resources. For example, it would be nice to be able to run a command that would show me the 10 accounts or databases using the most disk I/O or the most memory.

Are there any tools out there that might help me do this?

(Note: an answer of "you shouldn't be using MySQL this way" although true is not going to help me as I have no choice in how this service is setup: I just have to maintain it.)

Best Answer

I helped a company find which databases were accounting for the greatest load. We collected slow-query logs, and then ran a report with pt-query-digest. Except we subtotaled by db, instead of by query fingerprint.

$ pt-query-digest --group-by db mysql-slow.log

There are lots of other options in this tool, so you could filter or sort on the total IOPS, or total number of temp tables, or total duration of queries, etc.

Once you identify the worst db, move it off your server until its owner learns to write more reasonable SQL queries.