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.)


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.

  • That's like sending developers into a corner for a timeout. +1 !!! – RolandoMySQLDBA Jul 23 '14 at 17:33

Unfortunately, prior to 5.5, performance_schema isn't available and that might have been able to solve your problem - of interest are Mark Leith's ps_helper and Jesper Wisborg's ps_tools Both are current Oracle/MySQL employees (as of writing - . Upgrade?

Failing that, what about OS throttling? If you use Linux or similar, you should be able to restrict resources on a per user basis using cgroups. Is it memory or disk or CPU you wish to control? It's a bit of a broad brush, but could be a start.

(in response to OP's comment)

You are correct but I did say it was a broad brush - but it could restrict your MySQL user to, say, 75% of the machine's resources instead of your database causing the entire machine to grind to a halt. If you're going to upgrade, may I recommend 5.6 instead of 5.5? Combine this with the sites I've mentioned plus Peter Zaitsev's tools and Percona's stuff and the data world is your oyster! I'd put Percona's mysqlperformance blog site in my bookmarks.

This MySQL document may be of assistance, though if, as you say, users are all running under the same account, that might not be so useful. Could you look at partitioning your app somehow - OLTP - day to day/OLAP - DW queries? I know that it's not a trivial problem to fix. I think that using some sort of partitioning is your best bet for the moment.

Following up on Bill Karwin's suggestion, perhaps you could use VM's and see which ones are particularly problematic - and then issue warnings/throttle the systems which are trying to "steal" too much in the way of resources? Also, the slow query log might be of use - but if the problem isn't the fact that the queries are slow, but rather that there are millions of them, then that won't fly!

Just a final thought - and this is a bit out in left field (as the Americans would say), but take a look here at tcprstat (and links therein) - you may be able to figure out some of your issues using that tool - checking out queries by IP address - I hasten to add that I've never used this tool and know next to nothing about it - and coding wise, it hasn't moved in a while.

  • All the users connect using stunnel to the mysqld service, so wouldn't all the connections be the same user? Hence, OS throttling would not work for me. Also, we are trying to upgrade to 5.5 as soon as we can, but we are not ready yet. – rlandster Feb 10 '14 at 21:39

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.