慢查询有什么用？ 它能记录下所有执行超过long_query_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。 测试用 MySQL 版本。 Server version: 5.6.10-log Source distribution 未做任何慢日志设置时。 mysql show variables like “%que
慢查询有什么用？
它能记录下所有执行超过long_query_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。
测试用 MySQL 版本。
Server version: 5.6.10-log Source distribution
未做任何慢日志设置时。
mysql> show variables like "%query%"; +------------------------------+--------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 <span>本文来源gaodai#ma#com搞*代#码9网#</span> | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/Betty-slow.log | +------------------------------+--------------------------------------+ 13 rows in set (0.01 sec) mysql>
修改配置文件，开启 slow log 。
[root@Betty data]# vi /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. log_bin=mysql-bin slow_query_log=on slow_query_log_file=mysql-slow long_query_time=2 # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] socket = /tmp/mysql.sock
重启 MySQL 。
[root@Betty data]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] [root@Betty data]#
查看 slow log 。
[root@Betty data]# ll mysql-slow -rw-rw---- 1 mysql mysql 719 Sep 6 12:43 mysql-slow
重新查看系统变量值。
mysql> mysql> show variables like "%query%"; +------------------------------+------------+ | Variable_name | Value | +------------------------------+------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 2.000000 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | ON | | slow_query_log_file | mysql-slow | +------------------------------+------------+ 13 rows in set (0.00 sec) mysql>
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQL 之 slow log
转载请注明原文链接：MySQL 之 slow log
