MySQL 线程 Linux运维

mysql> select * from performance_schema.threads where threads.PROCESSLIST_ID=9\G;
*************************** 1. row ***************************
THREAD_ID: 48 #通过这个THREAD_ID 号去查哪条语句慢
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 9 #可以通过这个PROCESSLIST_ID进程查到前台进程，9号
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: information_schema
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: executing
PROCESSLIST_INFO: select * from performance_schema.threads where threads.PROCESSLIST_ID=9
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 8586 #OS 层面的进程号
RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

top - 22:03:46 up 7:38, 2 users, load average: 0.23, 0.13, 0.08
Tasks: 105 total, 3 running, 102 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.3 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 995896 total, 203868 free, 494160 used, 297868 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 329464 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8533 mysql 20 0 1297652 402488 17260 S 0.3 40.4 0:17.79 mysqld
8683 root 20 0 0 0 0 S 0.3 0.0 0:02.26 kworker/0:0
8710 root 20 0 161980 2204 1556 R 0.3 0.2 0:00.05 top
1 root 20 0 125728 4180 2572 S 0.0 0.4 0:03.25 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 0:01.01 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:01.05 kworker/u256:0
7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root 20 0 0 0 0 R 0.0 0.0 0:01.39 rcu_sched
10 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 lru-add-drain
11 root rt 0 0 0 0 S 0.0 0.0 0:00.39 watchdog/0
13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kdevtmpfs

锁定哪个PID占用高

top - 22:05:41 up 7:40, 2 users, load average: 0.03, 0.09, 0.07
Threads: 38 total, 0 running, 38 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.2 us, 0.0 sy, 0.0 ni, 93.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 995896 total, 203992 free, 494036 used, 297868 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 329588 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8533 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.83 mysqld
8536 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.22 mysqld
8537 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.25 mysqld
8539 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.19 mysqld
8541 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8542 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8543 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.25 mysqld
8544 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8545 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.31 mysqld
8546 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.32 mysqld
8547 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.30 mysqld
8548 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.45 mysqld
8549 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.70 mysqld
8550 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.93 mysqld
8551 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.94 mysqld
8552 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.95 mysqld
8553 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:02.01 mysqld
8554 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.40 mysqld

通过PID号查到

mysql> select * from performance_schema.threads where threads.thread_os_id=8533\G;
*************************** 1. row ***************************
THREAD_ID: 1 #
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: mysql
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 6457
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 8533 #OS层进程号
RESOURCE_GROUP: SYS_default
1 row in set (0.00 sec)

#再通过 THREAD_ID: 1 查到线程

select * from performance_schema.events_statements_history where events_statements_history.THREAD_ID='1'\G;

mysql> select * from performance_schema.events_statements_history where events_statements_history.THREAD_ID=48\G;
*************************** 1. row ***************************
THREAD_ID: 48
EVENT_ID: 144
END_EVENT_ID: 144
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6950724493749000
TIMER_END: 6950724890011000
TIMER_WAIT: 396262000
LOCK_TIME: 163000000
SQL_TEXT: select * from performance_schema.events_statements_history where THREAD_ID=48
DIGEST: ab6f69218695e267b8f701180da603950ed43ba9bc61078687a72522066428a0
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 10
ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 142
*************************** 2. row ***************************
THREAD_ID: 48
EVENT_ID: 135
END_EVENT_ID: 135
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 5642022055908000
TIMER_END: 5642022904246000
TIMER_WAIT: 848338000
LOCK_TIME: 516000000
SQL_TEXT: select * from performance_schema.threads where threads.PROCESSLIST_ID='root'
DIGEST: 072ea9f170c8cce2d38daaa2c013371448317be5e3d2e360df64a5ee29bf571f
DIGEST_TEXT: SELECT * FROM `performance_schema` . `threads` WHERE `threads` . `PROCESSLIST_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 1
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 133
*************************** 3. row ***************************
THREAD_ID: 48
EVENT_ID: 136
END_EVENT_ID: 136
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 5679849527449000
TIMER_END: 5679849951467000
TIMER_WAIT: 424018000
LOCK_TIME: 201000000
SQL_TEXT: select * from performance_schema.threads where threads.PROCESSLIST_ID=9
DIGEST: 072ea9f170c8cce2d38daaa2c013371448317be5e3d2e360df64a5ee29bf571f
DIGEST_TEXT: SELECT * FROM `performance_schema` . `threads` WHERE `threads` . `PROCESSLIST_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 134
*************************** 4. row ***************************
THREAD_ID: 48
EVENT_ID: 137
END_EVENT_ID: 137
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 5731416351035000
TIMER_END: 5731416812132000
TIMER_WAIT: 461097000
LOCK_TIME: 201000000
SQL_TEXT: select * from performance_schema.threads where threads.PROCESSLIST_ID=9
DIGEST: 072ea9f170c8cce2d38daaa2c013371448317be5e3d2e360df64a5ee29bf571f
DIGEST_TEXT: SELECT * FROM `performance_schema` . `threads` WHERE `threads` . `PROCESSLIST_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 135
*************************** 5. row ***************************
THREAD_ID: 48
EVENT_ID: 138
END_EVENT_ID: 138
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6459786833890000
TIMER_END: 6459787241393000
TIMER_WAIT: 407503000
LOCK_TIME: 205000000
SQL_TEXT: select * from performance_schema.threads where threads.thread_os_id=8533
DIGEST: aff6eb4df00ae29e555842b1b1de059e20954ed8a01ed143b29bc0223e596378
DIGEST_TEXT: SELECT * FROM `performance_schema` . `threads` WHERE `threads` . `thread_os_id` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 136
*************************** 6. row ***************************
THREAD_ID: 48
EVENT_ID: 139
END_EVENT_ID: 139
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6578142606903000
TIMER_END: 6578143272864000
TIMER_WAIT: 665961000
LOCK_TIME: 224000000
SQL_TEXT: select * from performance_schema.events_statements_history where THREAD_ID='1'
DIGEST: ab6f69218695e267b8f701180da603950ed43ba9bc61078687a72522066428a0
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 137
*************************** 7. row ***************************
THREAD_ID: 48
EVENT_ID: 140
END_EVENT_ID: 140
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6615624970700000
TIMER_END: 6615625203622000
TIMER_WAIT: 232922000
LOCK_TIME: 0
SQL_TEXT: select * from performance_schema.events_statements_history where events_statments_history.THREAD_ID='1'
DIGEST: 77a894dd8968b08409f28713d32be8011045cde95759096e8b4b996c50a3f9c1
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `events_statments_history` . `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 1054
RETURNED_SQLSTATE: 42S22
MESSAGE_TEXT: Unknown column 'events_statments_history.THREAD_ID' in 'where clause'
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 138
*************************** 8. row ***************************
THREAD_ID: 48
EVENT_ID: 141
END_EVENT_ID: 141
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6679733004138000
TIMER_END: 6679734189552000
TIMER_WAIT: 1185414000
LOCK_TIME: 417000000
SQL_TEXT: select * from performance_schema.events_statements_history
DIGEST: 6fcefbe86c9670c843bbf53da1ab47aeec8f1c38602f78919f91b0a71654b8ef
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history`
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 10
ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 139
*************************** 9. row ***************************
THREAD_ID: 48
EVENT_ID: 142
END_EVENT_ID: 142
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6755002102763000
TIMER_END: 6755002436304000
TIMER_WAIT: 333541000
LOCK_TIME: 189000000
SQL_TEXT: select * from performance_schema.events_statements_history where events_statements_history.THREAD_ID=1
DIGEST: 862ed7fcac35c045ab4b51f2e6dac212a7bd0d6ab85f309851b3a0aa98d00a40
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `events_statements_history` . `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 140
*************************** 10. row ***************************
THREAD_ID: 48
EVENT_ID: 143
END_EVENT_ID: 143
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6775563357093000
TIMER_END: 6775563691960000
TIMER_WAIT: 334867000
LOCK_TIME: 203000000
SQL_TEXT: select * from performance_schema.events_statements_history where THREAD_ID=1
DIGEST: ab6f69218695e267b8f701180da603950ed43ba9bc61078687a72522066428a0
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 141
10 rows in set (0.00 sec)

