MySQL Server : Most Frequent Requests

Hi,

I would like to know if there is anyway I can display the most frequent sql requests onmy mysql server in order to memcache it.

Thanks !

From serverfault mnml

• 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

  From l1x

• 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.

  From Synchro

• 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.

  From Will Glass

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

  From pQd