mysql log location
/var/log/mysql
Setup mysql slow query log in my.cnf
nano /etc/mysql/my.cnf
or
/etc/mysql/mysql.conf.d
slow_query_log = on
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 2
Restart mysql
service mysql restart
check slow query logs in mysql
tail -f /var/log/mysql/mysql-slow.log
Command line until restart only
mysql -u root -p
check whether slow query log enabled or not
By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log
show variables like ‘slow_query_log_file’;
mysql> show variables like ‘slow_query_log_file’;
+———————+———————————————-+
| Variable_name | Value |
+———————+———————————————-+
| slow_query_log_file | /var/lib/mysql/-s-4vcpu-8gb-blr1-01-slow.log |
+———————+———————————————-+
1 row in set (0.00 sec)
show variables like ‘slow_query_log’;
mysql> show variables like ‘slow_query_log’;
+—————-+——-+
| Variable_name | Value |
+—————-+——-+
| slow_query_log | OFF |
+—————-+——-+
1 row in set (0.01 sec)
Enable mysql slow query log in command line
mysql> SET GLOBAL slow_query_log = ‘ON’;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘slow_query_log’;
+—————-+——-+
| Variable_name | Value |
+—————-+——-+
| slow_query_log | ON |
+—————-+——-+
1 row in set (0.01 sec)
or
SET GLOBAL slow_query_log = 1;
check & Set mysql long query time
show variables like ‘long_query_time’;
mysql> show variables like ‘long_query_time’;
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 2.000000 |
+—————–+———-+
1 row in set (0.00 sec)
2 seconds
SET GLOBAL long_query_time = X;
SET GLOBAL long_query_time = 0.2;
mysql> SET GLOBAL long_query_time = 0.2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘long_query_time’;
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 2.000000 |
+—————–+———-+
1 row in set (0.01 sec)
SET GLOBAL long_query_time = 1;
mysql> show variables like ‘long_query_time’;
+—————–+———-+
| Variable_name | Value |
+—————–+———-+
| long_query_time | 0.200000 |
hardcoded in file mysql.cnf
Change mysql slow query log path
mysql> show variables like ‘slow_query_log_file’;
+———————+———————————————-+
| Variable_name | Value |
+———————+———————————————-+
| slow_query_log_file | /var/lib/mysql/-s-4vcpu-8gb-blr1-01-slow.log |
+———————+———————————————-+
1 row in set (0.00 sec)
SET GLOBAL slow_query_log_file = ‘/path/filename’;
SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;
mysql> show variables like ‘slow_query_log_file’;
+———————+——————————-+
| Variable_name | Value |
+———————+——————————-+
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+———————+——————————-+
check slow logs in command line
tail -f /var/log/mysql/mysql-slow.log
Time: 2021-05-08T10:50:20.176454Z
# User@Host: root[root] @ localhost [] Id: 149
# Query_time: 0.005103 Lock_time: 0.000327 Rows_sent: 403 Rows_examined: 463
use uid;
SET timestamp=1620471020;
SELECT option_name, option_value FROM wpxu_options WHERE autoload = ‘yes’;
long query time i set 0.2 (200ms)
verify slow query logs in mysql
SELECT SLEEP(X);
x=long_query_time
example 2
SELECT SLEEP(2);
Disable mysql slow query log in command line
SET GLOBAL slow_query_log = ‘OFF’;
for persistent settings use mysql.conf and restart
understanding mysql slow query logs
Query_time: duration
The statement execution time in seconds.
Lock_time: duration
The time to acquire locks in seconds.
Rows_sent: N
The number of rows sent to the client.
Rows_examined:
The number of rows examined by the server layer (not counting any processing internal to storage engines).
What’s the ideal mysql long query time
Average Query Response Time (Read/Write Split)
sourced from table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME.
SELECT * FROM information_schema.QUERY_RESPONSE_TIME;
ideal mysql response time = 100ms
server response time = 200ms
redis query time 40-100ms.
page speed load tome 1-3 seconds.
how many queries to database when user lands.
queries per second =? Queueing theory
Cpu usage = 50% means 2 process at queue
90% = 10 ques
99%=100 queue
mysql query execution time
troubleshooting
MySQL slow query log logging all queries
long_query_time configuration is set to 1, yet
seeing every single query (e.g. just saw one that took 0.000563s!).
set global log_queries_not_using_indexes = ‘off’;
may helps!
Queries not using indexes and running under 2 seconds are being logged in the log
Faqs on mysql slow query logs
How do I know which query takes a long time?
Answer: by enabling slow query logs.