MariaDB Slow Query Log Explained

Troubleshooting MariaDB Performance with Slow Query Log 

Introduction

How do be troubleshoot MariaDB for performance ? We get this question often and there is definitely no silver bullet to troubleshoot MariaDB performance like a magic so it’s always better to understand performance bottleneck in detail. Let us first explain how we define performance in MinervaDB – Performance is a combination of latency and throughput, is there a ratio in that computation ? Absolutely not, A very expensive SQL in a high performance server can never benefit from processing power and infrastructure investments, this equally applies when you execute a optimal SQL on undersized infrastructure, throughput is very important to increase the velocity of data retrieval and processing, So where do we start MariaDB performance audit / forensics in MinervaDB ? We strongly believe  performance is ” Response Time “, If we have identified the top 5 expensive SQLs  by response time and throughput then by tuning 4 out of 5 those SQLs solves 80% of the bottleneck so knowing what to optimize immediately by priority is very important than troubleshooting with no evidence. In this post we have explained how to use MariaDB slow query log to troubleshoot performance outage:

Enabling slow query log in MariaDB

By default slow query log is disabled in MariaDB for both performance and storage optimization. You can dynamically enable slow query log with SET GLOBAL:

You can set the same in server option group in an option file prior to starting up the server:

How to configure MariaDB slow query log to login slow queries by ” Response Time” ?

The time that defines a slow query can be configured by setting the long_query_time system variable.

Dynamically configuring long_query_time with SET GLOBAL:

Configuring long_query_time in a server option group in an option file prior to starting up the server:

Identify slow query log with a custom filename

By default, the slow query log is written to ${hostname}-slow.log in the datadir directory. But you can always change it to avoid possible confusion if you have several MariaDB instances.

Configuring slow query log file on my.cnf :

Configuring slow query log file dynamically with SET GLOBAL : 

Note: 

  • Relative path for slow query log file in MariaDB is datadir directory
  • You can also configure absolute path for slow query log file in MariaDB

You can also set slow query log filename in MariaDB with log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. slow query log, general query log, error log, binary logs, etc.). The slow query log filename will be built by adding -slow.log to this prefix. This option cannot be set dynamically. It can be set in a server option group in an option file prior to starting up the server. For example:

P.S. The log-basename cannot be an absolute path. The log file name is relative to the datadir directory.

Configuring slow query log output destination in MariaDB

You can configure slow query log output destination in MariaDB, it can either be written to a file on disk, or it can be written to the slow_log table in the mysql database.

Writing slow query log to a file

You can set the slow query log to a file dynamically with SET GLOBAL option:

You can set slow query log to a file in a server option group in an option file prior to starting up the server:

Writing slow query log to a table 

You can set the slow query log to a table dynamically with SET GLOBAL option:

You can set slow query log to a table in a server option group in an option file prior to starting up the server:

Querying MariaDB slow query log from mysql.slow_log table

Disable slow query log for a session

In MariaDB 10.1 and later, a user can disable logging to the slow query log for a connection by setting the slow_query_log system variable to 0

P.S. Not always logging every statements to slow query log in MariaDB is required to troubleshoot performance so consider setting for leaner slow logs, In MariaDB 10.3.1 and later, it is possible to disable logging to the slow query log for specific types of statements by setting the log_slow_disabled_statements system variable. This option cannot be set dynamically. It can be set in a server option group in an option file prior to starting up the server.

Using MariaDB slow query log to identify queries that don’t use indexes

It’s quite obvious that READs on larger tables without indexes goes very expensive. So when you have a complex database architecture on MariaDB how do you find specific queries on tables taking a hit on performance due to missing indexes / not using indexes ? You can enable MariaDB system variable log_queries_not_using_indexes with slow query log enabled.

Enable log_queries_not_using_indexes dynamically with SET GLOBAL:

Enable log_queries_not_using_indexes configuring server option group in an option file prior to starting up the server:

Log queries that examine a minimum number of rows

You can configure slow query log to log queries that examine a minimum number of rows regardless of their execution time by setting the min_examined_row_limit system variable.

configure min_examined_row_limit dynamically with SET GLOBAL:

configure min_examined_row_limit with server option group in an option file prior to starting up the server:

Identifying slow administrative statements with slow query log

By default, the Slow Query Log only logs slow non-administrative statements. To log administrative statements, set the log_slow_admin_statements system variable.

Enable log_slow_admin_statements dynamically with SET GLOBAL:

Enable log_slow_admin_statements with server option group in an option file prior to starting up the server:

The slow query log considers following statements administrative:

  • ALTER TABLE
  • ANALYZE TABLE
  • CHECK TABLE
  • CREATE INDEX
  • DROP INDEX
  • OPTIMIZE TABLE
  • REPAIR TABLE.

Conclusion

MariaDB slow query log is very powerful system variable to troubleshoot performance bottleneck effectively. But over configuring is not always recommended as that causes extra load on the MariaDB server. If MariaDB slow query log is used conservatively with detailed understanding of related server options / system variables you can simplify MariaDB performance troubleshooting.