innodb buffer pool 过小引起查询更新缓慢症状分析

By | 2014/05/14

场景:
业务(读写分离)的流量增大,使得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会缓解这个症状, 加多少视应用和主机环境而定;

Leave a Reply

Your email address will not be published. Required fields are marked *