MySQL – Slow Query Log

Mysql can log slow queries which takes longer to execute. In some cases this is expected but some queries take longer because of coding mistakes like multiple loops or inline queries or wrong joins. slow-query-log can definitely help a developer find those queries and make it easy to debug and improve performance of the application.
In WordPress/PHP world, many plugins are often coded my amateurs who have no idea about the scale at which big sites operate! Its better to use slow-query-log to find out such plugins.

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined.
The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:
The query must either not be an administrative statement, or log_slow_admin_statements must be enabled.
The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.
The query must have examined at least min_examined_row_limit rows.
The query must not be suppressed according to the log_throttle_queries_not_using_indexes setting.
Here are instructions on getting mysqldumpslow to work in your environment if you have my.cnf in a non-standard (eg, non /etc) locale. Please note this is for Unix only.

1. Make sure it can find perl in /usr/local/bin/perl
2. Make sure you’ve got the slow log running first
3. Copy the slow log to datadir and name it specifically: servername-slow.log (eg, servername-slow.log). This assumes your actual slowlog is located somewhere else. If it is located by default in datadir then you still must make a copy of it, named hostname-slow.log in datadir directory.
4. Make a copy of mysqldumpslow in the $MYSQL_HOME/bin directory, and name it: mysqldumpslow_new and be sure it is chmod 750
5. Edit the mysqldumpslow_new as described in the next item.
6. Assuming you don’t place your server’s my.cnf in /etc, you must tell my_print_defaults where datadir and basedir are because they can’t be set directly in mysqldumpslow nor can they be passed as a parameter on the command line to mysqldumpslow.
   This is done by altering the command line option to my_print_defaults, the results of which are fed into the mysqldumpslow perl script to establish datadir, basedir, and a few other variables.
7. The following line in mysqldumpslow_new should be changed.
   Change from:
   my $defaults = `my_print_defaults mysqld`;
   to:
   my $defaults = `my_print_defaults -c /location/where/config_file_lives/my.cnf mysqld`;Make sure the my.cnf pointed to in the -c option has datadir and basedir set under [mysqld] section.
8. To run the program now, simply type:
   ./mysqldumpslow_new -s c -t 3
10. You have now displayed output from the slow query log telling you the top 3 slow queries on the system.

Don’t forget..

Always restart mysql, every time you enable/disable slow-query-log for changes to take effect.
For more information about MYSQL programming services, or to get PHP help now, call us at +91 11 45683669. or fill out the form below to speak with a real person now.