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