InnoDB 引擎状态解析

1,477 阅读6分钟

[图片来自于网络 PostgreSQL] MySQL 开发文档

实验环境 5.7.20 MySQL Community Server (GPL)

套路: 一种类型的活, 如果一个人干得有点吃力了, 考虑多个人一起干

查看 innodb_read_io_threads 和 innodb_write_io_threads 信息

mysql> show VARIABLES LIKE 'innodb_%io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
        Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
        Value: 4
2 rows in set (0.01 sec)

ERROR:
No query specified
mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-06-11 09:56:35 0x7f4b6134c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5391 srv_active, 0 srv_shutdown, 14763749 srv_idle
srv_master_thread log flush and writes: 14769140
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3578
OS WAIT ARRAY INFO: signal count 2452
RW-shared spins 0, rounds 4241, OS waits 2129
RW-excl spins 0, rounds 10794, OS waits 362
RW-sx spins 45, rounds 1350, OS waits 20
Spin rounds per wait: 4241.00 RW-shared, 10794.00 RW-excl, 30.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
// 信息已经脱敏
------------
TRANSACTIONS
------------
Trx id counter 1093600
Purge done for trx's n:o < 1093596 undo n:o < 0 state: running but idle
History list length 108
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421437465770720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421437465769808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
10509 OS file reads, 765026 OS file writes, 746005 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 27, seg size 29, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2063945926
Log flushed up to   2063945926
Pages flushed up to 2063945926
Last checkpoint at  2063945917
0 pending log flushes, 0 pending chkp writes
735551 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 957523
Buffer pool size   8191
Free buffers       1024
Database pages     7163
Old database pages 2624
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 22829, not young 24340
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10375, created 9265, written 24250
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: 7163, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17038, Main thread ID=139962189133568, state: sleeping
Number of rows inserted 1102009, updated 10, deleted 6, read 4822480
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

ERROR:
No query specified

查看 innodb 引擎的状态:

  1. background thread 后台线程
  2. SEMAPHORES 信号量统计, 主要是和锁相关的, 比如读写共享锁, 读写排他锁, .... WL#6363: InnoDB: implement SX-lock for rw_lock
  3. LATEST FOREIGN KEY ERROR 最新的外键错误统计
  4. TRANSACTIONS 事务相关; Trx id 这个标志在可重复读隔离级别下, 当前事务可以看到的数据的版本有密切的关系.
  5. FILE I/O 文件相关的线程
  6. INSERT BUFFER AND ADAPTIVE HASH INDEX 插入缓存和自适应哈希
  7. LOG 日志相关
  8. BUFFER POOL AND MEMORY 缓存池和内存
  9. ROW OPERATIONS 行操作?

backgound thread

BACKGROUND THREAD
-----------------
srv_master_thread loops: 5391 srv_active, 0 srv_shutdown, 14763749 srv_idle // 主线程, 5391 活跃, 0 关闭, 14763749 空闲
srv_master_thread log flush and writes: 14769140 // 日志线程, 刷新和写数据

purge thread

事务提交之后, 需要删除不需要的回滚日志, purge thread 就是用来回收已经使用并分配的 undo 页 查看 purge thread 的信息

mysql> show variables like 'innodb_purge_threads';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6947
Current database: j_f

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
1 row in set (0.04 sec)

@谭老师, 该换服务器了, 最近老是自动断开链接 /(ㄒoㄒ)/~~

说到事务的隔离, 各种论坛肯定会丢出一大堆的关于事务的描述以及事务相关的(ACID), 和事务隔离级别相关引起的 脏读, 不可重复读和幻读的问题。

事务的隔离级别: 读未提交, 读提交, 可重复读和串行化。

  • 读未提交: 一个事务读取到另外一个事务未提交的更新
  • 读提交(Read Committed[RC]): 一个事务读取到另外一个事务已经提交的数据。
  • 可重复读(Repeatable Read[RR]): 事务期间读取到的数据一致, 对于更新来说, 会有一个当前读(但是其中的更新语句, 不管当前行的数据处于哪个版本, 更新语句都会拿到当前最新版本的数据再在当前最新版本的数据上做更新)的过程。
  • 串行化: 对于每一行的数据, 读加读锁, 写加写锁, 读写锁互斥, 写锁之间互斥。

以下所有的关于事务都是在 InnoDB 引擎下为标准 查看Innodb 的隔离级别:

mysql> show variables LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ | //RR
+-----------------------+-----------------+
1 row in set (0.01 sec)

在 Innodb 中, 对于(RC 和 RR 来说)事务的隔离是通过 mvcc(多版本并发控制[对于同一行的数据, 可能会有个多个版本, 不同版本的数据, 使用 row trx_id(row trx_id 是提交更新行数据的事务 id 的值) 来标识当前数据的版本, 不同事务通过 自己的事务 id和 row row trx_id 来区别某行数据的当前版本对于当前的事务是否可见]) 实现的; 对于串行化来说读加读锁, 写加写锁, 读写锁互斥(性能损耗最大)。

事务 id 的分配规则

transaction id 分配规则是: 按顺序递增

可重复读 (RR)

  1. 读规则: 只认当前事务在创建一致性视图时就已经提交的数据版本和当row trx_id 的值等于当前事务 id 值得数据版本, 其他的一概不认.

  2. 更新规则: 先读取数据的最新版本, 然后再在最新版本的记录的基础上更新.

undo , redo 怎么配合来实现数据的多版本的?

还没有写....