MySQL Server : Most Frequent Requests

Question

I would like to know if there is anyway I can display the most frequent SQL query on MySQL server in order to memcache it.

Answer 1

You have to enable query logging or you should monitor it trough show processlist; Please see the documentation:

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

If you pick up the parameters right you can see the queries in the slow query log:

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

Answer 2

check mysqlsla - it can take your query log, parametrize queries and provide you report with query types that show up most often.

Answer 3

I'd suggest you set your slow query time to something very small, then run maatkit's mk-query-digest to profile all the logged queries. You may be able to run it on an ordinary log file as well.

There are other maatkit utilities that may be able to help too.

Innotop is a good way of watching what your server is up to - generally easier to keep an eye on than showing the processlist.

Answer 4

I've been working with the commercial software package Jet Profiler for MySQL http://www.jetprofiler.com/ and like it. It's about $400, though there's a trial period.

Otherwise, I log the SQL requests in my application. With Java (my language of choice) you can configure the JDBC Connector/J driver to log all sql requests. Then I do a mix of regexp and hand editing to make it readable and analyze the results.