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 2

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.

share|improve this answer
    
That's like sending developers into a corner for a timeout. +1 !!! –  RolandoMySQLDBA yesterday
add comment

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]

[Edit 2]

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 OS partitioning is your best bet for the moment. Bill Karwin's suggestion is also worthy of merit. 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.

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

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.