MySQL 5.6 Release Notes
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 minimum and default values of
long_query_time are 0 and 10,
respectively. The value can be specified to a resolution of
microseconds. For logging to a file, times are written including
the microseconds part. For logging to tables, only integer times
are written; the microseconds part is ignored.
By default, administrative statements are not logged, nor are
queries that do not use indexes for lookups. This behavior can be
changed using
log_slow_admin_statements and
log_queries_not_using_indexes, as
described later.
The time to acquire the initial 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 differ from execution order.
By default, the slow query log is disabled. To specify the initial
slow query log state explicitly, use
--slow_query_log[={0|1}]. With no
argument or an argument of 1,
--slow_query_log enables the log.
With an argument of 0, this option disables the log. To specify a
log file name, use
--slow_query_log_file=.
To specify the log destination, use
file_name--log-output (as described in
Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”).
If you specify no name for the slow query log file, the default
name is
host_name-slow.log
To disable or enable the slow query log or change the log file
name at runtime, use the global
slow_query_log and
slow_query_log_file system
variables. Set slow_query_log to
0 (or OFF) to disable the log or to 1 (or
ON) to enable it. Set
slow_query_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, the server writes output to
any destinations specified by the
--log-output option or
log_output system variable. If
you enable the log, the server opens the log file and writes
startup messages to it. However, further logging of queries to the
file does not occur unless the FILE log
destination is selected. If the destination is
NONE, the server writes no queries even if the
slow query log is enabled. Setting the log file name has no effect
on logging if the log destination value does not contain
FILE.
The server writes less information to the slow query log if you
use the --log-short-format option.
To include slow administrative statements in the statements
written to the slow query log, use the
log_slow_admin_statements system
variable. Administrative statements include
ALTER TABLE,
ANALYZE TABLE,
CHECK TABLE,
CREATE INDEX,
DROP INDEX,
OPTIMIZE TABLE, and
REPAIR TABLE.
To include queries that do not use indexes for row lookups in the
statements written to the slow query log, enable the
log_queries_not_using_indexes
system variable. When such queries are logged, the slow query log
may grow quickly. It is possible to put a rate limit on these
queries by setting the
log_throttle_queries_not_using_indexes
system variable. By default, this variable is 0, which means there
is no limit. Positive values impose a per-minute limit on logging
of queries that do not use indexes. The first such query opens a
60-second window within which the server logs queries up to the
given limit, then suppresses additional queries. If there are
suppressed queries when the window ends, the server logs a summary
that indicates how many there were and the aggregate time spent in
them. The next 60-second window begins when the server logs the
next query that does not use indexes.
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_statementsmust be enabled.The query must have taken at least
long_query_timeseconds, orlog_queries_not_using_indexesmust be enabled and the query used no indexes for row lookups.The query must have examined at least
min_examined_row_limitrows.The query must not be suppressed according to the
log_throttle_queries_not_using_indexessetting.
The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.
By default, a replication slave does not write replicated queries
to the slow query log. To change this, use the
log_slow_slave_statements system
variable.
As of MySQL 5.6.3, passwords in statements written to the slow query log are rewritten by the server not to occur literally in plain text. Before MySQL 5.6.3, passwords in statements are not rewritten and the slow query log should be protected. See Section 6.1.2.3, “Passwords and Logging”.
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 a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.9, “mysqldumpslow — Summarize Slow Query Log Files”.
You may want to turn on General Query Log instead.
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.
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
[mysqld]
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
[mysqld]
log-slow-queries = slow.log
long_query_time = 20
log-queries-not-using-indexes
- The first line under [mysqld] turns on slow query log and logs all slow queries to slow.log in the MySQL data directory.
- The second line indicates that any queries that took more than 20 seconds to execute need to be logged.
- The last line tells MySQL to log *any* queries that do not use indexes regardless of the setting in the second line.
If you like you can set the long_query_time very high so that only the queries that do not use indexes are logged (and no 'general' slow queries).
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
log_queries_not_using_indexes = 1
Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.