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.

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

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
$ 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 []
# 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.

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 2 comments so far... add one

CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
2 comments… add one

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Problem posting comment? Email me @