Objective
In development, high-performance programs also include high-performance queries, so optimizing SQL is also one of the necessary skills for programmers. To optimize, you must have slow logging to know which queries are slow, and then reverse the changes
Slow log setup mode
Write file
Write to Database
Practice how to: Write a file
Edit my.conf to modify Log_slow_queries's log address
$ cd/etc/mysql$ cat my.cnf |grep slow log_slow_queries=/data/logs/mysql/mysql-slow.log$ sudo/etc/init.d/mysql restart
Simple validation
$ mysql -uroot -pmysql> show variables like '%slow_query_log% '; +-------- -------------+---------------------------------+| variable_name | value |+---------------------+-------------------- -------------+| slow_query_log | on | | slow_query_log_file | /data/logs/mysql/mysql-slow.log |+---------------------+----------- ----------------------+2 rows in set (0.00 sec) $ tail -f /data/logs/ Mysql/mysql-slow.log# time: 161110 23:20:22# [email proteCted]: root[root] @ localhost []# query_time: 3.007048 lock_time: 0.000000 rows_sent: 1 rows_examined: 0set timestamp=1478791222;select sleep (3);
Mode two: write to the database
Check the current log output mode first
Mysql> Show variables like '%log_output% '; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_output | FILE |+---------------+-------+1 row in Set (0.00 sec)
Set the output mode to File,table
mysql> set global log_output= ' file,table '; Query OK, 0 rows Affected (0.00 sec) mysql> Show variables like '%log_output% '; +---------------+------------+| variable_name | Value |+---------------+------------+| Log_output | File,table |+---------------+------------+1 row in Set (0.00 sec) mysql> Select COUNT (*) from mysql.slow_log;+-------- --+| COUNT (*) |+----------+| 2 |+----------+1 row in Set (0.00 sec)
Note: log_output parameter sets the output of the log file, the optional value is TABLE, file, NONE; "Table" means that the setting log is recorded separately in the MySQL library's general_log and Slow_log tables; "File" means logging to the operating system of the file, "NONE" means to cancel the log records.
Resources
Http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
Original address: MySQL: Dynamic Open Slow Query log (Slow query log)
Tags: mysql slow_log log log_output query
Smart recommendations
- AppArmor causes custom MySQL log issues
- In MySQL, a zero number equals any string
- MetaWeblog also manages blogs such as 51cto,csdn,sina,163,oschina,cnblogs
- PAC Automatic Proxy
- Free FQ Weapon: shadowsocks
MySQL: Dynamically turn on slow query log (Slow query log)