[Architecture] MySQL Log File-slow query log

[Architecture] MySQL Log File-slow query log as the name suggests, in the slow query log, a threshold value can be set for a query with a long execution time. All SQL statements whose running time exceeds the threshold value are recorded in the slow query log file. you can set the parameter long_query_time. The default value is 10 seconds. This parameter is required. If the running time is exactly the same as long_query_time, it is not recorded because, in the source code, it is determined that the value is greater than long_query_time, rather greater than or equal to [plain] mysql> show variables like 'Log _ slow_queries '; + ------------------ + ------- + | Variable_name | Value | + ------------------ + ------- + | log_slow_queries | ON | + ------------------ + ------- + 1 row in set (0.00 sec) mysql> show variables like 'long _ query_time '; + ----------------- + ----------- + | Variable_name | Value | + ----------------- + ----------- + | long_query_time | 10.000000 | + ----------------- + ----------- + 1 row in set (0.00 sec) another parameter related to slow query logs is log_queries_not_using_indexes. If the running SQL does not use indexes, MySQL will record this statement to the slow query log file [plain] mysql> show variables like' log _ queries_not_using_indexes '; + rows + ------- + | Variable_name | Value | + ------------------------------- + ------- + | log_queries_not_using_indexes | OFF | + rows + ------- + 1 row in set (0.00 sec) here, I didn't enable it, but I need to remind you that if you modify this parameter online, even if there is no error, but it will not take effect, MySQL also provides a tool program dedicated to analyzing the full log query mysqldumpslow, it is used to help MySQL DBA solve possible performance problems and obtain TOP-5 SQL statements: [plain] [mysql @ localhost bin] $. /mysqldumpslow-s al-n 5/home/mysql/log/slow. log Reading mysql slow query log from/home/mysql/log/slow. log Count: 1 Time = 0.00 s (0 s) Lock = 0.00 s (0 s) Rows = 0.0 (0), 0users @ 0 hosts more methods, please consult. /mysqldumpslow -- help Starts from version 5.1.6. Slow query logs can be files, you can also specify the log_output parameter in the database to specify the format of the slow query output. The default value is file. You can also set the parameter log_output to dynamic and global., We can change [plain] mysql> show variables like 'Log _ output' online '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | log_output | FILE | + --------------- + ------- + 1 row in set (0.00 sec) mysql> set global log_output = 'table'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'Log _ output '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | log_output | TABLE | + --------------- + ------- + 1 row in set (0.00 sec) mysql> select sleep (15); + ----------- + | sleep (15) | + ----------- + | 0 | + ----------- + 1 row in set (15.02 sec) mysql> select * from mysql. slow_log \ G; ***************************** 1. row ************************** start_time: 01:22:29 user_host: root [root] @ localhost [] query_time: 00:00:15 lock_time: 00:00:00 rows_sent: 1 rows_examined: 0 db: test last_insert_id: 0 insert_id: 0 server_id: 1 SQL _text: select sleep (15) 1 row in set (0.00 sec) in this example, if I set sleep for 15 seconds, this SQL statement will be recorded in the slow_log table. Note that, slow query logs may record statements related to user permissions or passwords. Therefore, you must pay attention to the security of slow query log files.