作用：

a. 控制刷新脏页到磁盘（CKPT）
b. 控制日志缓冲刷新到磁盘（log buffer ---> redo）
c. undo页回收
d. 合并插入缓冲(change buffer)
e. 控制IO刷新数量

mySQL> show variables like '%pct%';
+------------------------------------------+-----------+
| Variable_name | Value |
+------------------------------------------+-----------+
| innodb_buffer_pool_dump_pct | 25 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_pad_pct_max | 50 |
| innodb_idle_flush_pct | 100 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
| innodb_old_blocks_pct | 37 |
+------------------------------------------+-----------+

#参数解释：
innodb_io_capacity 表示每秒刷新脏页的数量，默认为200。
innodb_max_dirty_pages_pct 设置出发刷盘的脏页百分比(70%~90%)，即当脏页占到缓冲区数据达到这个百分比时，就会刷新innodb_io_capacity 个脏页到磁盘。
参数

innodb_adaptive_flushing = ON （自适应地刷新），该值影响每秒刷新脏页的数量。

#原来的刷新规则是：脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时，不刷新脏页；大于innodb_max_dirty_pages_pct时，刷新100个脏页。随着innodb_adaptive_flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。粗略地翻阅源代码后发现buf_flush_get_desired_flush_rate通过判断产生重做日志（redo log）的速度来决定最合适的刷新脏页数量。因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

#脏页

#1.什么叫脏页：
平时很快的更新操作，都是在写内存和日志。他并不会马上同步到磁盘数据页,这时内存数据页跟磁盘数据页内容不一致,我们称之为脏页
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
一条 SQL 语句，正常执行的时候特别快，偶尔很慢。那这时候可能就是在将脏页同步到磁盘中了
#2.什么时候会引起将脏页同步到磁盘中？
(1) 当 redo log写满了。这时候系统就会停止所有的更新操作，将更新的这部分日志对应的脏页同步到磁盘中，此时所有的更新全部停止，此时写的性能变为0，必须待刷一部分脏页后才能更新,这时就会导致 sql语句执行的很慢。
(2) 也可能是系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，则需要先将脏页同步到磁盘,空出来的给别的数据页使用。
(3) MySQL 认为系统“空闲”的时候,反正闲着也是闲着反正有机会就同步到磁盘一些数据
(4) MySQL 正常关闭。这时候，MySQL 会把内存的脏页都同步到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

#通过以下命令查看IO线程，其中：写线程和读线程分别由innodb_write_threads和innodb_read_threads参数控制，默认都为4。
mySQL> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-12-08 21:47:28 140335511410432 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 3438 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16393
Purge done for trx's n:o < 16390 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421810516540824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810516539968, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810516539112, 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)