Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a busy MyMQL server with quite a few databases. I'm trying to figure out the best way to find the busiest databases (and tables) on the server so I can migrate them to a separate machine. What's the best way to go about this?

share|improve this question

4 Answers 4

up vote 2 down vote accepted

You should activate the slow query log and see which queries take the most time. This can be achieved by using

  log_slow_queries       = /var/log/foo.log
  long_query_time = 5

Adjust the long_query_time to your needs.

A second option is to use mytop and see at the queries as they fly by. But I recommend going the log slow queries route. If you want requests/second metrics, you should enable the binary logfile for a while (say a day) and then run something like http://hackmysql.com/mysqlsla on it.

share|improve this answer
    
All of the hackmysql tools are deprecated (I work with their author) and I would suggest Percona Toolkit's pt-query-digest instead (both hackmysql's author and I work on it). I would also suggest innotop instead of mytop; it's a superset of mytop's capabilities. –  Baron Schwartz Mar 14 '12 at 11:48

If you have extra money to spend, use MySQL Enterprise Monitor for evaluating all kinds of performance and query statistics. I think it's possible to get an evaluation version of MEM somehow.

If you don't have that extra cash, MySQL Proxy can intercept the queries and execute whatever you want, so you could collect statistics that way.

The slow query log Peter Meyer told you is also one way.

share|improve this answer

Use the Maatkit toolset. Specifically mk-query-digest. Create a log of all queries, either with using general log or tcpdump. mk-query-digest will create a report of the most frequent queries. From that report you can see which database/tables are hit the most often.

Capture query packets with

tcpdump -i eth1 port 3306 -s 65535 -x -n -q -tttt > dump.txt

Analye with

mk-query-digest --type=tcpdump dump.txt --order-by=Query_time:cnt --limit=200 > query_report.txt
share|improve this answer
    
Maatkit is now reborn as Percona Toolkit, BTW. –  Baron Schwartz Mar 14 '12 at 11:48

I would suggest the so-called user_statistics feature that Google originally developed. It is available in Percona Server and MariaDB, and there is a similar feature in the PERFORMANCE_SCHEMA in the upcoming MySQL 5.6 release. All of them make it rather trivial to determine what tables, indexes, etc are used.

If this is not an option, then I would choose pt-query-digest as suggested by sreimer.

share|improve this answer

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.