Enable Slow Query Log in MariaDB for Performance

In this tutorial we’ll learn how to enable slow query log in MariaDB server. Slow queries are often the main reason behind poor MySQL server performance. By enabling the slow query log, you can identify and optimize those queries that are taking a long time to execute. To enable the slow query log in MariaDB, you can follow these steps:

Edit the MariaDB configuration file /etc/my.cnf.d/server.cnf using your favorite text editor:

nano /etc/my.cnf.d/server.cnf

Add the following lines under the [mysqld] section:

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 4
log_queries_not_using_indexes = ON

Explanation of the above parameters:

• slow_query_log: This parameter turns on the slow query log.
• slow_query_log_file: This parameter specifies the location and name of the slow query log file.
• long_query_time: This parameter specifies the number of seconds that a query must exceed to be considered a slow query.
• log_queries_not_using_indexes: This parameter logs queries that do not use indexes, which can help you identify queries that need optimization.

Note that you can adjust the values of these parameters to suit your needs.

Save and close the file.

Restart the MariaDB service to apply the changes:

systemctl restart mariadb

Verify that the slow query log is enabled by checking the log file:

tail -f /var/lib/mysql/slow-query.log

This will display the latest slow queries that have been logged.

By following these steps, you can enable the slow query log in MariaDB and start optimizing your queries for better performance.