Slow Query Log Overview

You are viewing an old version of this article. View the current version here.

The slow query log is a record of SQL queries that took a long time to perform.

Activating the Slow Query Log

The slow query log is disabled by default. To enable it, use the --slow_query_log option. The log_output server system variable determines how the output will be written, and can also disable it (see Writing logs into tables for details). By default, the log is written to file, but it can also be written to table. The name of the file is determined by the --slow_query_log_file=file_name option, by default host_name-slow.log, while the table used is the slow_log table in the mysql database.

What's in the Slow Query Log

A slow query is defined as a query that takes longer than long_query_time to run, by default 10 seconds. Microseconds are allowed for logging into file, but not for logging into a table. This value can be adjusted dynamically.

Only non-administrative statements are logged, as well as queries that use indexes. It can be particularly useful to include queries without indexes in the log, and this is set with the log_queries_not_using_indexes setting. Queries on tables containing 0 or 1 row will not be logged.

Slow administrative queries, which include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE statements, can also be stored by using the --log-slow-admin-statements setting.

The log contains SQL queries that took longer than long_query_time seconds to perform, by default ten seconds, as well as queries that had to examine more than min_examined_row_limit rows. Time to acquire locks is not included in this calculation, only time to resolve the query.

A number of variables can control what's written to, as well as the frequency, of the slow query log. log_slow_rate_limit can allow a fraction of the queries to be logged, reducing excessive I/O or overly large log files. log_slow_verbosity can control detail of information written to the log, while log_slow_filter can limit which queries to log.

Note that, if your queries contain users passwords, the slow query log may contain passwords too. Thus, it should be protected.

MariaDB starting with 10.0.5

Starting from MariaDB 10.0.5, it is possible to have EXPLAIN output printed in the slow query log.

Viewing the Slow Query Log

Slow query logs written to file can be view with any text editor, or you can use the mysqldumpslow tool to ease the process by summarizing the information.

Queries that you find in the log are key queries to try to optimize by constructing a more efficient query or by making better use of indexes, in some cases these are queries that you can't do anything at SQL layer, like big selects, slow/broken hardware, or very high lock/cpu/io contention, in some cases try shard/cluster/load balance solutions or use better hardware, stats tables to solve these queries problem.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.