MySQL Performance tuning

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1.表级锁状态

mysql> show status like 'table%';

+----------------------------+-----------+

| Variable_name | Value |

+----------------------------+-----------+

| Table_locks_immediate | 139306577 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 0 |

| Table_open_cache_misses | 0 |

| Table_open_cache_overflows | 0 |

+----------------------------+-----------+

5 rows in set (0.00 sec)

● Table_locks_immediate:产生表级锁定的次数;
● Table_locks_waited:出现表级锁定争用而发生等待的次数; 两个状态值都是从系统启动后开始记录,没出现一次对应的事件则数量加 1。如果这里的

Table_locks_waited 状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了。

2.InnoDB行级锁状态

mysql> show status like 'innodb_row_lock%';

+-------------------------------+---------+

| Variable_name | Value |

+-------------------------------+---------+

| Innodb_row_lock_current_waits | 2 |

| Innodb_row_lock_time | 8351372 |

| Innodb_row_lock_time_avg | 248 |

| Innodb_row_lock_time_max | 1678 |

| Innodb_row_lock_waits | 33578 |

+-------------------------------+---------+

5 rows in set (0.00 sec)

Innodb 的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长,每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下:

● Innodb_row_lock_current_waits:当前正在等待锁定的数量;
● Innodb_row_lock_time:从系统启动到现在锁定总时间长度;

● Innodb_row_lock_time_avg:每次等待所花平均时间;

● Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间; ●Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

对于这 5 个状态变量,比较重要的主要是 Innodb_row_lock_time_avg(等待平均时长), Innodb_row_lock_waits(等待总次数)以及 Innodb_row_lock_time(等待总时长)这三项。尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。

此外,Innodb 出了提供这五个系统状态变量之外,还提供的其他更为丰富的即时状态信息供我们分析使用。可以通过如下方法查看:

1. 通过创建 Innodb Monitor 表来打开 Innodb 的 monitor 功能:

mysql> create table innodb_monitor(a int) engine=innodb; Query OK, 0 rows affected (0.07 sec)

2. 然后通过使用“SHOW INNODB STATUS”查看细节信息(由于输出内容太多就不在此记录了);

可能会有读者朋友问为什么要先创建一个叫 innodb_monitor 的表呢?因为创建该表实际上就是告诉 Innodb 我们开始要监控他的细节状态了,然后 Innodb 就会将比较详细的事务以及锁定信息记录进入 MySQL 的 error log 中,以便我们后面做进一步分析使用。

查看锁详细信息：

mysql> select * from information_schema.INNODB_LOCKS;

Empty set (0.01 sec)

[ec2-user@ip-172-31-7-8 ~]$ sudo tail -n 100 /var/local/mysql/data/ip-172-31-7-8-slow.log

mysql> explain

3.Binlog 相关参数及优化策略

mysql> show variables like '%binlog%';

+-----------------------------------------+----------------------+

| Variable_name | Value |

+-----------------------------------------+----------------------+

| binlog_cache_size | 1048576 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | IGNORE_ERROR |

| binlog_format | MIXED |

| binlog_gtid_simple_recovery | OFF |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_image | FULL |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlogging_impossible_mode | IGNORE_ERROR |

| innodb_api_enable_binlog | OFF |

| innodb_locks_unsafe_for_binlog | OFF |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| simplified_binlog_gtid_recovery | OFF |

| sync_binlog | 0 |

+-----------------------------------------+----------------------+

19 rows in set (0.00 sec)

“binlog_cache_size":在事务过程中容纳二进制日志 SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存,注意,是每个Client都可以分配设置大小的binlog cache空间。如果读者朋友的系统中经常会出现多语句事务的华,可以尝试增加该值的大小,以获得更有的性能。当然,我们可以通过 MySQL 的以下两个状态变量来判断当前的 binlog_cache_size 的状况:Binlog_cache_use 和 Binlog_cache_disk_use。

“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 日志最大值,一般来说设置为 512M 或者 1G,但不能超过 1G。该大小并不能非常严格控制 Binlog 大小,尤其是当到达 Binlog 比较靠近尾部而又遇到一个较大事务的时候,系统为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有 SQL 都记录进入当前日志, 直到该事务结束。这一点和 Oracle 的 Redo 日志有点不一样,因为 Oracle 的 Redo 日志所记录的是数据文件的物理位置的变化,而且里面同时记录了 Redo 和 Undo 相关的信息,所以同一个事务是否在一个日志中对 Oracle 来说并不关键。而 MySQL 在 Binlog 中所记录的是数据库逻辑变化信息,MySQL 称之为 Event, 实际上就是带来数据库变化的 DML 之类的 Query 语句。

| 1048576 | | OFF | | 4294967295 | | 1073741824 | |0 |

“sync_binlog”:这个参数是对于 MySQL 系统来说是至关重要的,他不仅影响到 Binlog 对 MySQL 所带来的性能损耗,而且还影响到 MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下:

●   sync_binlog=0,当事务提交之后,MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘,而让 Filesystem 自行决定什么时候来做同步,或者 cache 满了之后才同步到磁盘。
●   sync_binlog=n,当每进行 n 次事务提交之后,MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

在 MySQL 中系统默认的设置是 sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统 Crash,在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为 1 的时候,即使系统 Crash,也最多丢失 binlog_cache 中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。

大家都知道,MySQL 的复制(Replication),实际上就是通过将 Master 端的 Binlog 通过利用 IO 线程通过网络复制到 Slave 端,然后再通过 SQL 线程解析 Binlog 中的日志再应用到数据库中来实现的。所以,Binlog 量的大小对 IO 线程以及 Msater 和 Slave 端之间的网络都会产生直接的影响。

MySQL 中 Binlog 的产生量是没办法改变的,只要我们的 Query 改变了数据库中的数据,那么就必须将该 Query 所对应的 Event 记录到 Binlog 中。那我们是不是就没有办法优化复制了呢?当然不是,在 MySQL 复制环境中,实际上是是有 8 个参数可以让我们控制需要复制或者需要忽略而不进行复制的 DB 或

者 Table ●

● ● ● ● ● ● ●

的,分别为:
Binlog_Do_DB:设定哪些数据库(Schema)需要记录 Binlog; Binlog_Ignore_DB:设定哪些数据库(Schema)不要记录 Binlog; Replicate_Do_DB:设定需要复制的数据库(Schema),多个 DB 用逗号(“,”)分隔; Replicate_Ignore_DB:设定可以忽略的数据库(Schema); Replicate_Do_Table:设定需要复制的 Table; Replicate_Ignore_Table:设定可以忽略的 Table;
Replicate_Wild_Do_Table:功能同 Replicate_Do_Table,但可以带通配符来进行设置; Replicate_Wild_Ignore_Table:功能同 Replicate_Ignore_Table,可带通配符设置;

通过上面这八个参数,我们就可以非常方便按照实际需求,控制从 Master 端到 Slave 端的 Binlog 量尽可能的少,从而减小 Master 端到 Slave 端的网络流量,减少 IO 线程的 IO 量,还能减少 SQL 线程的解析与应用 SQL 的数量,最终达到改善 Slave 上的数据延时问题。

实际上,上面这八个参数中的前面两个是设置在 Master 端的,而后面六个参数则是设置在 Slave 端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系,但是对于优化 MySQL 的 Replication 来说都可以启到相似的功能。当然也有一定的区别,其主要区别如下:

● 如果在 Master 端设置前面两个参数,不仅仅会让 Master 端的 Binlog 记录所带来的 IO 量减少, 还会让 Master 端的 IO 线程就可以减少 Binlog 的读取量,传递给 Slave 端的 IO 线程的 Binlog 量自然就会较少。这样做的好处是可以减少网络 IO,减少 Slave 端 IO 线程的 IO 量,减少 Slave 端的 SQL 线程的工作量,从而最大幅度的优化复制性能。当然,在 Master 端设置也存在一定的弊端,因为 MySQL 的判断是否需要复制某个 Event 不是根据产生该 Event 的 Query 所更改的数据

所在的 DB,而是根据执行 Query 时刻所在的默认 Schema,也就是我们登录时候指定的 DB 或者运行“USE DATABASE”中所指定的 DB。只有当前默认 DB 和配置中所设定的 DB 完全吻合的时候 IO 线程才会将该 Event 读取给 Slave 的 IO 线程。所以如果在系统中出现在默认 DB 和设定需要复制的 DB 不一样的情况下改变了需要复制的 DB 中某个 Table 的数据的时候,该 Event 是不会被复制到 Slave 中去的,这样就会造成 Slave 端的数据和 Master 的数据不一致的情况出现。同样,如果在默认 Schema 下更改了不需要复制的 Schema 中的数据,则会被复制到 Slave 端,当 Slave 端并没有该 Schema 的时候,则会造成复制出错而停止;

● 而如果是在 Slave 端设置后面的六个参数,在性能优化方面可能比在 Master 端要稍微逊色一点,因为不管是需要还是不需要复制的 Event 都被会被 IO 线程读取到 Slave 端,这样不仅仅增加了网络 IO 量,也给 Slave 端的 IO 线程增加了 Relay Log 的写入量。但是仍然可以减少 Slave 的 SQL 线程在 Slave 端的日志应用量。虽然性能方面稍有逊色,但是在 Slave 端设置复制过滤机制,可以保证不会出现因为默认 Schema 的问题而造成 Slave 和 Master 数据不一致或者复制出错的问题。

5.Slow Query Log 相关参数及使用建议

mysql> show variables like 'log_slow%';

+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| log_slow_admin_statements | ON |

| log_slow_slave_statements | OFF |

+---------------------------+-------+

2 rows in set (0.00 sec)

