MySQL: Dynamically turn on slow query log (Slow query log)

Source: Internet
Author: User

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
    1. 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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.