How to configure mysqld to log slow queries

Environment

• Red Hat Enterprise Linux (RHEL) 5
• Red Hat Enterprise Linux (RHEL) 6
• Red Hat Enterprise Linux (RHEL) 7
• mysql-server / Mariadb / Galera packages.

Issue

• How to configure mysqld to log slow queries
• How to troubleshoot slow mysql queries

Resolution

• 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":

[mysqld]

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

• Restart mysqld

# service mysqld restart

Diagnostic Steps

Check if the configuration works.

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)

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)

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.