innodb_max_dirty_pages_pct与检查点的关系

 

http://blog.sina.com.cn/s/blog_4d8a2c970100f53u.html

数据库运行一段时间后，经常导致服务器大量的swap,我怀疑是innodb中的脏数据太多了，因为没有free space了，mysql通知OS，把一些脏页交换出去，以上只是猜测。有一个现象是每次关数据库时都要关很久,并且在关数据库时，发现有大量的swap in。如果是数据库进程异常关闭，打开数据库又会花很长的时间来作恢复。我想提高一下mysql检查点发生的频率。看了Adaptive checkpointing,发现mysql检查点事件受两个因素的制约：一个是amount,另外一个是age.amount主要由 innodb_max_dirty_pages_pct参数控制;至于age,主要是由日志文件大小有关。因为修改日志文件大小，要重启数据库，所以没有做这个尝试；于是尝试修改innodb_max_dirty_pages_pct参数。

查看当前innodb_max_dirty_pages_pct参数的值：

mysql> show variables like '%pct%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct | 90    |
+----------------------------+-------+
1 row in set (0.00 sec)

查看当前的检查点位置（对于如何获取此信息，花了比较多的时间,才找到此方法）

show innodb status\G;

LOG
---
Log sequence number 16 881655880
Log flushed up to    16 881649862
Last checkpoint at  16 546135914

我们可以看到检查点与log sequence number,Log flushed up to都有相当大的差距。

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19338953832; in additional pool allocated 13600768
Buffer pool size    1048576
Free buffers        17666
Database pages      1009478
Modified db pages  204553

修改的页占到整个数据库buffer pool页将近20%,大小为204553*16k/1024=3.196G,有这么多的脏数据没有写到数据文件。如果此时关闭数据库，必然要花很长的时间。如果数据库服务器因为掉电或者mysqld进程异常中断，那么打开，恢复的时间也会很长。

在咨询mysql界的朋友后，大家对innodb_max_dirty_pages_pct基本上也是采用默认值，不过，觉得这个方向是对的，就开始一步步调此参数。因为脏页占整个pool的20%,所以直接将此参数从90调到20.反复执行命令show innodb status\G;发现检查点仍然增长缓慢。过了一会儿，发现系统并无任何异常之处，继续调低此参数到15,此时间发现脏页Modified db pages减少下来，检查点增长稍微快一点。最终综合考虑缓存大小，把此参数设为5.

mysql> set global innodb_max_dirty_pages_pct=5;
Query OK, 0 rows affected (0.00 sec)

---
LOG
---
Log sequence number 16 1160564756
Log flushed up to    16 1160560077
Last checkpoint at  16 1037968260    --检查点追上来了

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19338952464; in additional pool allocated 15022080
Buffer pool size    1048576
Free buffers        5291
Database pages      1021765
Modified db pages  61626          --这个值从204553快速下降

情况如自己预计的那样，脏页迅速减少，检查点追上来了。使用mysql的朋友，对于mysql服务器的交换，一直都采用直接关闭swap的做法。不知道使用此方法，即提高检查点发生的频率，减少脏页数量，能否解决我们常见的mysql交换问题呢？让我们试目以待吧。过一段时间，再把结果发上来。

--EOF--

Trackback：http://rdc.taobao.com/blog/dba/html/221_innodb_max_dirty_pages_pct_checkpoint.html/trackback

这篇【 innodb_max_dirty_pages_pct与检查点的关系】来自 TaobaoDBA.com

如果你觉得这个Blog不错，欢迎 订阅

Posted by 丹臣 2008-11-25 21:29 星期二 | 数据库

本文Tags: mysql innodb_max_dirty_pages_pct checkpoint zhaolinjnu

13 Responses to “innodb_max_dirty_pages_pct与检查点的关系”

---

1. 1玉面飞龙

   关闭数据库之前，可以手工调整@@global.innodb_max_dirty_pages_pct为比较小的数值，然后等待dity pages变少，然后restart，可以减少启动要恢复的时间。

   对于很大的innodb buffer的，设置小点的innodb_max_dirty_pages_pct理论上是比较合理的。谁见过在oracle数据库中，v$bh.dirty占到90%的。反正dirty buffer writer是backgound async的，只要不要太lazy或者crazy就行。

   至于buffer cache hit的变化应该可以从Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests衡量。

   ivan 的100 pages/s这个说法，我正要找这个东西。:)

   Comment on Feb 17th, 2009 at 4:09 pm   
2. 2丁原

   不同的环境有不同的设置。
   如果innodb cache和log file都设的非常大，pct如果是默认的话，cache的脏数据会非常非常多，实际上我看过一些文档，差不多设置在15-20%左右。
   说白了innodo的checkpoint机制也是oracle很老的check point实现机制，oracle已经改进了。

   Comment on Dec 22nd, 2008 at 3:22 pm   
3. 3bartholo

   频繁的checkpoint可以减少恢复的时间或者说数据的丢失，但是对I/O影响还是很大的，毕竟I/O的次数还是增多了。如果I/O跟不上，那对性能来说，没有好处吧

   Comment on Dec 21st, 2008 at 12:58 am   
4. 4丹臣

   把此参数innodb_max_dirty_pages_pct调小并不是目的！至于能否解决交换，那才是目标。从这十几天的观察来看，当前系统表现良好。

   Comment on Dec 4th, 2008 at 10:02 pm   
5. 5ivan

   checkpoint当然是不可避免。

   checkpoint 一直在进行，如果系统idle的时候 如果记得没错的话，默认是100 pages/s （没有打补丁的情况下）。还有就是iblogfile满了（不是真正意义的，因为是循环的），这时候需要强制做checkpoint，并且相应的变量会增加，你可以通过这个来评估。

   通过调整innodb_max_dirty_pages_pct为较小值做法并不明智。

   另外：swap跟innodb 的dirty page应该没关系吧？

   Comment on Dec 4th, 2008 at 12:55 pm   
6. 6丹臣

   你说的意思，当然明白！但这个你不觉得这个不是问题吗？增大checkpoint发生的频率，那也是在一恰当的范围内，至于多大合适，要根据具体的环境调整。其实无论innodb_buffer多大，你说的这种情况都会发生!

   Comment on Dec 2nd, 2008 at 9:20 pm   
7. 7ivan

   不是那个意思，你可能还没理解我说的

   比如某个page标志为脏数据页之后，如果这时候 innodb_max_dirty_pages_pct 值设置比较低，那么在一定程度之后这个页面的数据会更新到tablespace，并作checkpoint

   但是有没有想过，一个page中并不一定只包含一条记录，也许有多条
   而且还有个问题，update操作第二次或者第n次都可能更新这个page

   如果 innodb_max_dirty_pages_pct 设置比较大，将增大上面描述的这种情况，
   希望你能再想想

   showsa########live.cn

   Comment on Dec 2nd, 2008 at 5:36 pm   
8. 8丹臣

   回复ivan:
   并不是说降低这个参数，就会降低数据库写入的性能，你也可以算一下，现在的脏页所占的空间有多大，这个空间作为写入cache,不够吗？如果你的innodb_buffer比较小，那这个参数肯定要设大一点的。

   Comment on Dec 2nd, 2008 at 2:52 pm   
9. 9丹臣

   回复ivan
   还是将 iblogfile 的参数设置小点
   因为它满了之后，mysqld会强制做checkpoint，比你调整innodb_max_dirty_pages_pct强制做checkpoint要“划算”

   这个要重启数据库，所以没做。

   Comment on Dec 2nd, 2008 at 2:47 pm   
10. 10ivan

    呃… 写了这么多丢了？ 汗…

    Comment on Nov 28th, 2008 at 12:56 am   
11. 11ivan

    innodb_max_dirty_pages_pct 在可接受的范围内应该尽可能大才好，这样可以提高性能
    为什么呢？
    因为在某个page呢有多条记录，如果有一条记录修改那么这个page就标志位dirty
    如果你 innodb_max_dirty_pages_pct 设置太低，那么整个buffer很容易达到这个比率
    这时候就要做checkpoint并写入到tablespace
    那为什么说innodb_max_dirty_pages_pct设置高点可以提高性能呢？
    因为高了之后，后续的update 更新的数据极大可能会出现这已有的标志为dirty的page内
    这就是为什么innodb_max_dirty_pages_pct设置高点可以提高整体性能的原因

    如果嫌关机时间太慢，可以把 fast shutdown参数设置为2，这时候关机之后开机和crash之后重启是一样的检查过程

    最后想说的是，如果是在忍受不了关闭服务等待的时间，还是将 iblogfile 的参数设置小点
    因为它满了之后，mysqld会强制做checkpoint，比你调整innodb_max_dirty_pages_pct强制做checkpoint要“划算”

    Comment on Nov 28th, 2008 at 12:54 am   
12. 12jedy

    我不认为swap和buffer_pool中的脏页有什么关系

    Comment on Nov 26th, 2008 at 11:22 am   
13. 13yejr

    降低 innodb_max_dirty_pages_pct 的值后，有没有发现iowait的值变大了呢？

(顺便把留言也转过来了：http://rdc.taobao.com/blog/dba/html/221_innodb_max_dirty_pages_pct_checkpoint.html)