14.通过Innodb存储引擎信息查看MySQL的线程

1.查看MySQL存储引擎的当前信息

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2023-02-08 10:58:22 0x7fe0f848f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 srv_active, 0 srv_shutdown, 410186 srv_idle
srv_master_thread log flush and writes: 410197
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16
OS WAIT ARRAY INFO: signal count 16
RW-shared spins 0, rounds 24, OS waits 11
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 24.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 15168
Purge done for trx's n:o < 15164 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422079494727504, 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
308 OS file reads, 366 OS file writes, 127 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 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2749188
Log flushed up to   2749188
Pages flushed up to 2749188
Last checkpoint at  2749179
0 pending log flushes, 0 pending chkp writes
70 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 209538
Buffer pool size   8192
Free buffers       7823
Database pages     369
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 271, created 98, written 189
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: 369, 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=2241, Main thread ID=140604143040256, state: sleeping
Number of rows inserted 172, updated 0, deleted 0, read 248
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.00 sec)

2.Innodb存储引擎的线程

Innodb存储引擎的线程主要分为四种：

1.Master Thread 

2.IO Thread（Write thread,Read thread,Insert Buffer thread,Log thread）

3.Purge thread 

4.Page Cleaner thread 

1.Master Thread 

是核心后台线程，主要负责异步刷新和数据一致性处理。

2.IO Thread

使用了异步IO模型，负责处理不同类型的IO请求回调。与之相关的参数是：

Innodb_read_io_threads,innodb_write_io_threads 对应innodb引擎输出信息如下：即默认有4个读线程和4个写线程。

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)

log_thread,insert buffer thread 各一个。

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)

3.Purge Thread 

事务提交后回收已经使用并分配的undo 页，线程数从1提高到4，加快标记为废弃undo页的回收速度。相关参数是：innodb_purge_threads。

4.Page Cleaner Thread

执行buffer pool里面脏页刷新操作，可以进行调整，默认为1，最大值为64。相关参数为：

innodb_page_cleaners

如果在MySQL的后台日志中看到：

【Note】InnoDB:page_cleaner:1000ms intended loop took 2512222ms.The setttings might not be optimal.(flushed=0 and evicted,during the time)

说明脏页太多，刷新速度慢，需要适当提高：innodb_page_cleaners的值。