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.

Posted by: SXI ADMIN

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.


How to KVM, QEMU start or stop virtual machine from command line (CLI)

KVM or Kernel Based Virtual Machine is a popular virtualization technology. It allows you to run virtual guest machines over a host machine. To start...

How to Docker backup Saving and restoring your volumes

Running a Docker volume backup First, we spin up a temporary container, and we mount the backup folder and the target Docker volume to this container....

How to Start and Enable Firewalld on CentOS 7

In this article, we discuss how to start and enable firewalld. It is highly recommended that you have a firewall protecting your server.Pre-Flight CheckThese...