How to enable the MySQL Slow Query log in ubuntu

When developing your own internet application its necessary that pages load quickly. The most important factors that confirm page response times is that the time taken to SQL queries, however does one recognize that SQL queries at fault?

The answer is MySQL's slow question log. By facultative this feature, a log file is formed which will tell you on every time a query takes too long to execute. you'll be able to then use this data to either optimize your info or rewrite the SQL.

For this guide i'll assume you have already installed MySQL or MariaDB. These article is supporting Ubuntu 16.04 however the identical approach works on different UNIX operating system.

Step 1: modify the Slow question log

First, confirm your MySQL configuration file "my.cnf" is located - on Ubuntu 16.04 it's /etc/mysql/my.cnf whereas on different distributions it's typically /etc/my.cnf

Edit the MySQL configuration file and add the subsequent lines at the end:

 slow_query_log  
 slow_query_log_file = /var/log/mysql/slow.log  
 long_query_time = 1  

Save your changes and restart MySQL

 service mysql restart  

Step 2: examining the slow question log

Start looking the slow log:

 tail -f /var/log/mysql/slow.log  

At startup, you may see output like this:

 /usr/sbin/mysqld, Version: 5.7.12-0ubuntu1-log ((Ubuntu)). started with:  
 Tcp port: 3306 OS socket: /var/run/mysqld/mysqld.sock  
 Time Id Command Argument  

Now, mistreatment your browser begin interacting together with your internet application and note the queries that are shown. shortly you will notice one or 2 queries that are systematically shown.