How to Enable Slow Query Logging on MariaDB
Tags: mariadb, slowquery, logging
Published: 2023-11-20 22:32:07

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](https://docs.docker.com/engine/install/) or [Docker Desktop](https://www.docker.com/products/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](https://github.com/budiantoip-it/mariadb-slow-query.git). 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:
    ```bash
    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:

    ```bash
    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:

    ```bash
    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
    
    ![MySQL console login](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcdu8xnpm14x2gpfncet.png)
    b. Run this query to find out which filename the slow query log will be written to:
    ```sql
    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`.
    
    ![Filename](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/euvg0ksmrof8f6l6sc5e.png)
    
    ![File location](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qjrv6vg1qovi9ji6emzp.png)
    If we want to store the file to a new filename, we can run this query:
    ```sql
    SET GLOBAL slow_query_log_file='slow.log';
    ```
    The old file is preserved, and a new file will be created.

    ![File location 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bfwkw5lzh2krrs91uo44.png) 
    If we want to store the file in a specific folder, for example `/var/log/mysql`, we can run this query:
    ```sql
    SET GLOBAL slow_query_log_file='/var/log/mysql/slow.log';
    ```

    ![File location 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vi51chsm6hgks7rvid8k.png)
    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:
    ```sql
    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:
    ```sql
    SELECT @@slow_query_log;
    ```
    if it returns 0, it means it's disabled. We can enable it by running this query:
    ```sql
    SET global slow_query_log = 1; 
    ```
    To disable it back, run this query:
    ```sql
    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
    
    ![MySQL console login](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcdu8xnpm14x2gpfncet.png)
    b. First, let's check the current logging type by running this query:
    ```sql
    SELECT @@log_output;
    ```
    By default, it will return FILE. We can change it to table logging, by running this query:
    ```sql
    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:
    ```sql
    SET global long_query_time = 1;
    ```
    d. Enable the slow query logging by running this query:
    ```sql
    SET global slow_query_log = 1; 
    ```
    To disable it back, run this query:
    ```sql
    SET global slow_query_log = 0; 
    ```
    e. Check the slow query log by running this query:
    ```sql
    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:
```sql
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%';
```
```sql
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:
```bash
tail -f LOG_FILE_LOCATION
```
Note that, change LOG_FILE_LOCATION accordingly, e.g. `/var/log/mysql/slow.log`.

![File logging](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ctfcqlc4zxf5cjsy865b.png)
If you choose table logging, run this query:
```sql
SELECT query_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log ORDER BY query_time DESC;
```
![Table logging](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e6br279iqprcbi46zmlm.png)

## References
- [MariaDB official site](https://mariadb.org/)
- [Slow Query Log Overview](https://mariadb.com/kb/en/slow-query-log-overview/)
- [MariaDB Docker Image](https://hub.docker.com/_/mariadb)