Getting MySQL queries right is very important for the performance of Web application. similarly, getting indexes on the right columns in the right tables make difference between queries that take fractions of a second and queries that can take upwards of 10 seconds.
SSH server and create the MySQL log directory and slow log file:
mkdir /var/log/mysql
touch /var/log/mysql/server1-slow.log
chown mysql.mysql -R /var/log/mysql
Log in to the MySQL CLI via
mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 633645
Server version: 5.5.49-cll MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
Set the log-file location for the Slow Query log.
mysql> SET GLOBAL slow_query_log_file = ‘/var/log/mysql/server1-slow.log’;
Determine what makes a query “slow”, by setting the limit (in seconds) after which a query is logged to the slow query log. I’ve setup logs every query that exceeds 10 seconds in duration.
mysql> SET GLOBAL long_query_time = 10;
Now enable the Slow Query log.
mysql> SET GLOBAL slow_query_log = ‘ON’;
mysql> FLUSH LOGS;
You can also make all queries that do not use indexes be logged to the same Slow Query log.
mysql> SET GLOBAL log_queries_not_using_indexes = ‘YES’;
If you want to make these changes persistent, modify the my.cnf and add these lines to the [mysqld] part of the config.
[mysqld]
…
slow_query_log = /var/log/mysql/server1-slow.log
long_query_time = 10
log_queries_not_using_indexes = YES
Note: This will also work for MariaDB or Percona’s version of MySQL.
To check its setup correctly, request the running parameters from MySQL.
mysql> SHOW GLOBAL VARIABLES LIKE ‘log_queries_not_using_indexes’;
mysql> SHOW GLOBAL VARIABLES LIKE ‘log_queries_not_using_indexes’;
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| log_queries_not_using_indexes | ON |
+——————————-+——-+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE ‘slow\_%’;
mysql> show global variables like ‘%slow%’;
+———————+———————————+
| Variable_name | Value |
+———————+———————————+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/server1-slow.log |
+———————+———————————+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE ‘long_query_time’;
mysql> SHOW GLOBAL VARIABLES LIKE ‘long_query_time’;
+—————–+———–+
| Variable_name | Value |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+
1 row in set (0.00 sec)
Don’t forget to disable it or enable logrotate to prevent the logs files from growing too large.
Use the following at the CLI, to disable the logging, but leave the rest of the requested configs intact.
mysql> SET GLOBAL slow_query_log = ‘OFF’;
mysql> FLUSH LOGS;
To disable it permanently in the my.cnf file, set the slow_query_log to 0.
[mysqld]
…
slow_query_log = 0
long_query_time = 10
log_queries_not_using_indexes = YES
