Slow queries

The slow query log feature gives the possibility to log queries that take more than x seconds to be executed. This is the first step when investigating a performance issue. To look at the current status, connect to your MariaDB instance and launch it:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%SLOW_QUERY%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/log/mysql/mariadb-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

Here, we can see the path of the slow query logs. To activate this on the fly, run that SQL command:

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

The other option is to set in seconds the query delay to mark it as a long query. These long queries will be logged as follows:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%LONG_QUERY%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

This is the default setting; the long query is set to 10 seconds. You can change this setting on the fly as well:

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

Now, you've set global status variables on the fly. This prevents MariaDB from rebooting, and this is good news. However, only new connections will be affected by these changes. The problem will occur during the next start boot of MariaDB, as it will lose those settings. To avoid this, you have to set the MariaDB configuration file settings (/etc/mysql/my.cnf):

[mysqld]
slow_query_log=1
slow_query_log_file = /var/log/mysql/mariadb-slow.log 
long_query_time=1

You're now ready to look at the slow logs in the mariadb-slow.log file at /var/log/mysql/mariadb-slow.log. You will find all the slow queries, the query time, the lock time, and other interesting information in these queries. This is the first step to looking into your application and performing changes on the code part generating those requests. This could involve a lot of things, such as requests being too long, missing indexes, and so on, but the good thing is you now know which queries are slow and which ones make your application look slow.