0

Logging inefficient queries and query frequency in MySQL

We have a pour performing mysql DB. And I'm interested in logging queries and their frequency to assess which ones my time is best spent on. We have slow query logging but these queries aren't the ones hammering the DB. I've been told statspack for Oracle does exactly that with some sort of weighting on the queries.

Any ideas or suggestions would be appreciated.

Lex
  • 4,749
  • 3
  • 45
  • 66

1 Answers1

0

The MySQL general query log will show all statements being executed. On a busy server, this will grow quickly, and will slow performance, so you likely want to enable it briefly, and then disable it again. That will give you every statement executed, as well as client connects and disconnects.

http://dev.mysql.com/doc/refman/5.5/en/query-log.html

The slow query log can also be a useful tool.

There are some third party tools that can assist with analyzing the output from the general log, the slow query log, and the output from continually running a SHOW FULL PROCESSLIST. One example is MONyog.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks dude! Monyog looks great, trialling it now. I also found pt-query-digest from Percona tool kit. Will give the queries a rank, and generate sparklines http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html – Lex Feb 04 '13 at 00:08