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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. How to obtain MySQL metadata (metadata access methods)
  2. How to reset MySQL database root password
  3. What is the purpose of “system user” in MySQL Replication
  4. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  5. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  6. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  7. How To Start And Stop MySQL Cluster
  8. Managing MySQL Using Systemd As A Non Root User
  9. MySQL : How To Find the Slowest Queries
  10. How to Rollback RPM Upgrade of the MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • raw: command not found
  • raw Command Examples in Linux
  • rankmirrors Command Examples in Linux
  • radeontop: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright