mysql缓冲池怎么用 mysql缓存机制有几种

MySQL的WAL（Write-Ahead Logging）机制

MySQL 里经常说到的 WAL技术，也就是先写日志，再写磁盘。

创新互联-专业网站定制、快速模板网站建设、高性价比滑县网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式滑县网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖滑县地区。费用合理售后完善，10多年实体公司更值得信赖。

当内存数据页跟磁盘数据页内容不一致的时候，我们成这个内存页为“脏页”。内存数据写入磁盘后，内存和磁盘上的数据页内容就一致了，称为“干净页”。

MySQL 从 内存更新到磁盘的过程，称为刷脏页的过程（flush）。

InnoDB 刷脏页的时机：

往前推进之后，就要把两个点之间的日志对应的所有脏页都 flush 到磁盘上。

这种情况是 InnoDB 要尽量避免的。因为出现这种情况，整个系统都不能接受更新。更新数会跌为0。

那么为什么不能直接淘汰所有的内存，下次请求的时候，再从磁盘读入数据页，然后 拿 redo log 出来应用？这其实也是从性能的角度来考虑的，刷脏页一定写盘，就保证了每个数据页只有两种情况：

这种情况在日常应用中其实是常态。 在InnoDB 中，使用缓冲池 （buffer pool）管理内存，缓冲池中的内存页有三种状态：

刷脏页是常态，所以如果出现以下的情况，都会明明显影响性能：

首先，需要让 InnoDB 正确指导系统的 IO 能力，来控制刷脏页的快慢。

innodb_io_capacity 这个参数，它会告诉 InnoDB 你的磁盘能力，所以尽量设置成磁盘的 IOPS。可以使用 fio 工具来获取。

然后，如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？

这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是 redo log 写满。

所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认是 75%。InnoDB 会根据当前的脏页比例，计算出一个数字 F1。

InnoDB 写入日志都会有一个序号，当前写入序号跟 checkpoint 对应的序号之间的差值，假设为N。InnoDB 会根据N 计算出 F2.

根据 F1和F2 取其中较大的值为 R，之后引擎就可以按照 Innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

MySQL 中有一个机制，刷脏页的时候如果数据页旁边的数据页也是脏页，那么就会一起刷掉，而且这个逻辑是可以蔓延的，所以对于每个相邻的数据页，都会被一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

在使用机械硬盘时，这个优化很有意义，可以减少很多随机 IO。如果使用的是 SSD 这种IOPS 比较高的设备，可以设置innodb_flush_neighbors 为0，只刷自己，这个时候 IOPS 往往就不是性能瓶颈了。只刷自己就可以提高刷脏页的速度，减少 SQL 语句的响应时间。

binlog 的写入机制比较简单：事务执行的过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到binlog 文件中。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binglog_cache_size 用于控制单个线程内 binlog cache 的内存大小，超过就要暂存在磁盘。

事务提交的时候，执行器把 binlog cache 里完整事务写入到 binlog 中，并清空 binlog cache。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

事务的执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

redo log 三种状态：

日志写到 redo log buffer 是很快的，write 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，取值如下：

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

组提交 机制

日志逻辑序列号（log sequence number，LSN）是一个单调递增的值，对应 redo log 的一个个写入点。每次写入的长度为 lenght 的 redo log，LSN的值就会加上 length。

LSN 也会写到 InnoDB 的数据页中，来确保数据也不会被多次执行重复的 redo log。

在一组提交里面，组员越多，节约磁盘 IOPS 的效果越好。在并发更新的场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

WAL机制主要得益于两个方面：

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

针对这个问题，可以考虑以下三种方法：

mysql 缓冲池 设置 多大

innodb_buffer_pool_instances 参数，将 buffer pool 分成几个区，每个区用独立的锁保护，这样就减少了访问 buffer pool 时需要上锁的粒度，以提高性能。准备一个空数据库，在这里我们将 performance_schema_events_waits_history_long_size 调大，是为了让之后实验数据能采集的更多，在此不多做介绍。使用 sysbench，准备一些数据，

对数据进行预热 60s，可以看到预热期间的性能会不太稳定，预热后会比较稳定，

设置 performance_schema，这次我们将仅开启观察项（生产者）hash_table_locks，并开启 waits 相关收集端（消费者）。（相关介绍参看 实验 03）

为什么我们知道观察项应该选择 hash_table_locks？在 performance_schema.setup_instruments 表中，列出了所有观察项，但我们很难从中选出我们应观察哪个观察项。这时候，可以将所有观察项都启用，然后设计一些对比实验，比如使用几种不同的 SQL，观察这些操作影响了哪些观察项，找到共性或者区。还有一种高效的方式是搜索别人的经验，或者阅读 MySQL 源码。本例中 hash_table_locks 隐藏的比较深，使用了阅读 MySQL 源码和对比试验结合的方法。

mysql 参数调优（11）之innodb_buffer_pool_instances设置多个缓冲池实例

MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段。在5.5版本这个对提升吞吐量帮助很小，然后在MySQL 5.6版本这个提升就非常大了，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例。设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现。对了，不要指望这个设置能减少你单个查询的响应时间。这个是在高并发负载的服务器上才看得出区别。比如多个线程同时做许多事情。

5.7、8.0 下INNODB_BUFFER_POOL_INSTANCES默认为1，若mysql存在高并发和高负载访问，设置为1则会造成大量线程对BUFFER_POOL的单实例互斥锁竞争，这样会消耗一定量的性能的。

pool_instances 可以设置为cpu核心数，它的作用是：

1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。可以类比为 java中的 ThreadLocal 线程本地变量 就是为每个线程维护一个buffer pool实例，这样就不用去争用同一个实例了。相当于减少高并发下mysql对INNODB_BUFFER缓冲池的争用。

2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表， 刷新列表， LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。