mysql缓冲命中

骑猪看日落 2022-06-06 12:46 244阅读 0赞

缓冲池是一块内存区域，在对数据库进行读取操作时，首先将数据从磁盘中读取到缓冲池中，那缓冲池命中率则是客户端请求的数据在直接在缓冲池获取的比例。想象下如果缓冲池命中率很低，大部分数据都要从磁盘读取，可想而知一个数据库的性能。作为一个mysql OLTP系统，缓冲命中率最好在百分99以上。下面介绍几种mysql查看缓冲命中率的方法：  
方法一：  
mysql> show engine innodb status\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  Type: InnoDB  
  Name:   
Status:   
=====================================  
2016-06-07 09:40:38 7f593136f700 INNODB MONITOR OUTPUT  
=====================================  
Per second averages calculated from the last 6 seconds       \#以下信息是最近6秒的平均值  
.............................................  
.............................................  
Buffer pool size        393208  
Buffer pool size, bytes 6442319872  
Free buffers            8807  
Database pages          373575  
Old database pages      137869  
Modified db pages       43  
Pending reads 0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 1132357, not young 92318292  
2.33 youngs/s, 2496.75 non-youngs/s  
Pages read 2127203, created 1317816, written 1762250  
18.00 reads/s, 3.83 creates/s, 14.33 writes/s  
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 314 / 1000      \#缓冲池命中率  
结果：最近6秒缓冲池命中率是99.8%

innodb\_buffer\_pool\_size

innodb\_buffer\_pool\_size 参数用来设置Innodb 最主要的Buffer(Innodb\_Buffer\_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb 整体性能影响也最大。

对于一台单独给MySQL 使用的主机，并假设只使用innodb引擎，一般建议该参数为物流内存的75%左右。

当[系统][Link 1]上线之后，我们可以通过Innodb 存储引擎提供给我们的关于Buffer Pool 的实时状态信息作出进一步分析，来确定系统中Innodb 的Buffer Pool 使用情况是否正常高效：

mysql> show status like 'Innodb_buffer_pool_%'; +-----------------------------------------+---------------+ | Variable_name | Value | +-----------------------------------------+---------------+ | Innodb_buffer_pool_pages_data | 999020 | | Innodb_buffer_pool_pages_dirty | 47643 | | Innodb_buffer_pool_pages_flushed | 474668167 | | Innodb_buffer_pool_pages_LRU_flushed | 365125 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_made_not_young | 0 | | Innodb_buffer_pool_pages_made_young | 203410903 | | Innodb_buffer_pool_pages_misc | 49552 | | Innodb_buffer_pool_pages_old | 368697 | | Innodb_buffer_pool_pages_total | 1048572 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 66348855 | | Innodb_buffer_pool_read_ahead_evicted | 3716819 | | Innodb_buffer_pool_read_requests | 3215992991498 | | Innodb_buffer_pool_reads | 65634998 | | Innodb_buffer_pool_wait_free | 651 | | Innodb_buffer_pool_write_requests | 21900970785 | +-----------------------------------------+---------------+

从上面的值我们可以看出总共1048572个 pages，其中放数据的有999020个 pages，且已没有free状态的page。  
read 请求3215992991498次，其中有65634998次所请求的数据在buffer pool 中没有，也就是说有65634998 次是通过读取物理磁盘来读取数据的，所以很容易也就得出了Innodb Buffer Pool 的Read 命中率大概在为：(3215992991498- 65634998)/ 3215992991498\* 100% = 99.998%。

**innodb\_buffer\_pool\_instances**

该参数将innodb\_buffer\_pool划分为不同的instance，每个instance独立的LRU、FLUSH、FREE、独立的mutex控制。

对于比较大的innodb\_buffer\_pool\_size，建议设置多个instances，避免内存锁的争用。

**innodb\_log\_file\_size**

设置innodb redo log file的大小，从性能角度来看，日志文件越大越好，可以减少buffer pool checkpoint的频率，但是在MySQL的官方版本中，innodb\_log\_files\_in\_group\*innodb\_log\_files\_in\_group不能超过4G。

日志文件越大，也意味着MySQL实例crash之后恢复的时间越长，不过一般生成系统都会配置主从库，因此这个因素可以忽略不考虑。

一般来说，在我个人维护的环境中，比较偏向于将事务日志设置为3 组，每个日志设置为256MB 大小，整体效果还算不错。

**innodb\_log\_buffer\_size**

顾名思义，这个参数就是用来设置Innodb 的Log Buffer 大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log 数据，提高写Log 的IO 性能。一般来说，如果你的系统不是“写负载非常高且以大事务居多”的话，8MB 以内的大小就完全足够了。

我们也可以通过系统状态参数提供的性能统计数据来分析Log 的使用情况：

mysql> show status like 'innodb_log%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | Innodb_log_waits | 0 | | Innodb_log_write_requests | 3486920147 | | Innodb_log_writes | 352577360 | +---------------------------+------------+

如果Innodb\_log\_waits不等于0的话，表示出现过Log Buffer的写等待，表示innodb\_log\_buffer\_size有可能过小。

**innodb\_thread\_concurrency**

该参数表示innodb最大线程并发量，官方推荐设为0，表示由innodb自己控制，但实践证明，当并发过大时，innodb自己会控制不当，可能导致MySQL hang死，所以一般建议为CPU核心数（不含超线程）

**innodb\_io\_capacity**

表示每秒钟IO设备处理数据页的上限，如果硬盘性能比较好，可以设大一些（如1000）。

**innodb\_max\_dirty\_pages\_pct**

表示innodb从buffer中刷新脏页的比例不超过这个值，每次checkpoint的脏页刷新为：innodb\_max\_dirty\_pages\_pct\*innodb\_io\_capacity

**Innodb\_flush\_method**

用来设置Innodb 打开和同步数据文件以及日志文件的方式，不过只有在[Linux][] & Unix 系统上面有效。当我们设置为O\_DSYNC，则系统以O\_SYNC 方式打开和刷新日志文件， 通过fsync() 来打开和刷新数据文件。而设置为O\_DIRECT 的时候， 则通过O\_DIRECT（Solaris 上为directio()）打开数据文件，同时以fsync()来刷新数据和日志文件。  
总的来说，innodb\_flush\_method 的不同设置主要影响的是Innodb 在不同运行平台下进行IO 操作的时候所调用的操作系统IO 借口的区别。而不同的IO 操作接口对数据的处理方式会有一定的区别，所以处理性能也会有一定的差异。一般来说，如果我们的磁盘是通过RAID 卡做了硬件级别的RAID，建议可以使用O\_DIRECT，可以一定程度上提高IO 性能，但如果RAID Cache 不够的话，还是需要谨慎对待。

**innodb\_file\_per\_table**

一般建议开启，因为不同的表空间可以灵活设置数据目录的地址，避免共享表空间产生的IO竞争。

**innodb\_flush\_log\_at\_trx\_commit**

innodb\_flush\_log\_at\_trx\_commit = 0，Innodb 中的Log Thread 每隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush 操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread 将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。  
  
innodb\_flush\_log\_at\_trx\_commit = 1，这也是Innodb 的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer 中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提交的数据。  
  
innodb\_flush\_log\_at\_trx\_commit = 2，当我们设置为2 的时候，Log Thread 会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据的丢失，但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。  
  
从上面的分析我们可以看出，当innodb\_flush\_log\_at\_trx\_commit 设置为1 的时候是最安全的，但是由于所做的IO 同步操作也最多，所以性能也是三种设置中最差的一种。如果设置为0，则每秒有一次同步，性能相对高一些。如果设置为2，可能性能是三这种最好的。但是也可能是出现Crash后丢失数据最多的。到底该如何设置设置，就要根据具体的场景来分析了。一般来说，如果完全不能接受数据的丢失，那么我们肯定会通过牺牲一定的性能来换取数据的安全性，选择设置为1。而如果我们可以丢失很少量的数据(比如说1 秒之内)，那么我们可以设置为0。当然，如果大家觉得我们的OS 足够稳定，主机硬件设备，而且主机的供电系统也足够安全，我们也可以将innodb\_flush\_log\_at\_trx\_commit 设置为2 让系统的整体性能尽可能的高。

**transaction-isolation**

对于高并发应用来说，为了尽可能保证数据的一致性，避免并发可能带来的数据不一致问题，自然是事务隔离级别越高越好。但是，对于Innodb 来说，所使用的事务隔离级别越高，实现复杂度自然就会更高，所需要做的事情也会更多，整体性能也就会更差。

所以，我们需要分析自己应用系统的逻辑，选择可以接受的最低事务隔离级别。以在保证数据安全一致性的同时达到最高的性能。  
虽然Innodb 存储引擎默认的事务隔离级别是REPEATABLE READ，但实际上在我们大部分的应用场景下，都只需要READ COMMITED 的事务隔离级别就可以满足需求了。

**sync\_binlog**

表示每次刷新binlog到磁盘的数目。

对于核心系统，我们需要采用双1模式，即：innodb\_flush\_log\_at\_trx\_commit=1, sync\_binlog=1，这样可以保证主备库数据一致，不会有数据丢失。

MySQL缓存命中率概述

工作原理：

查询缓存的工作原理，基本上可以概括为： 缓存SELECT操作或预处理查询（注释：5.1.17开始支持）的结果集和SQL语句； 新的SELECT语句或预处理查询语句，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写；

查询缓存对什么样的查询语句，无法缓存其记录集，大致有以下几类：

1、 查询语句中加了SQL\_NO\_CACHE参数；

2、查询语句中含有获得值的函数，包涵自定义函数，如：CURDATE()、GET\_LOCK()、RAND()、CONVERT\_TZ等；

3、 对系统数据库的查询：mysql、information\_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量；

4、查询语句中使用了LOCK  IN SHARE MODE、FOR UPDATE的语句 查询语句中类似SELECT …INTO 导出数据的语句；

5、对临时表的查询操作； 存在警告信息的查询语句； 不涉及任何表或视图的查询语句； 某用户只有列级别权限的查询语句；

6、 事务隔离级别为：Serializable情况下，所有查询语句都不能缓存；

配置

是否启用mysql查询缓存，可以通过2个参数：query\_cache\_type和query\_cache\_size，其中任何一个参数设置为0都意味着关闭查询缓存功能。

query\_cache\_type 值域为：

0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache;

1(ON)： 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集斗可以 缓存起来，共其他客户端使用；

2(DEMAND):  启用查询缓存，只要查询语句中添加了参数：sql\_cache，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，共其他客户端使用；

query\_cache\_size 允许设置query\_cache\_size的值最小为40K，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大， 查询缓存的命中率就越高，也不是对服务器负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置 为：64M；建议设置不要超过256MB

缓存选项的说明：

用show global status like 'QCache%';查看

Qcache\_free\_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目

Qcache\_free\_memory：目前还处于空闲状态的 Query Cache 内存总量

Qcache\_hits：Query Cache 命中次数

Qcache\_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数

Qcache\_lowmem\_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache\_not\_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query\_cache\_type 设置的不会被 Cache 的 SQL

Qcache\_queries\_in\_cache：目前在 Query Cache 中的 SQL 数量

Qcache\_total\_blocks：Query Cache 中总的 Block 数量

内存碎片的产生。当一块分配的内存没有完全使用时，MySQL会把这块内存Trim掉，把没有使用的那部分归还以重 复利用。比如，第一次分配4KB,只用了3KB，剩1KB，第二次连续操作，分配4KB，用了2KB，剩2KB，这两次连续操作共剩下的 1KB+2KB=3KB，不足以做个一个内存单元分配， 这时候，内存碎片便产生了。使用flush query cache，可以消除碎片

下面是命中率和内存使用率的一些算法

query\_cache\_min\_res\_unit的估计值：(query\_cache\_size - Qcache\_free\_memory) / Qcache\_queries\_in\_cache

查询缓存命中率 ≈ (Qcache\_hits – Qcache\_inserts) / Qcache\_hits \* 100%

查询缓存内存使用率 ≈ (query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%

InnoDB存储引擎的缓冲池

通常InnoDB存储引擎缓冲池的命中不应该小于99%。

参数说明：  
Innodb\_buffer\_pool\_reads: 表示从物理磁盘读取页的次数

Innodb\_buffer\_pool\_read\_ahead: 预读的次数

Innodb\_buffer\_pool\_read\_ahead\_evicted: 预读的页，但是没有读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率

Innodb\_buffer\_pool\_read\_requests: 从缓冲池中读取页的次数

Innodb\_data\_read: 总共读入的字节数

Innodb\_data\_reads: 发起读取请求的次数，每次读取可能需要读取多个页

Innodb缓冲池命中率计算：

![211007099067644.png][]

提高缓存命中率

众所周知，系统读取数据时，从[内存][Link 2]中读取要比从[硬盘][Link 3]上速度要快好几百倍。故现在绝大部分应用系统，都会最大程度的使用缓存([内存][Link 4]中的一个存储区域)，来提高系统的运行效率。MySQL数据库也不例外。在这里，笔者将结合自己的工作经验，跟大家探讨一下，MySQL数据库中缓存的管理技巧：如何合理配置MySQL数据库缓存，提高缓存命中率。

什么时候应用系统会从缓存中获取数据?

[数据库][Link 5]从[服务器][Link 6]上读取数据时，可以从[硬盘][Link 7]的数据文件中获取数据，也可以从数据库缓存中读取数据。现在数据库管理员需要搞清楚的是，在什么样的情况下，系统是从缓存中读取数据，而不是从硬盘的数据文件中读取数据?

简单的说，数据缓存就是内存中的一块存储区域，其存储了用户的SQL文本以及相关的查询结果。通常情况下，用户下次查询时，如果所使用的SQL文本是相同的，并且自从上次查询后，相关的纪录没有被更新过，此时数据库就直接采用缓存中的内容。从这个原则中，可以看到如果要直接使用缓存中的数据，至少要满足以下几个条件。

一是所采用的SQL文本是相同的。当前后两次用户使用了相同的SQL语句(假设不考虑其他条件)，则[服务器][Link 8]会从缓存中读取结果，而不需要再去解析和执行SQL语句。这里需要注意的是，这里的SQL文本必须一次不差的完全相同。如果前后两次查询，使用了不同的查询条件。如第一次查询时没有输入Where条件语句。后来发现数据量过多，利用了Where条件了过滤查询的结果。此时即使最后的查询结果是相同的，系统仍然是从数据文件中获取数据，而不是从数据缓存中。再如，Select后面所使用的字段名称也必须是相同的。如果有一个字段名称不同或者前后两次查询所使用的字段数量不同，则系统都会认为是不同的SQL语句，而重新解析并查询。

二是从数据缓存的角度考虑，大小写是不敏感的。如前后两次查询时，采用的字段名称可能只有大小写的差异。如第一次使用的是大小，第二次使用的是小写，这系统认为仍然是相同的SQL语句。或者说关键字大小写等等这都是不敏感的。

三是要满足二次查询之间，数据记录包括表结构都没有被更改过。如果记录所在的标更改了，如增加了一个字段等等，此时使用这个表的所有缓冲数据系统将自动清空。这里需要注意，这里指的更改是一个广义的更改，包括表中任何数据或者结果的改变。举一个简单的例子，第一次查询时用户需要查询2010年的出货数据。查询后有用户在这个表中插入了一条2011年1月份的出货信息。然后又有用户需要查询2010年的出货信息。使用的SQL语句与第一次查询时完全相同。在这种情况下，数据库系统会使用缓存中的数据吗?答案是否定的。因为当中间用户插入一条记录时，系统会自动清空跟这个表相关的所有缓存记录。当第二次查询时，缓存中已经没有这张表对应的缓存信息。此时就需要重新解析并查询。

四是需要注意，默认字符集对缓存命中率的影响。通常情况下，如果客户端与服务器之间所采用的默认字符集不同，则即使查询语句相同、在两次查询之间记录与表结构也没有被更改，系统仍然认为是不同的查询。对于这一点需要特别的注意，大家比较容易忽视。

提高缓存命中率的建议

从上面的条件分析中可以看出，利用缓存中的数据具有比较严格的条件。其实这些条件也是合情合理的。主要是为了保障数据的一致性。对以上这些条件有深入的认识之后，现在数据库管理员需要考虑的是，如何来提高这个缓存的命中率?对此笔者有如下几个建议。

一是在配置时，客户端与服务器端要使用相同的字符集。如果客户端(或者说第三方工具)与服务器端使用的字符集不同，那么任何情况下都不会使用缓存功能。特别在国内，需要用到中文的字符集。此时特别需要注意，客户端默认字符集要与服务器端的默认字符集相同。注意，这里是相同，而不是兼容。有时候即使采用了不同的字符集，客户端上仍然可以正常显示。这主要是因为有些字符集虽然不相同，但是是相互兼容的。在缓存管理上，需要相同，光兼容还不行。

二是在客户端上，要固化查询的语句。如现在有财务人员和采购人员同时从系统中查询11月份的出货数据。显然他们岗位职责不同，所需要字段的内容是不同的。此时在客户端出，可以允许用户设置自己所需要的表单格式。但是笔者建议，后台所采用的SQL语句最好是相同的。这里数据会经过三个渠道：后台数据库、客户端、用户。笔者的意识时，后台数据库与客户端之间的交互采用相同的SQL语句。然后客户端与用户之间进行交互时，根据用户定义的格式(包括字段前后的排列、不包括查询条件语句的差异)向用户显示数据。此时由于采用了相同的SQL语句(只是用户对于显示格式的要求不同)，从而可以提高应用系统的查询效率。

三是提高内存中缓存的配置，来提高命中率。一般在服务器启动时，[操作系统][Link 9]会跟数据库软件协商缓存空间的大小。当缓存工作不足时，缓存中最旧的缓存记录会被最新的消息所覆盖。可见，如果能够提高缓存空间，就可以提高命中率。这就好像打靶，目标多了，命中的几率也会高许多。不过用户的并发数越多，这个设置的效果会越不明显。

四是通过分区表可以提高缓存的命中率。在上面的条件分析中，大家可以看到，只要所查询的表中插入了一条记录，系统就会清空缓存记录。现在以查询出货记录为例。出货记录表每天都在更新，而用户在年初时，会经常需要查询上一年的出货记录。此时由于这个表中的数据每个小时都在更新，那么缓存中的信息会不断的被情况。此时缓存的命中率显然不会很高。针对这种情况，笔者建议可以采用分区表。如可以通过系统设置，将2010年的出货记录单独存放在一个出货的分区表中。即每一个年度都使用一张单独的分区表。此时2011年的纪录，就不会影响到2010年的分区表。此时如果用户重复查询2010年的出货信息，只要其使用的SQL语句相同(没有采用不同的查询条件)，那么就可以享受缓存机制所带来的效益，提高应用系统的查询效果。。

多个应用对缓存的影响

通常情况下，[MySQL][Link 5]数据库的缓存是根据[服务器内存][Link 10]的大小自动分配的。如果一台服务器上只有一个MySQL应用，那么固然最好。不过在实际工作中，为了降低信息化投资的成本，往往会在同一台服务器上布置多个信息化应用。由于其他信息化应用也需要使用内存的空间作为缓存，那么MySQL数据库中缓存空间就可能变小。如果遇到这种情况下，数据库管理员需要跟系统工程师进行协商，为各种不同的应用根据性能要求的不同，手工设置不同的缓存空间。如此的话，就可以避免同一台服务器上不同信息化应用对缓存的冲突。

· 尽量用小表的简单替代大表的复杂查询

· 尽量用批量写入取代单条写入

· 控制query\_cache\_size的大小，甚至是禁用查询缓存

· 通过DEMAND+SQL\_CACHE/SQL\_NO\_CACHE来灵活控制某个select是否需要进行缓存

· 对于写密集型的应用，直接禁用查询缓存

[Link 1]: https://www.2cto.com/os/
[Linux]: https://www.2cto.com/os/linux/
[211007099067644.png]: /images/20220606/0c142b63b46946adbb5af793f146cc67.png
[Link 2]: http://product.it168.com/list/b/0205_1.shtml
[Link 3]: http://product.it168.com/list/b/0218_1.shtml
[Link 4]: http://product.pcpop.com/Memory/10734_1.html
[Link 5]: http://lib.csdn.net/base/mysql
[Link 6]: http://product.it168.com/list/b/0402_1.shtml
[Link 7]: http://product.pcpop.com/HDD/10734_1.html
[Link 8]: http://product.pcpop.com/Server/10734_1.html
[Link 9]: http://product.it168.com/list/b/0501_1.shtml
[Link 10]: http://product.it168.com/list/b/1283_1.shtml