Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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 actually current Oracle/MySQL employees. 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.

[Edit]
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.
[/Edit]

HTH, Paul...

share|improve this answer
    
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 at 21:39
add comment

You could look at this document about account resource limits?

share|improve this answer
    
These restrictions help, but not a whole lot. We have some of those settings in place, but they do not address the underlying resource use. For example, even if we restrict an account to 1 query per hour, that query could use all the system's resources. –  rlandster Feb 22 at 17:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.