If you notice some slowness on your application (ie: website) and are looking to speed things up you can enable slow query logging on your server and inspect some of the slow queries. Slow queries are database queries that take longer than the amount of time you specify in the file. Most people start at 1 or 2 seconds and let that cook a day or two.
Once you start getting some data in your log file, you can use mysqldumpslow to parse it into more useful data if you have a lot of slow queries.
The syntax to enable the slow query log in MySQL changes by version. Let's find out which version you're running.
Run the following command to find the version:
It should output something like:
Let's enable it now..
Edit your MySQL config file (/etc/my.cnf) and add the following lines, being careful to use the one specific to your version. Note that 'long_query_time' can be set to however many seconds you'd like to use.
MySQL 5.6 and after:
MySQL 5.1.6 up to 5.5.x:
MySQL previous to 5.1.6:
Next, create the directory and chown it to the correct owner:
Then, reload MySQL to read in the changes:
You should see something like:
Once you start getting some data in your log file, you can use mysqldumpslow to parse it into more useful data if you have a lot of slow queries.
The syntax to enable the slow query log in MySQL changes by version. Let's find out which version you're running.
Run the following command to find the version:
Code:
mysql --version
It should output something like:
Code:
Server version: 5.5.54-cll MySQL Community Server (GPL)
Let's enable it now..
Edit your MySQL config file (/etc/my.cnf) and add the following lines, being careful to use the one specific to your version. Note that 'long_query_time' can be set to however many seconds you'd like to use.
MySQL 5.6 and after:
Code:
long_query_time=1
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
MySQL 5.1.6 up to 5.5.x:
Code:
log_slow_queries = 1
long_query_time=1
slow_query_log_file =/var/log/mysql/slow-query.log
MySQL previous to 5.1.6:
Code:
long_query_time = 1
log_slow_queries=/var/log/mysql/slow-query.log
Next, create the directory and chown it to the correct owner:
Code:
mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
Then, reload MySQL to read in the changes:
Code:
service mysql reload
You should see something like:
Code:
Success! MySQL running (22344)