Schedule Meeting

a

Logging all MariaDB and MySQL queries into the Slow Log

by | Aug 18, 2020 | MariaDB

Need Help?  Click Here for Expert Support
A slow country road
Is this road’s traffic logged somehow?

MySQL and MariaDB optionally log slow queries into the Slow Query Log – or just Slow Log, as friends call it. However, there are good reasons to log all the queries, not just some of them. This article shows how to log all available information into the Slow Log.

Why can’t I only log the slowest queries?

There are several reasons to log all queries. This list is not exhaustive:

  • If you have a locking query that takes 0.5 seconds and runs 10 times per second, most probably optimising it is much more relevant than a query that takes 1 second and is executed once per second. Logging the slowest queries may result in bad priorities.
  • Threshold are not smart. Put a 5 seconds threshold, and you will never know that a very frequent query takes 4.9 seconds. Logging the slowest queries hides important queries.
  • Suppose you only log queries that take 5 or more seconds. If a query takes less than 1 second more of the times and ore than 5 seconds in rare cases (which typically depends on data distribution) you will lose this information. Logging the slowest queries may hide the real behaviour of a query.
  • If you don’t log relatively fast queries, you cannot analyse your workload and see, for example, that many locking statements insist on the same table.
  • You want to see and remove useless queries.
  • For some queries it is perfectly ok to take a long time (one-time queries, cached queries, analytics…). This makes any time threshold not very significant.

How to apply the changes

Below you can find the variables to change, as they should be written in the configuration file (most probably /etc/mysql/my.cnf or /etc/my.cnf). Note that changes in the configuration file will take effect on MySQL restart.

You avoid restarting MySQL, but I recommend to make the changes to the configuration file anyway, in case it crashes.

To change variables values at runtime:

SET GLOBAL variable_name := 1;
SET GLOBAL variable_name := 'value'; # string values

This only takes effect for new connections. You are not logging all queries until all existing connections are closed and new ones are established.

Setting the Slow Log

Here you can find the correct setting to use to log all your queries, depending on which MySQL flavour you use.

MySQL

MySQL 8.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_extra = 1

For more information about log_slow_extra, see Overview of detailed slow query logging in MySQL 8: log_slow_extra.

MySQL 5.7

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

MySQL 5.6

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

MySQL 5.5

To be done

MySQL 5.1

To be done

MySQL 5.0

log_slow_queries = slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

On MySQL 5.0 didn’t allow to log all the queries. long_query_time was required to be an integer and the minimum value was 1. Therefore all we can to is logging queries that don’t use an index or take at least one second.

Hint: if you still use MySQL 5.0, please consider our upgrade service.

Percona Server

Percona Server 8.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

# information in the slow log
log_slow_extra = 1
log_slow_verbosity = full

Percona Server 5.7

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

Percona Server 5.6

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
log_throttle_queries_not_using_indexes = 0
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log_use_global_control = all

MariaDB

MariaDB 11.0

# log queries into a log.slow file
log_slow_query = 1
log_output = FILE
log_slow_query_file = log.slow

# log all queries
log_slow_query_time = 0
log_slow_min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.11

Note that some slow log variables were renamed in version 10.11.

# log queries into a log.slow file
log_slow_query = 1
log_output = FILE
log_slow_query_file = log.slow

# log all queries
log_slow_query_time = 0
log_slow_min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.7, 10.8, 10.9, 10.10

Same as MariaDB 10.6.

MariaDB 10.6

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.5

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.4

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.3

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.2

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.1

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 10.0

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1

# information in the slow log
log_slow_verbosity = query_plan,innodb,explain

MariaDB 5.5 and previous versions

# log queries into a log.slow file
slow_query_log = 1
log_output = FILE
slow_query_log_file = log.slow

# log all queries
long_query_time = 0
min_examined_row_limit = 0
log_queries_not_using_indexes = 1

Hint: if you still use MariaDB 5.5 or older, please consider our upgrade service.

Other forks

WarpSQL 8.0 is based on MySQL 8.0, so you can use the same settings for the slow log.

Is the Slow Log slow?

In most cases, the slow log performance impact is minimum. It may sound scary to think that MySQL will write something into a file for every single statement; but not if you know the amount of IO that is required for every single statement (undo log, redo log, binlog, doublewrite, potentially reads from tablespaces) and InnoDB background threads. And the slow log is just a sequential write-only file. Slow log access is governed by a lock, but in practice this can only slow down very intensive workloads.

That said, if your IO is nearly saturated, Percona Server and MariaDB allows to throttle the slow log. Which means, multiple queries will be written to the file together, to reduce the IO operations. Take a look at log_slow_rate_limit. Note however that, if you do so, some queries will not be logged.

NOTE: Thanks Peter Zaitsev for reporting to me that log_slow_rate_limit was first implemented and is also supported in Percona Server. This allowed me to fix a wrong sentence in the article. He wasn’t able to write a comment on Federico-Razzoli.com, where this article was originally published, because of a problem in that website (now fixed).

See also

Related courses

Reference

Conclusions

We discussed why you should log all your queries and all available information about them. We saw exactly how to do it, depending on your MySQL or Percona Server or MariaDB version.

This is something that we check at the beginning of our MySQL Health Checks and MariaDB Health Checks. This is because I include recommendations so optimise the most impacting queries, so we need to be sure we have complete query statistics.

Federico Razzoli

Did you like this article?

Photo credit

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Coming up next, a ColumnStore webinar

Coming up next, a ColumnStore webinar

The 27th is fast approaching but you can still signup to our live webinar where we will be exploring the benefits of time series data using MariaDB ColumnStore. We will be jumping into a live demo with some example data and queries simulating high volume time series...

The benefits of MariaDB ColumnStore

The benefits of MariaDB ColumnStore

Last week Richard announced our projects on MariaDB ColumnStore. Take a look at his great post, if you didn't already. Since then, I've got some questions from customers, colleagues and friends: why did you guys decide to robustly invest into ColumnStore, and offer...

Let’s go, MariaDB ColumnStore at Vettabase!

Let’s go, MariaDB ColumnStore at Vettabase!

I have been an avid user of Columnar based storage engines for as long as I have been a DBA. The advantage is instantly obvious once you and your teams start to write or convert queries which previously took tens of minutes, are now returning in fractions of a second....

Services

Need Help?  Click Here for Expert Support

3 Comments

  1. Abbas Ahmed

    Great write-up Federico, it is valuable to have all this info on a single page for so many versions of MySQL, Percona Server and MariaDB. I wonder if you could do a similar one for which version of XtraBackup works with what version/variant of the server product, as that’s one more source of confusion at times.

    Reply
    • federico.razzoli

      Hi Abbas! Thank you for your feedback.
      It will be a long test, I can’t promise we will do it. I suggest you file a feature request on Percona’s JIRA.
      However, if you care about old discontinued versions, you’ll be interested in VettaArchive: archive.vettabase.com
      Soon you will find old Xtrabackup versions there.

      Reply
  2. Ricardo Bravo

    Great Job, Federico!

    Unhappily I was stuck in MariaDB 5.5, so I’d like to tell you what worked for me: the MariadB 5.6 run OK but I had to comment out one sentence:

    # log_throttle_queries_not_using_indexes = 0

    After the reboot it started to log all queries.

    Congrats!

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *