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参数的值:
+----------------------------+-------+
| Variable_name
+----------------------------+-------+
| innodb_max_dirty_pages_pct | 90
+----------------------------+-------+
1 row in set (0.00 sec)
查看当前的检查点位置(对于如何获取此信息,花了比较多的时间,才找到此方法)
LOG
---
Log sequence number 16 881655880
Log flushed up to
Last checkpoint at
我们可以看到检查点与log sequence number,Log flushed up to都有相当大的差距。
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19338953832; in additional pool allocated 13600768
Buffer pool size
Free buffers
Database pages
Modified db pages
修改的页占到整个数据库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.
Query OK, 0 rows affected (0.00 sec)
---
LOG
---
Log sequence number 16 1160564756
Log flushed up to
Last checkpoint at
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 19338952464; in additional pool allocated 15022080
Buffer pool size
Free buffers
Database pages
Modified db pages
情况如自己预计的那样,脏页迅速减少,检查点追上来了。使用mysql的朋友,对于mysql服务器的交换,一直都采用直接关闭swap的做法。不知道使用此方法,即提高检查点发生的频率,减少脏页数量,能否解决我们常见的mysql交换问题呢?让我们试目以待吧。过一段时间,再把结果发上来。
--EOF--
这篇【 innodb_max_dirty_pages_pct与检查点的关系】来自 TaobaoDBA.com
Posted by 丹臣 2008-11-25 21:29 星期二 | 数据库
13 Responses to “innodb_max_dirty_pages_pct与检查点的关系”
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这个说法,我正要找这个东西。:)
2丁原
不同的环境有不同的设置。
如果innodb cache和log file都设的非常大,pct如果是默认的话,cache的脏数据会非常非常多,实际上我看过一些文档,差不多设置在15-20%左右。
说白了innodo的checkpoint机制也是oracle很老的check point实现机制,oracle已经改进了。3bartholo
频繁的checkpoint可以减少恢复的时间或者说数据的丢失,但是对I/O影响还是很大的,毕竟I/O的次数还是增多了。如果I/O跟不上,那对性能来说,没有好处吧
4丹臣
把此参数innodb_max_dirty_pages_pct调小并不是目的!至于能否解决交换,那才是目标。从这十几天的观察来看,当前系统表现良好。
5ivan
checkpoint当然是不可避免。
checkpoint 一直在进行,如果系统idle的时候 如果记得没错的话,默认是100 pages/s (没有打补丁的情况下)。还有就是iblogfile满了(不是真正意义的,因为是循环的),这时候需要强制做checkpoint,并且相应的变量会增加,你可以通过这个来评估。
通过调整innodb_max_dirty_pages_pct为较小值做法并不明智。
另外:swap跟innodb 的dirty page应该没关系吧?
6丹臣
你说的意思,当然明白!但这个你不觉得这个不是问题吗?增大checkpoint发生的频率,那也是在一恰当的范围内,至于多大合适,要根据具体的环境调整。其实无论innodb_buffer多大,你说的这种情况都会发生!
7ivan
不是那个意思,你可能还没理解我说的
比如某个page标志为脏数据页之后,如果这时候 innodb_max_dirty_pages_pct 值设置比较低,那么在一定程度之后这个页面的数据会更新到tablespace,并作checkpoint
但是有没有想过,一个page中并不一定只包含一条记录,也许有多条
而且还有个问题,update操作第二次或者第n次都可能更新这个page如果 innodb_max_dirty_pages_pct 设置比较大,将增大上面描述的这种情况,
希望你能再想想showsa########live.cn
8丹臣
回复ivan:
并不是说降低这个参数,就会降低数据库写入的性能,你也可以算一下,现在的脏页所占的空间有多大,这个空间作为写入cache,不够吗?如果你的innodb_buffer比较小,那这个参数肯定要设大一点的。9丹臣
回复ivan
还是将 iblogfile 的参数设置小点
因为它满了之后,mysqld会强制做checkpoint,比你调整innodb_max_dirty_pages_pct强制做checkpoint要“划算”这个要重启数据库,所以没做。
10ivan
呃… 写了这么多丢了? 汗…
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要“划算”12jedy
我不认为swap和buffer_pool中的脏页有什么关系
13yejr
降低 innodb_max_dirty_pages_pct 的值后,有没有发现iowait的值变大了呢?