Steps to enable slow query log in MYSQL

Steps to enable slow query log in MYSQL

Sometimes, you can see the performance of the website is slow down abruptly. You might be confused about the reason behind the slowdown of website performance. One hectic reason is slow MySQL queries which act as the main reason for website slowdown. It doesn’t stop with slowing down the performance it tries to get more resources from the server. You need a perfect choice to stop this kind of process which eats up the resource of the server. Choose MySQL server slow query log which helps to reduce the time searching query and makes execution very easy. For enabling the slow query log, start editing the file my.cnf preferred for customers like a dedicated server and Linux VPS.

Here, we elaborate the steps for enabling slow query log and making changes in my.cnf:

Step 1:
Use SSH to log in to the server. You can use the below command for editing settings of MySQL also, you can use editors like Emacs, Pico, Vi, and nano editor.

Code:
nano /etc/my.cnf
Step 2: Go to the section of mysqld for adding the below lines into mysql slow query log:

Code:
long_query_time=3
| This statement tells MySQL server to log any query that takes longer than 3 seconds.

Code:
log_slow_queries=/var/log/mysql/log-slow-queries.log
| This statement tells MySQL server where to log the slow queries.

Code:
log_queries_not_using_indexes=YES
| This is an optional statement that logs queries that don’t use an index.

Step 3: To save the changes you have done till now use shortcut Ctrl + O and do exit by entering Ctrl + X.

Step 4: Use the below commands by creating a slow log file and MySQL log directory:

Code:
mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql
Step 5: Again, use the below command once if you made changes in file my.cnf and log file creation. The command you are using is to restart the server of mysql here:

Code:
service mysqld restart
It’s over here! You have done all the possibilities of improving the high performance of the website also, your resource from the server won’t be eaten up now. All the best!
Author
kumkumsharma
Views
130
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top