Some MySQL queries are executing more time than the normal and it might be efficiency issues affecting your server, if you want to identifying those queries that take a long time to execute and would helps optimisation the queries.

Enable the Slow Query Log on MySQL or MariaDB:

You can enable the Slow Query Log for MySQL or MairaDB Databases. Need to login to your Instannce or Server as the Root user account via SSH.

Open the MySQL configuration file named "my.cnf" with any text editor and add the following lines under the "mysqld" session, here I'm usiing VIM editor,



vim /var/log/mysql-slow-query.log

 
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow-query.log
long_query_time = 2

Next, Create a new file and named mysql-slow-query.log using the command "touch" in /var/log directory and set its user as the MySQL using the command "chown",



touch /var/log/mysql-slow-query.log



chown mysql:mysql /var/log/mysql-slow-query.log


Restart MySQL or MariaDB database using the command below

service mysql {stop | start | restart | status}



service mysql stop 

service mysql start 

or


service mysql restart

Start monitoring the slow query logfile to the location /var/log/mysql-slow-query.log. You can start to analyze and print the file summary using the mysqldumslow command,

mysqldumpslow -a  /var/log/mysql-slow-query.log



Some MySQL queries are executing more time than the normal and it might be efficiency issues affecting your server, if you want to identifying those queries that take a long time to execute and would helps optimisation the queries.


How do We Enable the Slow Query Log in MySQL or MariaDB databases on Linux