How to set and enable MariaDB slow query log

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.

ADVERTISEMENTS

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.

Category	List of Unix and Linux commands
File Management	cat
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Network Utilities	dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time
Searching	grep • whereis • which
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04