I setup MariaDB server on Linux. How do I enable slow query log in MariaDB server on Linux or Unix-like system?

The slow query log is nothing but a log of SQL queries that took a long time on your server. You can use this facility to find out SQL queries that are slowing down your dynamic web app. Please note that default disables the slow query log. So let us see how to set and enable slow query log in MariaDB server.

Adblock detected 😱

My website is made possible by displaying online advertisements to my visitors. I get it! Ads are annoying but they help keep this website running. It is hard to keep the site running and producing new content when so many people block ads. Please consider donating money to the nixCraft via PayPal/Bitcoin, or become a supporter using Patreon.

How to activating the slow query Log

Edit my.cnf or mariadb.cnf stored in /etc/mysql/ directory. In this example I am editing the /etc/mysql/mariadb.conf.d/50-server.cnf file:
$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
Append the following syntax in [mysqld] section:

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow-query.log
log_queries_not_using_indexes

Save and close the file. Where,

1. slow_query_log = 1 – Enable the slow query log.
2. long_query_time = 1 – Set time in seconds/microseconds defining a slow query.
3. slow_query_log_file = /var/log/mysql/slow-query.log – Name of the slow query log file
4. log_queries_not_using_indexes – Whether to log queries that don’t use indexes

Restart the mariadb server

Type the following command:
$ sudo /etc/init.d/mysql restart
OR
$ sudo systemctl restart mysql
OR for RHEL/CentoS
$ sudo systemctl restart mysqld

View your slow query log

Type the following command:
$ sudo tail -f /var/log/mysql/slow-query.log
Sample outputs:

# Thread_id: 950  Schema: wpblog  QC_hit: No
# Query_time: 0.006252  Lock_time: 0.000032  Rows_sent: 499  Rows_examined: 816
SET timestamp=1490818968;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
# User@Host: bloguser[bloguser] @ www1 [192.168.162.36]
# Thread_id: 951  Schema: wpfront  QC_hit: No
# Query_time: 0.009001  Lock_time: 0.000038  Rows_sent: 448  Rows_examined: 2184
use wpfront;
SET timestamp=1490818968;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

You need to use the pt-query-digest to analyzes MySQL queries from slow, general, and binary log files.
$ pt-query-digest /var/log/mysql/slow-query.log
For more info see this page.