Tweet
MySQL Slow Query Logs
“Slow Query” :- Slow Query in simple terms can be defined as the query which took long time to get executed.. By default MySQL server is configured to mark the query
which takes more then 10 sec to get executed as Slow Query. It is best pratice to log all such queries in the log file so that they can be examined periodically..
The major question which now comes in the mind is why any query would be slow.. ???
Generally when any query gets executed on any database, it tries to find the index which enables the query to find desired values from the column.
An index is a sorted set of data that references the primary key of a table. If the proper index is missing even the simpler queris may take long time to gets executed.
Whereas if you use index in every column, any queries that add, change or delete data would then have to update all of the indexes so this will take long time and consume high resources.
So now all must have got the importance of the mysql slow query logs..
Now lets see how one can enable and configure them on the server.
Open mysql configuration file which is generally “/etc/my.cnf”
Now add the following lines in it.
log-slow-queries=1
log-slow-queries=/var/lib/mysql/slow.log
The “/var/lib/mysql/slow.log” is the path of the file where you wish to store it ( you may change it according to your requirements )
Once done, create a file with the correct ownership and file permissions using the following commands
touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
Now Restart MySQL.
/etc/init.d/mysql restart Or /etc/init.d/mysqld restart
Now if you wish to change the default time after which any query is marked as slow query, you may add the following in line my.cnf file above log-slow-queries
long_query_time=5
Save the file and restart the time.
It is important to take into consideration the server resources ( RAM and CPU ) before setting up the long_query_time
“Slow Query” :- Slow Query in simple terms can be defined as the query which took long time to get executed.. By default MySQL server is configured to mark the query
which takes more then 10 sec to get executed as Slow Query. It is best pratice to log all such queries in the log file so that they can be examined periodically..
The major question which now comes in the mind is why any query would be slow.. ???
Generally when any query gets executed on any database, it tries to find the index which enables the query to find desired values from the column.
An index is a sorted set of data that references the primary key of a table. If the proper index is missing even the simpler queris may take long time to gets executed.
Whereas if you use index in every column, any queries that add, change or delete data would then have to update all of the indexes so this will take long time and consume high resources.
So now all must have got the importance of the mysql slow query logs..
Now lets see how one can enable and configure them on the server.
Open mysql configuration file which is generally “/etc/my.cnf”
Now add the following lines in it.
log-slow-queries=1
log-slow-queries=/var/lib/mysql/slow.log
The “/var/lib/mysql/slow.log” is the path of the file where you wish to store it ( you may change it according to your requirements )
Once done, create a file with the correct ownership and file permissions using the following commands
touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log
Now Restart MySQL.
/etc/init.d/mysql restart Or /etc/init.d/mysqld restart
Now if you wish to change the default time after which any query is marked as slow query, you may add the following in line my.cnf file above log-slow-queries
long_query_time=5
Save the file and restart the time.
It is important to take into consideration the server resources ( RAM and CPU ) before setting up the long_query_time