Percona Server for MySQL Highlights – Extended Slow Query Logging

mysql extended slow query loggingLast year, I made the first post in a small series, which aimed to highlight unique features of Percona Server for MySQL, by discussing binlog_space_limit option.

Today, I am going to discuss another important type of log available in MySQL that is enhanced in Percona Server for MySQL – the slow query log. The reason why I am doing this is that although this extension has existed since the very early times of versions 5.1 (over 10 years ago!), many people are still unaware of it, which I see from time to time when working with Support customers.

Default Slow Log Inadequacy

How many times have you been wondering why, whilst reviewing slow query logs, the very same query occasionally runs way slower than usual? There may be many reasons for that, but the standard slow query log does not always provide any helpful information about that. Let’s take a look at these two real test examples:

vs

The most important information that usually gives a good clue on why it is slow – Rows_examined – is the same in both cases. So where dies the big difference in execution time come from?

Extended Slow Query Log Virtue

And here comes the extended verbosity mode for slow log available in Percona Server for MySQL. By simply using log_slow_verbosity=full variable, here is how similar log entries now look:

vs

So much more information! And we can now easily find the difference – InnoDB IO information. Basically the slow instance of the query had to read many data pages from disk, while the fast one clearly took advantage of the buffer pool.

That’s not all, we can be even more verbose and make the slow log print full query profiling info as well:

And there are more perks in the extended slow query logging!

Safety For Busy Production Server and More

How many times were you concerned about using long_query_time=0 (or just the very low value of), because logging hundreds or thousands of queries per second on a busy system could be too much overhead? Two very useful options come handy here: log_slow_filter and log_slow_rate_limit. For example, you can order the server to log only queries that do joins without using indexes, but only 1% of such queries will be logged, with the following settings:

Of course, all those settings are dynamic and you can control whether they will apply to local sessions only or to all connections.
It is important to mention here, that other Percona tools, like pt-query-digest or Percona Monitoring and Management (PMM), can take advantage of the extra information. Here is an example of Query Analytics view in PMM using the extra InnoDB details in its statistics:

Query Analytics in Percona Monitoring and Management

You will find more details about this rich, yet not very well known, feature set in our documentation pages.

MySQL 8.0 Extra

Since MySQL 8.0.14 Community Edition (and Percona Server for MySQL as well), also in the upstream, another variant of extended slow query log info was implemented, which however only partially overlaps with Percona features. Basically, you can now make the status handlers printed as well, by using log_slow_extra=1. An example entry for the same query looks like that:

This can be super useful in cases where we want to check if the execution plan was changing. Interestingly, you can enable both extensions at the same time if needed.

Slow Log Automatic Rotation

Tired of maintaining logrotate or custom scripts that keep your disk space safe from too bloated logs? There are new options in Percona Server for MySQL 5.7 that can make DBA/Sysadmin life easier – max_slowlog_size and max_slowlog_files. You may now set up limits after which the log will be automatically rotated and removed.

This auto-rotating functionality was not yet ported to Percona Server for MySQL 8.0 though. But if you think such should exist in MySQL, I’d suggest you vote for this 12-year-old feature request 🙂 https://bugs.mysql.com/bug.php?id=38702

Documentation reference:

Slow Query Log

Percona Server specific settings


Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.

Download PDF

Share this post

Leave a Reply