2

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?

4 Answers 4

1

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.

1
  • 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.
    – user48802
    Mar 14, 2012 at 11:48
3

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
1
  • Maatkit is now reborn as Percona Toolkit, BTW.
    – user48802
    Mar 14, 2012 at 11:48
1

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .