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.

How to enable the mysql slow query log - sgeek

Enabling the MySQL slow query log

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].

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.

If you are using Linux OS than you need to add permission to log file that you have set in mysql config.

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 gopal@sgeek.org as well.

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">