Skip to main content

Innodb Redo Undo & Checkpoint

事务隔离级别

Innodb 提供了四种隔离级别:

Level Description
READ UNCOMMITTED 幻像读、不可重复读和脏读都允许。
READ COMMITTED 允许幻像读、不可重复读,但不允许脏读。一般使用的级别
REPEATABLE READ 允许幻像读,但不允许不可重复读和脏读。InnoDB默认级别
SERIALIZABLE 幻像读、不可重复读和脏读都不允许。

检查和设置当前隔离级别

检查

mysql> SELECT @@global.tx_isolation;  
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@session.tx_isolation;  
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)  

设置

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}  

修改全局事务级别为RC:

mysql> set global TRANSACTION ISOLATION LEVEL read committed;  
Query OK, 0 rows affected (0.00 sec)

此时,我们在另一个连中查询
mysql> select @@global.tx_isolation;  
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)  

READ UNCOMMITTED下的未提交读

以下实验按照执行顺序在2个终端中切换:
在两个终端中都切换事务到READ UNCOMMITTED:

set session TRANSACTION ISOLATION LEVEL read uncommitted;  

在A中begin一个事务,并查询:

mysql> begin;  
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;  
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)  

在B中,begin事务,插入数据:

mysql> begin;  
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1);  
Query OK, 1 row affected (0.00 sec)  

此时在A中能查到B中没提交的数据:

mysql> select * from test;  
+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql>  

READ COMMITTED下的提交读

以下实验按照执行顺序在2个终端中切换:
在两个终端中都切换事务到READ COMMITTED:

set session TRANSACTION ISOLATION LEVEL read committed;  

在A中begin一个事务,并查询:

mysql> begin;  
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;  
+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
|    1 |
+------+
4 rows in set (0.00 sec)  

此时在B插入数据,并提交:

mysql> begin;  
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(4);  
Query OK, 1 row affected (0.00 sec)

mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  

然后回到A再查询,此时会多出一条数据:

mysql> select * from test;  
+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
|    1 |
|    4 |
+------+

Redo Log & Undo Log

Undo

Undo日志是为了实现事务的原子性及实现了多版本并发控制MVCC(都成功或者都不成功)。
事务在修改数据之前先将前镜像存入到undo日志中以保证事务的原子性。

Undo Process

开始事务
Undo log记录操作前记录  
操作事务
Undo log记录操作后记录  
...
Undo log记录操作前记录  
操作事务
Undo log记录操作后记录  
写入Undo log
数据写入磁盘
提交事务


因为Undo log是在提交事务前进行的,所以可以通过Undo log来恢复事务。也提供了版本控制的可能性。
因为在每次提交之前都要将日志提交到磁盘,所以会导致Mysql性能低下。为了解决这个问题,所以就有了Redo Log。

Redo

Redo Log的作用是保证数据库的持久性,和进行数据恢复使用。

Redo Process

开始事务
Undo log记录操作前记录  
操作事务
Redo log记录操作后记录  
...
Undo log记录操作前记录  
操作事务
Redo log记录操作后记录  
写入Redo log
提交事务


通过使用redo log替换undo log,数据不需要每次都写入磁盘,提升了Mysql性能。同时因为Redo log是顺序写入,所以磁盘性能也会提升。

Mysql数据恢复

Mysql在宕机恢复重启的时候,只会恢复已经提交的事务。并且先恢复Redo Log再恢复Undo Log。
因为要先向前滚数据,然后回滚,才能保证数据的一致性。

Physical Log & Logical Log

这两种日志格式是Redo log的两种日志格式。

Physical Log

物理日志。物理日志是记录完整的Page和Page中被修改的内容。物理日志是幂等的,无论恢复多少次都是同一结果,但是备份尺寸会很大(一个Page 16k大小,物理日志相当于Page的克隆)。
物理日志可以直接通过覆盖日志到Page来恢复内容。

Logical Log

逻辑日志。逻辑日志日质量很小,但是会有一致性的问题。
当插入记录的时候,如果表中存在多个Index,向B-tree中插入记录。会出现前一个成功后面没有成功的问题。 在索引分裂的时候,可能也会出现A分裂为A和B,A写入成功,但是B不成功的问题。

Innodb的日志方式

Innodb为了解决问题,采用逻辑和物理结合的方式,在Page上是物理日志,在Page内部的操作是逻辑日志。
同时为了解决数据一致性问题,采用了Double Write的设计。

Double Write

