|
1、问题描述 但是还存在一个疑点，那就是该定时任务每天会执行三次，零点，12点和17点，数据的更新量几乎一致，其中12点和17点均是业务的高峰期，为何只有下午17点会出现大量慢查询，而上午12点数据库受到的影响相对较小，没有出现慢查询呢？ 2、监控数据分析 DBA对当时数据库系统的状态进行了分析，12点与17点都存在一个突发的大量数据写入，对比两个时间段的innodb性能参数，可以发现：
(1) 两个时间段Innodb log buffer均有一个突发的写入
(2) 两个时间段innodb_log file均有一个突发的写入
注：对int32高于2^31的数据都显示为负数，13:30左右的陡降为监控系统的bug，请忽略。
分析 (1),(2) 说明该时间点有一个影响行数较高的突发的写入操作。
(3) 12点时innodb buffer dirty page有一个突增，但17点左右innodb buffer dirty page 有一个突降
(4) 12点时innodb的数据文件写入速度维持在一个相对平缓的水平，而17点左右innodb的数据文件写入速度有一个突增。 注：对int32高于2^31的数据都显示为负数，13:30左右的陡降为bug，请忽略。
(5) 12点时系统IO的BI略有增长，但是不大，而17点时BI有非常大的增长，对系统IO性能有很大的影响。
分析 (3),(4)说明该时间点确实有大量数据写入，但是12点左右的时候数据以脏页的形式保存在buffer pool中，而17点的时候脏页被大量写入到磁盘中。通过(5)可以看出12点的时候脏页是在update操作结束后缓缓刷入磁盘，而17点的时候脏页被直接刷入磁盘。可见导致数据库性能陡降的原因就是脏页刷盘，12点的时候性能没有明显相加的原因就是没有突发的脏页刷盘。
3、InnoDB数据存储 InnoDB数据存储主要分为数据部分和日志部分。 数据部分称为tablespace，记录数据和索引数据都保存在tablespace中，最小物理存储单位是page。InnoDB中每一个page的默认大小为16K。tablespace一般使用文件来保存，5.1版本中可以将tablespace存放在磁盘裸分区中。总之innoDB数据存储的物理结构是一个以page为最小单位的数据空间。 相对于tablespace数据空间，innoDB通过一个log group作为日志空间。log group由至少两个日志文件组成，日志空间每一条记录都保存一个tablespace中数据的变化，称为为mini transaction。日志空间是一个环状的结构，写满后会从头部开始写入。每一条记录都会按写入日志的累积长度（单位为字节）分配一个记录号，称为Log Sequence Number (LSN)。与日志对应，数据空间中每一个page的头结构中也记录着该page对应的LSN，即表明了该数据的新旧程度。 InnoDB数据库的数据空间和日志空间都有两种存在形式，持久化在磁盘上的磁盘数据和内存中的buffer数据。 数据文件的buffer是由Innodb_buffer_pool_size定义的，缓存的是运行时正在被使用的数据空间page,其中被写请求更新，但没有同步到磁盘上page，称为dirty page； 日志文件的buffer是由Innodb_log_buffer_size定义的，缓存的是运行时正在被使用的日志空间page。日志buffer刷盘的策略由innodb_flush_log_at_trx_commit确定。 数据buffer和日志buffer在一些时机会同步到磁盘的对应空间中去，即flush。进行flush的时机包括三种：overflow，checkpoint和commit。 InnoDB认为数据是日志的一种冗余，所以日志写盘必须早于对应的数据写盘，这种思想在这三个时机都有体现。 overflow：log buffer是一个环状结构，最后一个记录写满后会从第一个位置开始写。日志记录被覆盖之前会被写入到日志文件中去。日志空间在磁盘上写入的方式也是环形队列方式，log group被写满后也会覆盖最早的日志记录。InnoDB的checkpoint机制能保证数据必定会在日志记录被覆盖之前写入到磁盘中去。 当data buffer写满后，InnoDB会使用LRU策略清除部分缓存。在清除每个buffer page之前都会检查该page的LSN是否比磁盘上最大的LSN大，即data buffer写盘速度是否写的比log buffer快。InnoDB不允许data buffer 比log buffer 写盘快，当这种情况发生时，InnoDB会触发log buffer的写盘，直到log buffer写盘进度比data buffer快才会进行data buffer page的写盘。 checkpoint：InnoDB有独立线程负责定期进行checkpoint。InnoDB使用一种叫做Fuzzy Checkpoint的策略，它并不保证所有数据都被完全同步到磁盘上，而是只能保证： (a) log buffer和data buffer不会超过buffer总量的一定阈值，这保证了buffer可用空间 (b) log buffer写盘进度比data 写盘快，InnoDB写盘基本原则 (c)确保日志文件中日志记录被覆盖前，日志记录对应的数据page一写先被写盘，确保保证log overflow时不存在没有同步到磁盘上的数据。 commit：commit的时候InnoDB只会对log buffer进行写盘，而不会写入对应的data buffer，这一过程交由overflow触发。
由于innodb对log的空间有预留策略，部分page使用还有其他的用途，实际可用的空间只有80%左右。 4、分析checkpoint增长情况 回到我们出现性能问题的数据库上来，该数据库InnoDB log大小为100M*2=200M，实际可用与保存日志空间约为160M.
分析innodb log的增长情况。采集了4月20日12点和4月21日17点的innodb状态数据。此数据通过show engine innodb status\G获得。 4月20日12点innodb log的增长情况： Update前：10-04-20 11:55:57 Log sequence number 1016 342557090 Log flushed up to 1016 342557090 Last checkpoint at 1016 302781061
Update后：10-04-20 11:56:12 Log sequence number 1016 427706865 Log flushed up to 1016 427706865 Last checkpoint at 1016 302792380
LSN增长了80M左右，checkpoint与最新LSN差距约120M，没有达到160M，所以checkpoint没有较大变化。
4月21日17点innodb log的增长情况： Update前：10-04-21 17:04:58 Log sequence number 1018 345302625 Log flushed up to 1018 345301911 Last checkpoint at 1018 243759516
Update后：10-04-21 17:05:14 Log sequence number 1018 426144696 Log flushed up to 1018 426144696 Last checkpoint at 1018 278852487
LSN增长约55M，但checkpoint与最新的LSN差距180M超过160M, log被写满，多出的20M log由上述checkpoint规则(c)触发了这20M日志对应的dirty pages强制写盘（监控表明约12000个page被刷盘）。 处于刷盘量峰值的17:05:14采集的innodb status数据显示存在大量锁等待的query，而前后10秒的innodb status中几乎没有等待的query，即刷盘操作对请求处理有较大影响。 所以在这个数据库中，增长innodb log file size，则可以保存更多的log，容忍更多的dirty page在buffer pool中，能减少由于log写满触发的脏页刷盘情况。 由此可见，innodb buffer pool中的脏页不仅仅只是由innodb_max_dirty_pages_pct来控制脏页对于整个buffer pool的最大比例， innodb_log_file_size*innodb_log_files_in_group得到的日志空间大小也能控制dirty page的总量。当然日志大小和数据大小没有一个绝对的比例，这个会由日志记录的更新内容的不同而不同。 mysql手册上说明了innodb的日志空间（innodb_log_file_size*innodb_log_files_in_group）可以设置为1M至整个innodb buffer pool的大小。 日志空间大的优点是，checkpoint触发数据flush的几率小，能容忍更多的脏页，有更多的机会进行脏页的merge，最终达到平滑IO,节省IO的目的。 日志空间大的缺点是：意味着innodb关闭或在crash后的恢复时间会非常慢。 出现异常的数据库拥有4G innodb buffer pool，但只设置了200M的日志空间，该值偏小是导致17点出现innodb大量物理写盘的一个主要原因。 5、总结 导致性能下降的根本原因是数据库的innodb_log_file_size 设置过小导致。
[该问题的解决方案] 推动业务修改定时任务脚本，通过分解大操作量的语句或其他方式，平滑数据库写入速度，减小数据库突发写入量。 调整数据库innodb_log_file_size，设置为一个更大的值，保证不要出现由于日志空间的overflow导致的脏页刷盘。
完成以上调整后，该数据库集群的性能陡降问题完全消失。
|
