Checkpoint以及LOG中四行的解释

Checkpoint以及LOG中四行的解释

mysql> show engine innodb status \G;

 

将上图中虚线的部分拿出来放在数轴上就可以做简单的运算

LOG

---

Log sequence number 4318672452  //字节，日志生成的最新位置，最 新位置出现在log buffer，A

Log flushed up to   4318672452//日志已经写入到log file的位置A-B， 如果A-B=log buffer 说明log buffer满了

这是十分危险的

并且A-B的日志丢失了是没有关系的，因 为是log buffer中的数据肯定没有提交。

Pages flushed up to 4318672452  //B-C，innodb_buffer_pool中脏页的 数量，数据库崩溃的话，这些日志是需要 redo的

Last checkpoint at  4318672443  //共享表空间上的日志记录点，C-D 就是崩溃恢复多跑的日志，B-D是实 际上数据库崩溃之后重新启动时需要 重新redo的脏页的数量，可以看到比 B-C多了一点点，checkpoint意味着脏 页写入到磁盘

为什么会多一点点？Checkpoint作用解释？

数据库崩溃的时候需要找到磁盘上的要恢复的点，这个点就是checkponit（D点），但是这个点是跟随flush list刷新而变化的，但是flush list是不断刷新的，假设每刷新一次都要更新check ponit就浪费了ibdata的写能力，于是就有一种机制慢一点刷新checkponit，并不是紧跟flush list刷新，于是就是D点而不是C点。这样的话，B-D就是要恢复的脏页；多了有坏处吗？没有，在恢复的时候，多了的这些日志的时间点是比已经写入磁盘的数据旧的，因此在恢复的时候并不会多恢复这些数据，而是空跑(数据页上有时间点，日志里也有时间点，如果日志的时间点比数据页上的时间点旧，就空跑)。当到达C点的时候，数据才会真正的开始被恢复为dirtypage；当然C点以下的日志对应的数据早就写入了磁盘。

0 pending log flushes, 0 pending chkp writes

10 log i/o's done, 0.00 log i/o's/second

 

Check point分类

Fuzzy checkponint

fuzzy checkpoint部分页写入磁盘，数据库正常运行期间脏页写入磁盘的几种情况

读取lru，找到脏页，写入磁盘

mysql> show variables like '%depth%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| innodb_lru_scan_depth  | 1024

从lru冷端开始找，扫描1024个冷端，找到脏页，写入磁盘

读取flush list，找到脏页，写入磁盘

mysql> show variables like '%io_capa%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| innodb_io_capacity     | 200   |

| innodb_io_capacity_max | 2000  |

+------------------------+-------+

2 rows in set (0.01 sec)

 

log file快用满了，会批量的触发数据页回写

这个事件触发的时候又分为同步和异步

75%异步，此时触发数据页回写，但是是异步的，不影响数据库使用

mysql> show variables like '%dirty%';

+--------------------------------+-----------+

| Variable_name                  | Value     |

+--------------------------------+-----------+

| innodb_max_dirty_pages_pct     | 75.000000

90%同步，此时也触发数据页回写，但是是同步的，数据库表现为夯住

以上两个数值如果任何一个触发的时候，在errlog里面会有记录，这就表明log file太小了、组数少，一定要改变下面这两个参数

 

脏页太多了

mysql> show variables like '%dirty%';

+--------------------------------+-----------+

| Variable_name                  | Value     |

+--------------------------------+-----------+

| innodb_max_dirty_pages_pct     | 75.000000

默认当脏页百分比占系统的75%的时候触发。

将 innodb_max_dirty_pages_pct 调的小一些，但是调小了会增加系统的写压力，一般的PCIE闪存卡完全胜任系统的写压力，所以这个值可以调的很小

sharp checkpoint完全检查点发生在数据库正常关闭的时候

分析MySQL的写压力以及参数调整提升tps

1、redolog的写压力

2、脏页的写压力

事务的提交、redolog大量生成、脏页大量生成、IO带宽考虑

每秒写入多少页数、errlog里面是否出现有关写的错误

系统写压力的监控参数加入到zabbix

确认系统的写压力

操作系统层面

 

关注wrqm/s这个是合并写；

w_await是写等待；

w/s每秒写

数据库层面:

mysql>show status like '%pend%';

 

Innodb_data_pending_writes这个数值大于零表示系统写有问题并且Innodb_os_log_pending_writes日志写也是与系统写有关的。

大于0表示有被挂起的。

日志写入速度

mysql> show status like '%log%written%';

 

脏页写入速度

mysql> show status like '%written%';

 

Double write比值是否接近128

mysql> show status like '%dbl%';

 

脏页的数量

mysql> show status like '%dirty%';

 

总共能容纳多少脏页

mysql> show status like '%total%';

 

 

 

8191*16k=innodb_buffer_pool_size

#cat /dev/urandom|md5sum 模拟操作系统压力

最重要指标

mysql> show status like '%wait%';

 

Innodb_buffer_pool_wait_free大说明了脏页应该快点写而不会出现wait的情况；Innodb_log_waits大说明了redolog应该快点写

事务提交数

mysql> show status like  '%commit%';

 

如上Handler_commit被称为隐式提交，与参数autocommit有关

 

显式提交

 

开启一个事务，然后提交，可以发现Com_commit数值有所变化

 

事务回滚量

mysql> show status like '%back%';

 

Com_rollback显式回滚

Handler_rollback隐式回滚

异常DML SQL

 

 

read代表select

调整相关参数

1、mysql> show variables like '%depth%';

 

将这个数值调小降低写压力，写得慢

2、mysql> show variables like '%capa%';

 

这个数值在系统IO允许的情况下调大可以增大写压力，写得快

3、mysql> show variables like '%innodb_log%';

 

innodb_log_buffer_size增加，优化写

innodb_log_file_size每组文件大小增加以下，优化

innodb_log_files_in_group增加组数为5组，优化

innodb_log_group_home_dir找一个I/O能力强的位置

就是这些文件的位置，log file文件的位置

mysql> show variables like '%dirty%';

 

如果短时间内写压力很大，可以将innodb_max_dirty_pages_pct调整的大一些，大是不要超过90%；如果写性能很强，可以将innodb_max_dirty_pages_pct调整得小一些25%，这样写的勤快，脏页到了innodb25%就会异步写。

innodb_max_dirty_pages_pct_lwm代表的是水位线的意思。Low water mark

mysql> show variables like '%flush%commit';

是一个非常重要的参数

 

0:日志先写在logbuffer中，直接就告诉用户提交成功；实际上并没有写到磁盘；日志缓冲每秒一次地将logbuffer中的数据写到磁盘上日志文件，数据库崩溃丢失数据，丢一秒钟的事务；

1:数据先写到文件系统的高速缓冲buffer中，然后写到磁盘文件中，不丢失事务；

2:数据先写到logbuffer中，再写到操作系统中的高速缓存，这个时候告诉用户提交成功；实际上并没有写到磁盘，最后写道磁盘上的日志文件中；数据库崩溃不丢失事务，OS崩溃丢失事务

三种参数根据行情来定:如果这个数据库存储的是某个任务的坐标信息，那么完全可以将参数设置为0,但是要求将logbuffer调大，因为这个时候数据都在logbuffer中；非常重要的信息例如装备信息，要设置这个参数为1；2的话就是中规中矩。这个参数更改之后数据库写性能会提升地非常明显。

mysql> show variables like '%double%';