MySQL 日志设置优化、慢查询日志

MySQL 日志设置优化、慢查询日志

Comments 2 comments

 1. 日志产生的性能影响

          日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的 IO 资源，所以对于日志的优化策略，在 MySQL 性能调优中也至关重要。
          MySQL的日志包括 错误日志（Error log）、更新日志（Update log）、二进制日志（Binlog）、查询日志（Query log）、慢查询日志（Slow Query）。更新日志在老版本的MySQL才有，目前已经被二进制日志替代。

 

2. 二进制日志（Binlog）相关参数及优化策略

          获得 Binlog 相关参数
          mysql> show variables like ‘%binlog%’;
         
  • binlog_cache_size：代表在事务过程中容纳二进制日志 SQL 语句的换成大小。服务器启用了二进制日志（–log-bin 选项）的前提小为每个客户端分配内存，注意，是每个客户端都可以分配设置大小的 binlog cache空间。如果系统中经常会出现多语句事务，可以尝试增加该值大小，获得更优的性能。可通过 binlog_cache_use 和 binlog_cache_disk_use 判断当前 binlog_cache_size 的状况。
  • max_binlog_cache_size：和 binlog_cache_size 想对应，但所代表的是 binlog 能够使用的最大 cache 内存大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，系统可能会报错 “Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage “
  • max_binlog_size：Binlog 最大值，一般设置为 512MB 或 1GB，但不能超过 1GB。
  • sync_binlog：此参数设置不仅影响到 Binlog 对MySQL带来的性能损耗，而且还影响到 MySQL 中数据的完整性。
  • sync_binlog = 0， 当事务提交之后，MySQL 仅仅是将 binlog_cache 中的数据写入 BInlog 文件，但不执行 fsync 之类的磁盘同步指令通知文件系统缓存刷新到磁盘，而让 Filesystem 自行决定什么时候来做同步。
  • sync_binlog = 1，在进行 n 此事务提交之后， MySQL 将执行一次 fsync 之类的磁盘同步指令，通知文件系统将 Binlog 文件的缓存刷新到磁盘
                          MySQL 中系统默认 sync_binlog = 0 ，即不做任何强制性的磁盘刷新指令，性能是最好的，但封信也是最大的。因为系统一旦崩溃（Crash），在系统缓存中的所有 Binlog 信息会丢失。而设置为 1 时，即使系统崩溃，最多只会丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性的影响。但是两者从系统写入性能差距可能高达 5倍，甚至更多。
     3.  慢查询日志（Slow Query Log） 相关参数及使用建议
          为了定位系统中效率比较低下的 Query 语句，必须要打开慢查询日志，也就是 Slow Query Log。
          mysql > show variables like ‘log_slow%’;
         
          mysql > show variables like ‘long_query%’;
         
     4. Query Cache 优化
           MySQL 的 Query Cache 实现原理实际上并不是特别复杂，简单来说就是讲客户端请求的 Query 语句（仅限于 SELECT 类型的 Query ）通过一定的 Hash 算法进行一个计算，得到一个 hash值，存放在一个 hash 桶中。同时该 Query 的结果集（Result Set）也存放在一个内存 Cache 中。存放 Query hash 值的链表中每一个 hash值所在节点的同时，还存放了该 Query 所对应的 Result Set 的Cache 所在的内存地址，以及该 Query 涉及的所有Table 的表示等一些其他相关信息。
Query Cache 之后所带来的负面影响：
  • Query 语句的 hash 运算及 hash 查找资源消耗。
  • Query Cache 的失效问题。
  • Query Cache 中缓存的是 Result Set，而不是数据页，也就是说，同一条记录被 Cache 多次的可能性，从而造成内存资源的过渡消耗。
          Query Cache 的相关系统参数变量和状态变量：
          
               have_query_cache：改 MySQL 是否支持 Query Cache；
               query_cache_limit：Query Cache 存放的单条 Query 最大 Result Set，默认 1MB；
               query_cache_min_res_unit：Query Cache 每个 Result Set 存放的最小内存大小，默认 4KB；
               query_cache_size：系统中用于 Query Cache 内存大小；
               query_cache_type：系统是否打开了 Query Cache 功能；
               quer_cache_wlock_invalidate：针对 MyISAM 存储引擎，设置当有 write lock 在某个 Talbe 上时，读请求是要等待 write lock 释放资源后再查询还是允许直接从 Query Cache中读取结果，默认 FALSE（直接从Query Cache中取得结果）
慢查询优化
查看是否开启慢查询日志
mysql> show variables like ‘slow_query_log’;
+—————-+——-+
| Variable_name  | Value |
+—————-+——-+
| slow_query_log | OFF   |
+—————-+——-+
查看查过多长时间的慢查询记录在日志中，单位秒，通常设置 0.001
mysql> show variables like ‘long_query_time’;
+—————–+———–+
| Variable_name   | Value     |
+—————–+———–+
| long_query_time | 10.000000 |
+—————–+———–+
设置慢查询超时时间
my.cnf 配置 long_query_time=1;          // 设置为超时为 1 秒，配置完毕后重启 mysql 服务
设置开启慢查询日志
mysql> show variables like ‘slow_query_log’;
+—————-+——-+
| Variable_name  | Value |
+—————-+——-+
| slow_query_log | ON    |
+—————-+——-+
开启慢查询日志
mysql> set global slow_query_log=on;
查看慢查询日志保存目录
mysql> show variables like ‘slow_query_log_file’;
+———————+——————————————+
| Variable_name       | Value                                    |
+———————+——————————————+
| slow_query_log_file | /usr/local/mysql/data/centos216-slow.log |
+———————+——————————————+
查看是否开启没有使用索引的查询记录日志中
mysql> show variables like ‘log_queries_not_using_indexes’;
+——————————-+——-+
| Variable_name                 | Value |
+——————————-+——-+
| log_queries_not_using_indexes | OFF   |
+——————————-+——-+
设置开启没有使用索引的查询记录在日志中
mysql> set global log_queries_not_using_indexes=on;
常用慢查询日志分析工具
分析慢查询日志
  1. mysql 自带工具 mysqldumpslow
[root@centos216 mysql]# ./bin/mysqldumpslow -t 3 ./data/centos216-slow.log
Reading mysql slow query log from ./data/centos216-slow.log
Count: 3  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1.0 (3), root[root]@localhost
  select * from innodb where id=N
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global slow_query_log=on
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=9.0 (9), root[root]@localhost
  select * from innodb
   -s  order(c,t,l,r,at,al,ar)
    指定按那种排序方式输出结果：
        c：总次数
        t：总时间
        l：锁的时间
        r：总数据行
        at,al,ar: t,l,r 平均数       例如：at = 总时间/总次数
    -t top
    指定取前几条作为结束输出
  1. mysql 慢查询日志分析工具
CentOS 安装依赖包：
    yum install perl-Digest-MD5
    下载：wget percona.com/get/percona-toolkit.tar.gz 解压即可使用
pt-query-digest  –explain  h=127.0.0.1,u=root,p=root  slow-mysql.log > slow.txt
实时获取有性能问题的SQL
查询执行时间大约 60 秒的SQL
> SELECT id,`user`,`host`,DB,command,`time`,state,info FROM information_schema.PROCESSLIST WHERE TIME >= 60

2 thoughts on “MySQL 日志设置优化、慢查询日志”

  1. 这篇文章写的非常不错，看完之后茅塞顿开，感觉自己的内力有了质的提升，仿佛内心的斗气之火熊熊燃烧，我觉得我的斗气段位已经达到了7星斗者的水准，那么问题来了，mysql 和 mongoDB 各有什么优势？请给我一个完美的解释！

    回复

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注