How to Enable Slow Query Logging on MariaDB

MariaDB is one of the most popular open source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

Many websites use MariaDB to store its data. When a website is launched, it may run fast. But the data grows from time to time, especially when it's getting more traffic from day to day. There are times when websites become slow. Many factors can be involved, but one of them is because of slow queries. Slow queries happen when many, e.g. millions of records are in one or multiple tables.

Slow queries

Slow queries are queries that run more than a specific time limit, e.g. 5 seconds. Imagine if a webpage executes 10 queries, and each of them runs for at least 5 seconds. In addition to how many asset files are loaded, plus other processing, including but not limited to executing a third-party API call, sending email notifications, and lastly, imagine if you have thousands of people that are using your website, that 5 seconds will surely make your website look slow.

How to identify slow queries

MariaDB provides a way for us to log slow queries. In the previous example, we specified queries run at least 5 seconds are considered slow. MariaDB will log those slow queries to a log file or table. More instructions will be provided later in this article.

Before we begin

• The instructions have been tested on MariaDB 10.7.8. The DDL file in this article uses the UUID data type, which is only available starting from this MariaDB version.

Prerequisites

• Docker Engine or Docker Desktop installed
• A sample of a large dataset is used to experiment with the slow queries. You can get it by cloning my repository here. Note that the dataset file size is 644 MB. It may take a while for the git clone to finish.

• Once the repository is cloned, run this command on your terminal at the project level to initialize the container:

  docker-compose up -d
  

  It will take a while for the container to initialize as it has to extract the compressed dataset file, but you can monitor the process by running this command:

  docker logs -f mariadb_slow_query
  

  Wait until you read this line: Finished initializing the container

• Once the container is fully initialized, you can run all the commands in this article inside the container. To get inside the container, run this command:

  docker exec -it mariadb_slow_query /bin/bash
  

Slow query logging

There are 2 ways to enable slow query logging, and they are:

• By modifying the MariaDB configuration file

  You can use this approach to enable slow query logging permanently. Note that this approach requires us to restart the MariaDB service. Restarting MariaDB service can be dangerous. Imagine if visitors to your website are trying to make payments, and the payment processes are suddenly terminated. This may cause corrupted data stored in your database. And, slow query logging should be enabled temporarily, as it can make your disk space full in no time. When the disk space is fully occupied, it can corrupt your MariaDB, rendering it completely inoperable. So, be very super careful when taking this approach.

• By modifying some of the MariaDB system variables

  I prefer this approach because it does not require us to restart the MariaDB service. We will use this approach in this article.

Logging Type

There are two slow query logging types, and they are:

1. File logging

   To enable slow query logging and store the result in a log file, follow these steps:
   a. Login to MySQL console, and select the appropriate database

   
   b. Run this query to find out which filename the slow query log will be written to:

   SELECT @@slow_query_log_file;
   

   If the output is a filename, not a fullpath, e.g. showing folder location, it means it will be written to a file, and the file by default is stored to /var/lib/mysql.

   

   
   If we want to store the file to a new filename, we can run this query:

   SET GLOBAL slow_query_log_file='slow.log';
   

   The old file is preserved, and a new file will be created.

   
   If we want to store the file in a specific folder, for example /var/log/mysql, we can run this query:

   SET GLOBAL slow_query_log_file='/var/log/mysql/slow.log';
   

   
   b. Next, we need to change the time limit. We can change the time limit to 1 second, which means any queries run at least 1 second will be considered slow queries. By default, it's configured to 10 seconds. We can change it by running this query:

   SET global long_query_time = 1;
   

   c. Lastly, we need to see whether the slow query logging has been enabled or not by running this command:

   SELECT @@slow_query_log;
   

   if it returns 0, it means it's disabled. We can enable it by running this query:

   SET global slow_query_log = 1; 
   

   To disable it back, run this query:

   SET global slow_query_log = 0; 
   

2. Table logging
   The slow queries can be recorded in a table by following these steps:
   a. Login to MySQL console, and select the appropriate database

   
   b. First, let's check the current logging type by running this query:

   SELECT @@log_output;
   

   By default, it will return FILE. We can change it to table logging, by running this query:

   SET global log_output = 'table';
   

   Note that the above query will be applied to slow and general query logging. General query logging will log all types of queries. By default, general query logging is disabled.
   The slow query logging will be recorded to slow_query table within the mysql database. Meanwhile, the general query logging will be recorded to general_log table within the mysql database.
   c. Change the time limit, e.g. 1 second. Any query that runs at least 1 second will be considered a slow query. We can change the time limit by running this command:

   SET global long_query_time = 1;
   

   d. Enable the slow query logging by running this query:

   SET global slow_query_log = 1; 
   

   To disable it back, run this query:

   SET global slow_query_log = 0; 
   

   e. Check the slow query log by running this query:

   SELECT * FROM mysql.slow_log;
   

Run some tests

Now, we need to generate the slow query logs, either by running the website or by running some queries that are believed to be slow.
For example, using the above large dataset, run these queries:

SELECT u.DisplayName, c.* from askubuntu.comments c INNER JOIN askubuntu.posts p ON c.PostId = p.id INNER JOIN askubuntu.users u ON c.UserId = u.id Where Text Like '%aptitude%';

SELECT p.Title, u.DisplayName, p.ViewCount, p.AnswerCount, p.CommentCount FROM posts p INNER JOIN users u ON p.OwnerUserId = u.Id WHERE p.Title LIKE '%ubuntu%';

Now, let's check the slow query logs.
If you choose file logging, run this command:

tail -f LOG_FILE_LOCATION

Note that, change LOG_FILE_LOCATION accordingly, e.g. /var/log/mysql/slow.log.

If you choose table logging, run this query:

SELECT query_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log ORDER BY query_time DESC;

References

• MariaDB official site
• Slow Query Log Overview
• MariaDB Docker Image