在覆盖磁盘上的数据前,先将Page的内容写入到磁盘上的其他地方(InnoDB存储引擎中的doublewrite buffer,这里的buffer不是内存空间,是持久存储上的空间),然后再将Page的内容覆盖到磁盘上原来的数据。
double write buffer size: 2Mb
屏幕快照<em>2016-09-19</em>上午10.33.27
数据读取到内存——更新数据产生脏页——脏数据复制到内存中的double write buffer——写入共享表空间——刷入磁盘

Sharp checkpoint & Fuzzy checkpoint

假设在某个时间点,所有的脏页都被刷新到了磁盘上.这个时间点之前的所有Redo Log就不需要重做了。系统记录下这个时间点时redo log的结尾位置作为checkpoint. 在进行恢复时,从这个checkpoint的位置开始即可。Checkpoint点之前的日志也就不再需要了,可以被删除掉。为了更好的利用日志空间,InnoDB以环形缓存(circular buffer)的方式来使用日志空间。
为了加速Mysql Crash后的恢复速度,Mysql采用了检查点设计,同时用LSN标记来记录事务执行。

LSN
LSN是日志空间中每条日志的结束点,用字节偏移量来表示。在Checkpoint和恢复时使用.

Sharp checkpoint

Sharp checkpoint
Sharp checkpoint会在进行脏页刷新的时候,停止一切操作。这样子会导致执行效率非常低。一般只在进行关机或者重启的操作的时候才会使用。当Sharp checkpoint完成时,持久存储中存储的数据是某个确切时间点的内存数据的快照。

Fuzzy checkpoint

Fuzzy checkpoint
Fuzzy checkpoint的刷新方法,会在刷新脏页的过程中,依然允许更新操作。此时刷新的脏页中就会包含该刷新脏页过程中刷新的数据的信息。Fuzzy checkpoint完成时,持久存储中存储的数据不是某个确切时间点的内存数据的快照。这样子的话就不具备数据一致性了。

LSN

为了解决Fuzzy checkpoint数据不同步的问题,Innodb在Page的记录中的事务上都加上了LSN序号。每次进行数据更新的时候,就将Redo log中的LSN更新到Page的LSN上。如果在进行恢复的时候,Page的LSN的序号高于Redo log的LSN,就不跳过而不执行这个事务的恢复。以此来解决数据不一致的问题。

Innodb File Format

Antelope

Antelope是最原始Mysql数据存储格式。
这种格式对于大字段,会在当前Page中使用20byte的指针指向新的Page。

Barracuda

Barracuda是新的数据存储格式。在5.7中默认启用。
相比于Antelope,因为大字段直接存储在新的Page中,所以索引扫描效率变高,B+tree层级变低,性能提升。

涉及的Innodb参数

innodb_log_file_size

Redo Log大小

innodb_log_files_in_group

Redo Log个数

innodb_log_group_home_dir

Redo Log所在目录

innodb_autoinc_lock_mode

0 simple inserts
全部使用传统方式进行加锁,每次插入都会锁一次,自增+1
1(默认) bulk inserts bulk inserts
对于可以预测的插入,预先分配自增数的ID,就不用每次都获取锁然后自增+1
对于不确定的插入,依然使用传统模式
2 mixed-mode inserts 对于所有的插入,都不加锁。这种方式下,可能出现ID间隙。即索引ID不连贯。

innodb_io_capacity

控制每秒钟Innodb后台执行任务I/O上限
这个参数要根据磁盘的随机读写IO性能而定,但是最好不要占据全部的磁盘性能

innodb_buffer_pool_instances

将Innodb buffer pool拆分为多个区,减少锁的竞争。

innodb_lock_wait_timeout

锁超时等待时间

innodb_strict_mode

严格校验模式,建议保持开启

innodb_adaptive_hash_index

innodb根据访问频率自动创建hash索引

常用状态检查命令

以下结果结余mysql5.7,命令输出有省略,只留下最重要的内容

show engine innodb status\G

mysql> show engine innodb status\G  
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:  
=====================================
2016-09-20 23:45:25 0x700000d51000 INNODB MONITOR OUTPUT  
=====================================
省略……
TRANSACTIONS  
------------
Trx id counter 38403  
Purge done for trx's n:o < 37902 undo n:o < 0 state: running but idle  
History list length 1190  
LIST OF TRANSACTIONS FOR EACH SESSION:  
---TRANSACTION 281479459125040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)  
--------
省略……
LOG  
---
Log sequence number 25383617  
Log flushed up to   25383617  
Pages flushed up to 25383617  
Last checkpoint at  25383608  
0 pending log flushes, 0 pending chkp writes  
10 log i/o's done, 0.00 log i/o's/second  
----------------------
省略……
----------------------
BUFFER POOL AND MEMORY  
----------------------
Total large memory allocated 137428992  
Dictionary memory allocated 455138  
Buffer pool size   8192  
Free buffers       7704  
Database pages     484  
Old database pages 0  
Modified db pages  0  
Pending reads      0  
Pending writes: LRU 0, flush list 0, single page 0  
Pages made young 0, not young 0  
0.00 youngs/s, 0.00 non-youngs/s  
Pages read 450, created 34, written 36  
0.00 reads/s, 0.00 creates/s, 0.00 writes/s  
No buffer pool page gets since the last printout  
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  
LRU len: 484, unzip_LRU len: 0  
I/O sum[0]:cur[0], unzip sum[0]:cur[0]  
--------------
省略……
END OF INNODB MONITOR OUTPUT  
============================

1 row in set (0.03 sec)

mysqladmin -uroot -r -i 1 –pxxxx extended-status

输出结果实在过多,挑重点说:

QPS:  
Questions 132

TPS:  
Com_rollback + Com_commit 数据之和

Innodb CRUD的数量:  
Innodb_rows_deleted 0  
Innodb_rows_inserted 5  
Innodb_rows_read 39  
Innodb_rows_updated 0

Innodb每秒读写字节数:  
Innodb_data_read 7557632  
Innodb_data_written 2620928

Innodb每秒读写字次数:  
Innodb_data_reads 509  
Innodb_data_writes 208

Innodb每秒逻辑读数量:  
Innodb_buffer_pool_read_requests 14766

Innodb每秒从磁盘读取Page次数:  
Innodb_buffer_pool_reads 457

写入日志文件字节数:
innodb_os_log_written 50176

写入日志文件次数:
innodb_log_writes 51

每秒日志写入请求:
innodb_log_write_requests 20

每秒全表扫描排序数量:
Sort_scan 0

每秒索引范围查询扫描排序的数量:
Sort_range 27

全表扫描次数:
Select_scan 44

随机读buffer大小:
read_rnd_buffer_size 

看到这个数据比较大的时候,是因为分配的sort_buffer_size不足导致经常使用磁盘缓存排序,此时应增加sort_buffer_size:
Sort_merge_passes 0  

其他

mysql> show variables like '%buffer_size%';  
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608  |
| innodb_log_buffer_size  | 16777216 |
| innodb_sort_buffer_size | 1048576  |
| join_buffer_size        | 262144   |
MyIASM表缓冲池大小  
| key_buffer_size         | 8388608  |
| myisam_sort_buffer_size | 8388608  |
| preload_buffer_size     | 32768    |
| read_buffer_size        | 131072   |
随机读buffer
| read_rnd_buffer_size    | 262144   |
排序buffer
| sort_buffer_size        | 262144   |
+-------------------------+----------+
10 rows in set (0.00 sec)

mysql> show variables like '%sort%';  
+--------------------------------+---------------------+
| Variable_name                  | Value               |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | OFF                 |
| innodb_ft_sort_pll_degree      | 2                   |
| innodb_sort_buffer_size        | 1048576             |
| max_length_for_sort_data       | 1024                |
| max_sort_length                | 1024                |
| myisam_max_sort_file_size      | 9223372036853727232 |
| myisam_sort_buffer_size        | 8388608             |
| sort_buffer_size               | 262144              |
+--------------------------------+---------------------+
8 rows in set (0.01 sec)

mysql> show variables like '%table%';  
+----------------------------------------+----------+
| Variable_name                          | Value    |
+----------------------------------------+----------+
| big_tables                             | OFF      |
| innodb_file_per_table                  | ON       |
| innodb_ft_aux_table                    |          |
| innodb_ft_server_stopword_table        |          |
| innodb_ft_user_stopword_table          |          |
| innodb_table_locks                     | ON       |
| innodb_undo_tablespaces                | 0        |
| lower_case_table_names                 | 2        |
| max_heap_table_size                    | 16777216 |
| max_tmp_tables                         | 32       |
| old_alter_table                        | OFF      |
| performance_schema_max_table_handles   | -1       |
| performance_schema_max_table_instances | -1       |
| performance_schema_max_table_lock_stat | -1       |
| table_definition_cache                 | 1400     |
| table_open_cache                       | 2000     |
| table_open_cache_instances             | 16       |
临时表大小,如果超出设置大小,则在磁盘上生成临时表
| tmp_table_size                         | 16777216 |
| updatable_views_with_limit             | YES      |
+----------------------------------------+----------+
19 rows in set (0.00 sec)

mysql>