How to enable the MySQL slow query log
Sometimes
when working with big data, you probably faced issue to fetch number of records from database when number of records are more than 1,00,000. Suppose your developed web application which have backend mysql database is running slow and you wish to optimise web application to find out database queries which are executing slow. So, you can optimise slow queries and speedup application. Now, Testing each database query is bit time consuming task. Think about something helps you to list out all the queries at one place which takes more time than your specified time. Yes, In this post we will set configuration that lists all the queries running slow automatically in one file when you test the project.
First of all set below parameters in mysql configuration file. If you are using Linux operating system than default path if configuration file should be ‘/etc/mysql/my.cnf’ or find mysql config file in linux operating system. If you are using XAMPP than default path of configuration file is [XAMPP Directory]/mysql/bin/my.ini. Suppose, You have installed in C: drive than path would be C:\mysql\bin\my.ini.
Open configuration file and add below parameters under [mysqld].
|
1
2
3
4
|
slow_query_log = 1
log-queries-not-using-indexes
long_query_time=10
log-slow-queries=/var/log/mysql/slow-queries.log
|
slow_query_log is used to enable or disable query log option.
log-queries-not-using-indexes tells MySQL to log any queries that do not use indexes.
long_query_time=10 indicates that queries need to be logged that took more than 10 seconds to execute
log-slow-queries logs all slow queries to /var/log/mysql/log-slow-queries file.
That’s it. MySql Configuration is updated. Now, Create log file on particular location which you have set in configuration file. For Linux OS, Create directory using mkdir command if directory does not exists and than create file using touch command.
|
1
2
|
mkdir /var/log/mysql
touch /var/log/mysql/slow-queries.log
|
If you are using Linux OS than you need to add permission to log file that you have set in mysql config.
|
1
|
chown mysql:mysql -R /var/log/mysql
|
Now, restart mysql service to apply changes.
Once restarting mysql service start testing application and mysql will log all slow queries which took more than 10 seconds to execute.
For, further help you may comment or send your query on as well.
