MySQL Slow Query Logging

mysql slow query logging

Hello Guys,

Today we will discuss on monitoring slow MySQL queries logging that is the important point comes under database design.

Here are the following steps

Step 1:
First of all, we need to open the Linux terminal and then log in on the MySQL prompt using the following command:

After hit Enter, It will ask for the root user password.
After entering the correct password it will open mysql prompt like this

Step 2:
Now we will enable slow_query_log using the following mysql sql query:

Step 3:
Now in the next step, we will set the long_query_time.
Note: By default it set for 10 seconds so it means when we enable the slow_query_log then it will log every slow query who is taking more than 10 seconds in execution.

According to the above global setting, it will log query is taking more than 15 seconds.

Step 4:
Now we will set the log file where all the query logs will store.

Now all the logs will store in this file those taking more than 15 seconds in execution.

Step 5:
All setting for slow log queries has been done and we need to verify the execution.

We will execute the query whose taking more than 15 seconds in execution.Suppose I have a query

this query will take more than 20 seconds in execution so its logging must be stored in the logging file.

Open the file and check the log file content

Step 6:
If you want to disable the mysql slow query logging then you need to execute the following commands

Note:
If you want to log those queries not using the indexes then you could also set the following

Leave a Comment

Your email address will not be published. Required fields are marked *

Optimization WordPress Plugins & Solutions by W3 EDGE