mysql> show variables like 'long_query%';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 2.000000 |

+-----------------+----------+

1 row in set (0.00 sec)

“ log_slow_queries”参数显示了系统是否已经打开 Slow Query Log 功能,而 “long_query_time”参数则告诉我们当前系统设置的 Slow Query 记录执行时间超过多长的 Query。在 MySQL AB 发行的 MySQL 版本中 Slow Query Log 可以设置的最短慢查询时间为 1 秒,这在有些时候可能没办法完全满足我们的要求,如果希望能够进一步缩短慢查询的时间限制,可以使用 Percona 提供的 microslow-patch(件成为 msl Patch)来突破该限制。msl patch 不仅仅能将慢查询时间减小到毫秒级别,同时还能通过一些特定的规则来过滤记录的 SQL,如仅记录涉及到某个表的 Slow Query 等等附加功能。考虑到篇幅问题,这里就不介绍 msl patch 给我们带来的更为详细的功能和使用,大家请参考官方介绍 ( http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch- installation-walk-through/)

6.Query Cache 的相关系统参数变量和状态变量

mysql> show variables like '%query_cache%';

+------------------------------+----------+

| Variable_name | Value |

+------------------------------+----------+

| have_query_cache | YES |

| query_cache_limit | 2097152 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 67108864 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

+------------------------------+----------+

6 rows in set (0.00 sec)

●   “have_query_cache”:该 MySQL 是否支持 Query Cache;
●   “query_cache_limit”:Query Cache 存放的单条 Query 最大 Result Set ,默认 1M;
●   “query_cache_min_res_unit”:Query Cache 每个 Result Set 存放的最小内存大小,默认

4k;
●   “query_cache_size”:系统中用于 Query Cache 内存的大小;
●   “query_cache_type”:系统是否打开了 Query Cache 功能;
●   “query_cache_wlock_invalidate”:针对于 MyISAM 存储引擎,设置当有 WRITE LOCK 在某个

Table 上面的时候,读请求是要等待 WRITE LOCK 释放资源之后再查询还是允许直接从 Query Cache 中读取结果,默认为 FALSE(可以直接从 Query Cache 中取得结果)。

以上参数的设置主要是“query_cache_limit”和“query_cache_min_res_unit”两个参数的设置需要做一些针对于应用的相关调整。如果我们需要 Cache 的 Result Set 一般都很小(小于 4k)的话,可以适当将 “ query_cache_min_res_unit ” 参数再调小一些 , 避免造成内存的浪费 , “query_cache_limit”参数则不用调整。而如果我们需要Cache的 Result Set 大部分都大于4k的话, 则最好将“query_cache_min_res_unit”调整到和 Result Set 大小差不多,“query_cache_limit”的参数也应大于 Result Set 的大小。当然,可能有些时候我们比较难准确的估算 Result Set 的大小, 那么当 Result Set 较大的时候,我们也并不是非得将“query_cache_min_res_unit”设置的和每个 Result Set 差不多大,是每个结果集的一半或者四分之一大小都可以,要想非常完美的完全不浪费任何内存确实也是不可能做到的。

如果我们要了解Query Cache的使用情况,则可以通过Query Cache相关的状态变量来获取,如通过如下命令:

mysql> show status like 'Qcache%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 67091112 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 15023846 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+-------------------------+----------+

8 rows in set (0.00 sec)

●   “Qcache_free_blocks”:Query Cache 中目前还有多少剩余的 blocks。如果该值显示较大, 则说明 Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理()。
●   “Qcache_free_memory”:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的 Query Cache 内存大小是否足够,是需要增加还是过多了;
●   “Qcache_hits”:多少次命中。通过这个参数我们可以查看到 Query Cache 的基本效果;
●   “Qcache_inserts”:多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两

个参数我们就可以算出 Query Cache 的命中率了:

Query Cache 命中率 = Qcache_hits / ( Qcache_hits + Qcache_inserts );

●   “Qcache_lowmem_prunes”:多少条 Query 因为内存不足而被清除出 Query Cache。通过 “Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系

统中Query Cache的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query被换

出
●   “Qcache_not_cached”:因为 query_cache_type 的设置或者不能被 cache 的 Query 的数量;
●   “Qcache_queries_in_cache”:当前 Query Cache 中 cache 的 Query 数量;
●   “Qcache_total_blocks”:当前 Query Cache 中的 block 数量;

Query Cache 的限制
Query Cache 由于存放的都是逻辑结构的 Result Set,而不是物理的数据页,所以在性能提升的同
```
时,也会受到一些特定的限制。
```

a)   5.1.17 之前的版本不能 Cache 帮定变量的 Query,但是从 5.1.17 版本开始,Query Cache 已经

开始支持帮定变量的 Query 了;
b)   所有子查询中的外部查询 SQL 不能被 Cache;
c)   在 Procedure,Function 以及 Trigger 中的 Query 不能被 Cache;
d)   包其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache。