innodb buffer pool 过小引起查询更新缓慢症状分析
场景: 业务(读写分离)的流量增大，使得MySQL Server压力增大很多， 表现为iostat显示频繁的写操作, master(只写)中show processlist出现大量的update线程, slave出现大量的select 线程； 表设计和索引相对正常(尽管整体的设计显得不合理，不过对于sql语句及表而言,尽管存在冗余的索引等信息，不过对于业务而言显得可以胜任);
分析: 1. master(只写说明): master说明:
merged operations:
insert 5417064, delete mark 9998418, delete 3935427
Total memory allocated 5494538240; in additional pool allocated 0
Dictionary memory allocated 710216
Buffer pool size 327680
Free buffers 88
Database pages 324937
Old database pages 119928
Modified db pages 182550
LRU len: 324937, unzip_LRU len: 0
可以看到 Old database pages + Modified db pages =~ Database pages; 更新频繁会引起LRU链表的频繁更新，再加上buffer pool过小，尽管有insert buffer的缓冲，频繁的更新还是会引起频繁的flush disk操作, insert buffer 溢出一次，就会刷新一次，引起磁盘io增长; 同样的，buffer pool不够用 update操作会花较长时间处于updating状态（搜索匹配的记录），匹配后再转为updated状态; 这种情况引起出现大量的更新语句:
UPDATE `search` SET `record`='xxxxxxxxxx' WHERE n='nnnn' AND s='sssss';
profiling分析,时间集中在updating状态:
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000776 |
| checking permissions | 0.000424 |
| Opening tables | 0.000523 |
| System lock | 0.000388 |
| init | 0.000723 |
| Updating | 4.498569 |
| end | 0.000903 |
| query end | 0.000797 |
| closing tables | 0.000672 |
| freeing items | 0.000690 |
| logging slow query | 0.000720 |
| logging slow query | 0.000767 |
| cleaning up | 0.000662 |
+----------------------+----------+
加大buffer size 后, 存在更多的free buffer页, update操作更新变快(show processlist 显示很少的update线程(time时间大于1的)), io使用降低，主机负载降低;
2.slave说明: show processlist中出现大量的select语句:
SELECT record,n,s FROM `search` WHERE n = 'nnnnnnn' AND s = 'sssss';
大量的这种语句，profiling中的Sending data占用较多时间, 在buffer pool不够的时候，尽管记录很小，如果没有cache到内存里，还是会走磁盘扫描(也是手工操作会出现一条特别快，一条特别慢的状况的原因)解决同上加大buffer pool size;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000245 |
| Waiting for query cache lock | 0.000048 |
| checking query cache for query | 0.000069 |
| checking permissions | 0.000045 |
| Opening tables | 0.000105 |
| System lock | 0.000047 |
| Waiting for query cache lock | 0.000062 |
| init | 0.000052 |
| optimizing | 0.000048 |
| statistics | 0.000192 |
| preparing | 0.000051 |
| executing | 0.000042 |
| Sending data | 1.988487 |
| end | 0.000438 |
| query end | 0.000341 |
| closing tables | 0.000967 |
| freeing items | 0.000313 |
| logging slow query | 0.000082 |
| logging slow query | 0.000129 |
| cleaning up | 0.000091 |
+--------------------------------+----------+
从engine innodb status来看:
Pages made young 56281937, not young 0
764.74 youngs/s, 0.00 non-youngs/s
Pages read 47532991, created 94762, written 20074767
这种情况下LRU链表不停更新(made young)，buffer缓存在这种情况下没多少意义，没有起到缓存的作用;可通过加大buffer pool size大小来缓解;
结论: 描述相对粗糙,没有列出详细的症状和出现问题搜集的信息,这里只是表明buffer pool size过小会引起查询和更新缓慢,加大buffer pool size会缓解这个症状, 加多少视应用和主机环境而定;