Profiling MySQL

MySQL lacks in providing a good profiling support for its RDBMS. But we all have found some hacks around it. Here are some of them as follows :

1 General query log
This is the most basic and the best profiling tool for MySQL as it records every statement executed at mysqld (Be it an erroneous statement). Its very helpful for finding out the type of statements executed on the database. But the problem lies in enabling the general log. The mysqld needs a restart, which is not a good option in production systems. Steps to enable general query log can be found in the MySQL reference manual. Also general query log fails to record the hostname or IP of the clients connecting MySQL server.

2 Show Processlist
This can be a good profiling tool except that it catches data about queries upon quering it. So in a high concurrency environment it becomes difficult to catch the data through "Show Processlist". Anyways these are some common hacks at using this handy command.

$ watch -n 1 'mysql -u <user> -p<password> -h <hostname> -e "Show Processlist"'

$ watch -n .5 'mysqladmin -u <user> -p<password> -h <hostname> --verbose processlist'

Well the above hacks will surely get the data but at the expense of new connections every time "watch" executes (for eg in an hour we might have threads_connected increased by 7200), that is a problem isnt it.

Or else

$ mysqladmin -u <user> -p<password> -ri 1 --verbose -processlist

This hack will get you the data like the other iterative operations but the connection will be persistent (Hmmm this one is good, isnt it ?). However we miss the sub-millisecond interval like the slow query logs, dont we ? In high concurrency environment scripting this hack will make you miss most of the statements.

The processlist generated above can be piped through "sed" (to remove the "|" "+" "-") and redirected to a file. This file can be used for future reference.

3 Using the Slow Query Log
This is the most simple yet powerful hack. Most of the production systems are enabled with Slow Query Log. The default long_query_time is 10 seconds but can be dyanamically set to 0 seconds. Newer versions of MySQL do come with sub-milliseconds long_query_time, but 0 is sufficient for our purpose. This can be very handy and nifty for the purpose of profiling. Also one can use mk-query-digest to dig into this log. The only disadvantage in this approach as compared to general query log being erroneous statements are missed. Thats not a problem, is it.

To conclude these are some common hacks which can be done at a very low or no setup cost, with little performance degradation though. Many options exist including commercial tools like Jet-Profiler, MySQL Enterprise monitor or open source tools like innotop , mytop, Maatkit for the purpose of profiling MySQL.