• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Configuring mysqld to log slow queries

By admin

The Basics

As per the MySQL documentation:

"The slow query log consists of SQL statements that took more than 
long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined."

To log all the queries, you can set the value of long_query_time to 0. The default value of long_query_time is 10 seconds and min_examined_row_limit is 0.

Enable Slow Query log in MySQL

1. Modify the “mysqld” section of /etc/my.cnf. The following example shows to log queries which takes more than five seconds to “/var/lib/mysql/mysqld-slow.log”:

# vi /etc/my.cnf
[mysqld]

  long_query_time=5
  slow_query_log=1
  slow_query_log_file=/var/lib/mysql/mysqld-slow.log

2. Restart mysqld service once you are done with the above changes.

# service mysqld restart

Verify

1. Check if the configuration works. Check for the “slow_query_log” parameter (it should be “ON”).

mysql> show variables like '%slow%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| log_slow_queries    | ON                             |
| slow_launch_time    | 2                              |
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/mysqld-slow.log |
+---------------------+--------------------------------+
4 rows in set (0.00 sec)

2. Check for the parameter “long_query_time”, it should have the time as 5 seconds.

mysql> show variables like '%long%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| long_query_time    | 5.000000 |
| max_long_data_size | 1048576  |
+--------------------+----------+
2 rows in set (0.00 sec)

3. Verify that the queries are logged (You have to execute few long running queries to see them in slow query log):

mysql> SELECT SLEEP(6);
+----------+
| SLEEP(6) |
+----------+
|        0 |
+----------+
1 row in set (6.00 sec)
shell> sudo less /var/log/mysql/mysql-slow.log 
/usr/sbin/mysqld, Version: 8.0.3-rc-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2017-10-15T12:43:55.038601Z
# User@Host: root[root] @ localhost []  Id:     7
# Query_time: 2.000845  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1508071435;
SELECT SLEEP(6);

Enabling Slow query log Online

You can also enable the slow query log in MySQL instead of restart the mysqld service. To enable slow query log, you can dynamically set slow_query_log=1 and you can set the filename using slow_query_log_file. To specify the log destination, use –log-output:

1. Set the LONG_QUERY_TIME and verify the value.

mysql> SET @@GLOBAL.LONG_QUERY_TIME=5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.LONG_QUERY_TIME;
+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
|                 5.000000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.LONG_QUERY_TIME;
+--------------------------+
| @@GLOBAL.LONG_QUERY_TIME |
+--------------------------+
|                5.000000 |
+--------------------------+
1 row in set (0.00 sec)

2. Set the slow query file. By default, it would be in the data directory with the hostname-slow log:

mysql> SELECT @@GLOBAL.slow_query_log_file;
+---------------------------------+
| @@GLOBAL.slow_query_log_file    |
+---------------------------------+
| /var/lib/mysql/server1-slow.log |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log_file='/var/log/mysql/mysql-slow.log';
Query OK, 0 rows affected (0.00 sec)

Verify the new location of the log file:

mysql> SELECT @@GLOBAL.slow_query_log_file;
+-------------------------------+
| @@GLOBAL.slow_query_log_file  |
+-------------------------------+
| /var/log/mysql/mysql-slow.log |
+-------------------------------+
1 row in set (0.00 sec)

3. Enable the slow query log. By default it is disabled as shown below:

mysql> SELECT @@GLOBAL.slow_query_log;
+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@GLOBAL.slow_query_log;
+-------------------------+
| @@GLOBAL.slow_query_log |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
MySQL : How To Find the Slowest Queries
MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
What’s a good process to find and eliminate slow queries in MySQL

Filed Under: mysql

Some more articles you might also be interested in …

  1. What is the purpose of “system user” in MySQL Replication
  2. Understanding MySQL Query Cache
  3. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  4. How to Backup a MySQL Cluster
  5. How to Set Space limits for MySQL for database/schema/table
  6. How to obtain MySQL metadata (metadata access methods)
  7. How to use foreign keys to attain referential integrity in MySQL
  8. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  9. How to find the size of a MySQL database
  10. How to Change the Default Character Set and Collation for a Database in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary