Mysql Slow running query

 To find Slow running Query you need to enable logging of slow running query

Lets first check status if slow running query logging is enabled

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

Output:

+---------------------+------------------------------------+

| Variable_name | Value |

+---------------------+------------------------------------+

| slow_query_log | OFF |

| slow_query_log_file | /var/lib/mysql/mysqlutils-slow.log |

+---------------------+------------------------------------+

How to enable Logging

Open MySQL's configuration file /etc/mysql/mysql.conf.d/mysqld.cnf

you will find following statements

# Here you can see queries with especially long duration

# slow_query_log = 1

# slow_query_log_file = /var/log/mysql/mysql-slow.log

# long_query_time = 2

# log-queries-not-using-indexes = 1

After saving this file run following

sudo mysqld --validate-config

sudo service mysql restart

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

+---------------------+-------------------------------+

| Variable_name | Value |

+---------------------+-------------------------------+

| slow_query_log | ON |

| slow_query_log_file | /var/log/mysql/mysql-slow.log |

+---------------------+-------------------------------+

You can toggle this variable by following

SET GLOBAL slow_query_log = 'OFF';

To find slow running query

sudo mysqldumpslow /var/log/mysql/mysql-slow.log

Other way is

sudo apt install percona-toolkit

sudo pt-query-digest mysql-slow.log

References:

https://blog.toadworld.com/2017/08/09/logging-and-analyzing-slow-queries-in-mysql