Slow query analysis

Slow query analysis is very important to dig low mysql performance, high cpu utilization, slow query response and application performance. The process of analyzing slow query includes enabling slow query in mysql server and then process the logs using scripts or query analysis tools.

How to Enable Slow Query log:

Add following line in /etc/my.cnf ( mysql configuration file )
slow_query_log=ON
slow_query_log_file=slowquery_logfile_path
stop start mysql

versions 5.1 and later allow to enable slow query log without restarting mysql.

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

If no file_name value is given for --log-slow-queries, the default name is host_name-slow.log. The server creates the file in the data directory unless 
an absolute path name is given to specify a different directory. 

By default mysql logs slow queries taking more than 10 seconds, we can reduce query log time by setting variable long_query_time in /etc/my.cnf or at run time on mysql console

mysql> set global long_query_time=5.000000;
Query OK, 0 rows affected (0.00 sec)

Now all sql queries/statement taking more than 5 seconds will be logged into log file in plain text with additional information.