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和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

我们都知道 MySQL 的 Table Cache 是表定义的缓存，江湖上流传着各种对这个参数的调优方法。

table cache 的作用，就是节约读取表结构文件的开销。对于table cache 是否命中，其实table cache 是针对于线程的，每个线程有自己的缓存，只缓存本线程的表结构定义。不过我们发现，strace 中没有关于表结构文件的 open *** 作（只有 stat *** 作，定位表结构文件是否存在），也就是说 table cache 不命中，不一定需要读取表结构文件。这种感觉好像是：在不命中 table cache 时，命中了另外一个表结构缓存。

运维建议：

我们读一下 MySQL 的文档，关于 table_open_cache 的建议值公式：建议值 = 最大并发数 * join 语句涉及的表的最大个数。

通过实验我们容易理解：table_cache 是针对于线程的，所以需要最大并发数个缓存。另外，一个语句 join 涉及的表，需要同时在缓存中存在。所以最小的缓存大小，等于语句 join 涉及的表的最大个数。将这两个数相乘，就得到了 MySQL 的建议值公式。

增加线程缓存大小

连接管理器线程处理服务器监听的网络接口上的客户端连接请求。连接管理器线程将每个客户端连接与专用于它的线程关联，该线程负责处理该连接的身份验证和所有请求处理。因此，线程和当前连接的客户端之间是一对一的比例。确保线程缓存足够大以容纳所有传入请求是非常重要的。

MySQL提供了许多与连接线程相关的服务器变量：

线程缓存大小由thread_cache_size系统变量决定。默认值为0（无缓存），这将导致为每个新连接设置一个线程，并在连接终止时需要处理该线程。如果希望服务器每秒接收数百个连接请求，那么应该将thread_cache_size设置的足够高，以便大多数新连接可以使用缓存线程。可以在服务器启动或运行时设置max_connections的值。

还应该监视缓存中的线程数（Threads_cached）以及创建了多少个线程，因为无法从缓存中获取线程（Threads_created）。关于后者，如果Threads_created继续以每分钟多于几个线程的增加，请考虑增加thread_cache_size的值。

使用MySQL show status命令显示MySQL的变量和状态信息。这里有几个例子：

Monyog线程缓存监测

Monyog提供了一个监控线程缓存的屏幕，名为“线程”。与MySQL线程相关的服务器变量映射到以下Monyog指标：

Monyog线程屏幕还包括“线程缓存命中率”指标。这是一个提示线程缓存命中率的指标。如果值较低，则应该考虑增加线程缓存。在状态栏以百分比形式显示该值；它的值越接近100％越好。

如果这些指标的值等于或超过指定值，则可以将每一个指标配置为发出警告和/或严重警报

---