- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
The slow query log consists of all SQL statements that took more
than long_query_time seconds to execute and
(as of MySQL 5.1.21) required at least
min_examined_row_limit rows to be examined.
The time to acquire the initial table locks is not counted as
execution time. mysqld writes a statement to
the slow query log after it has been executed and after all
locks have been released, so log order might be different from
execution order. The minimum and default values of
long_query_time are 1 and 10, respectively.
Prior to MySQL 5.1.21, the minimum value is 1, and the value for
this variable must be an integer. Beginning with MySQL 5.1.21,
the minimum is 0, and a resolution of microseconds is supported
when logging to a file. However, the microseconds part is
ignored and only integer values are written when logging to
tables.
To enable the slow query log as of MySQL 5.1.6, start
mysqld with the
--log-slow-queries[=
option, and optionally use file_name]--log-output to
specify the log destination (as described in
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”). Before 5.1.6, the slow query log
destination is always a file. To enable the slow query log file,
use the
--log-slow-queries[=
option.
file_name]
If no file_name value is given for
--log-slow-queries, the default name is
host_name-slow.log
When --log-slow-queries is specified, the
--slow-query-log option also may be given as of
MySQL 5.1.12 to specify the initial slow query log state. With
no argument or an argument of 0, the option disables the log. If
omitted or given with an argument of 1, the option enables the
log. If --log--slow-queries is not given,
--slow-query-log has no effect.
The global slow_query_log and
slow_query_log_file system variables provide
runtime control over the slow query log. Set
slow_query_log to 0 (or
OFF) to disable the log or to 1 (or
ON) to enable it. Set
general_log_file to specify the name of the
log file. If a log file already is open, it is closed and the
new file is opened.
When the slow query log is enabled, output is written to any
destinations specified by the --log-output
option or log_output system variable. Note
that if the destination is NONE, no output is
written even if the slow query log is enabled. Setting the log
filename has no effect on logging if the log destination value
does not contain FILE.
The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process the slow query log using the mysqldumpslow command to summarize the queries that appear in the log. Use mysqldumpslow --help to see the options that this command supports.
In MySQL 5.1, queries that do not use indexes are
logged in the slow query log if the
--log-queries-not-using-indexes option is
specified. See Section 5.1.2, “Command Options”.
MySQL Enterprise. Excessive table scans are indicative of missing or poorly optimized indexes. Using an advisor specifically designed for the task, the MySQL Enterprise Monitor can identify such problems and offer advice on resolution. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
In MySQL 5.1, the
--log-slow-admin-statements server option
enables you to request logging of slow administrative statements
such as OPTIMIZE TABLE, ANALYZE
TABLE, and ALTER TABLE to the slow
query log.
Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.
User Comments
Please note that the mysql slow query log will not show the SQL of your slow queries if your application uses prepared statement.
You may want to turn on General Query Log instead.
When I turned on the slow_query.log, and told it to also log queries that do not use indexes my log started to fill up with SHOW COLLATION; and SHOW VARIABLES; statement.
It can cause your log file to grow VERY quickly.
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. 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.
7. To run the program now, simply type:
./mysqldumpslow_new -s c -t 3
8. You have now displayed output from the slow query log telling you the top 3 slow queries on the system.
Andrea Gangini posted that slow queries made via prepared statements do not post to the slow query log. This is not exactly true.
In the case of MySQL 5.0.22 when accessed through JDBC (Java 1.5.0), slow queries constructed via PreparedStatement always appear in the slow query log. Slow queries constructed normally (via Statement), sometimes appear. I suspect that PreparedStatements bypass the query cache and so are always reported in the log whereas regular Statements are checked against the query cache. If the results are taken from the query cache and not by accessing the tables, the query does not appear in slow query log. Testing regular Statements with the SQL_CACHE and SQL_NO_CACHE hints seems to confirm this. (Remember MySQL does not expend much effort trying to recognise queries it's already seen - if the query strings aren't exactly the same, don't expect to get help from the query cache.)
Chris
If you see many trivial queries being logged here, you may have log-long-format enabled.
This logs all queries not using indexes in the slow query logs, which includes queries so trivial that using the index would be slower.
Replication queries which are slow aren't saved in the slow logs. If a Slave is behaving badly and the only thing that shows up in the slow logs are queries which are not normally a problem, then replication queries may be causing problems. Check the timetamp in the replication logs and compare them to the slow logs.
Syntax for /etc/my.cnf in Red Hat 9:
[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
You must create the file manually and change owners this way:
mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql
Add your own comment.