Mysql InnoDB引擎生产环境配置

一. 前言

在生产环境中mysql如何配置, 有什么约定项…

数据库在生产环境运行的时候，你必须根据机器的内存设置合理的buffer pool的大小，然后设置buffer pool的数量，这样的话，可以尽可能的保证你的数据库的高性能和高并发能力。
在线上运行的时候，buffer pool是有多个的，每个buffer pool里多个chunk但是共用一套链表数据结构，然后执行crud的时候，就会不停的加载磁盘上的数据页到缓存页里来，然后会查询和更新缓存页里的数据，同时维护一系列的链表结构。

二. 查看配置 SHOW ENGINE INNODB STATUS

mysql> SHOW ENGINE INNODB STATUS \G;
*************************** 1. row ***************************Type: InnoDBName:
Status:
----------------------
BUFFER POOL AND MEMORY
----------------------
## buffer pool最终的总大小是多少
Total large memory allocated 137428992
Dictionary memory allocated 100382
## 这就是说buffer pool一共能容纳多少个缓存页
Buffer pool size   8191
## 这就是说free链表中一共有多少个空闲的缓存页是可用的
Free buffers       7703
## Database pages和Old database pages，就是说lru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量
Database pages     484
Old database pages 0
## 这就是flush链表中的缓存页数量
Modified db pages  0## Pending reads和Pending writes，等待从磁盘上加载进缓存页的数量，
## 还有就是即将从lru链表中刷入磁盘的数量、即将从flush链表中刷入磁盘的数量
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0## 这就是说已经lru冷数据区域里访问之后转移到热数据区域的缓存页的数量，
## 以及在lru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
Pages made young 0, not young 0## youngs/s和not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，
## 以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
0.00 youngs/s, 0.00 non-youngs/s## Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，
## 这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
Pages read 450, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s## Buffer pool hit rate xxx / 1000，这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
## young-making rate xxx / 1000 not xx / 1000，每1000次访问，有多少次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s## LRU len：这就是lru链表里的缓存页的数量
LRU len: 484, unzip_LRU len: 0## I/O sum：最近50s读取磁盘页的总数
## I/O cur：现在正在读取磁盘页的数量
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

三. 配置

buffer pool 大小.

约定: 要根据可用内存来评估. 比如 buffer pool设置你的机器内存的50%~60%左右,

buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数

确定了buffer pool的总大小之后，就得考虑一下设置多少个buffer pool，以及chunk的大小了

关键公式 : buffer pool总大小=(chunk大小 * buffer pool数量)的倍数

示例:
默认的chunk大小是128MB, 机器的内存是32GB，打算给buffer pool总大小分配20GB内存.

答案: buffer pool的数量是16个，
此时chunk大小 * buffer pool的数量 = 16 * 128MB = 2048MB，
然后buffer pool总大小如果是20GB，此时buffer pool总大小就是2048MB的10倍
当然，此时你可以设置多一些buffer pool数量，比如设置32个buffer pool，那么此时buffer pool总大小（20GB）就是（chunk大小128MB * 32个buffer pool）的5倍，也是可以的。
那么此时你的buffer pool大小就是20GB，然后buffer pool数量是32个，每个buffer pool的大小是640MB，然后每个buffer pool包含5个128MB的chunk，算下来就是这么一个结果了。