How to handle slow query logging on MariaDB

Sometimes once you have eliminated all bottlenecks, e.g. NVME and RAM and vCPUs, then all that is left is to check the database.

I like to do this using three terminal windows because it’s monitoring in real-time.

Also, I don’t like setting my.cnf values because what if I forget to reset them? So I rather do this on the console.

Once I’m done my workspace looks like this:

As you will see in the error on the first page, it was actually quite a mission to set up.

Creating a sane processlist monitor

First we’ll create a real-time monitor to show there is action when doing database queries:

watch -n 1 'mysql -uroot -p -e "SHOW PROCESSLIST;"'

Enable Slow Query Logging

Gotcha: touch the file *first* and assign mysql:mysql permissions to it, otherwise you get the weirdness below:

debian@server:~$ mysql -uroot -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6356
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.020 sec)

MariaDB [(none)]> SET GLOBAL long_query_time = 10;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
ERROR 1231 (42000): Variable 'slow_query_log_file' can't be set to the value of '/var/log/mysql/slow.log'
MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
ERROR 29 (HY000): File '/var/log/mysql/slow.log' not found (Errcode: 13 "Permission denied")
MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

Tailing the slow query log file

root@server:/var/log/mysql# tail -f slow.log
/usr/sbin/mariadbd, Version: 10.11.6-MariaDB-0+deb12u1 (Debian 12). started with:
Tcp port: 3306 Unix socket: /run/mysqld/mysqld.sock
Time Id Command Argument

Output of slow queries: