InnoDB-InnoDB线程

参考文献

• 千金良方: MySQL性能优化金字塔法则

InnoDB后台线程

mysql> select name,type,thread_id,processlist_id from performance_schema.threads;
+---------------------------------------------+------------+-----------+----------------+
| name                                        | type       | thread_id | processlist_id |
+---------------------------------------------+------------+-----------+----------------+
| thread/sql/main                             | BACKGROUND |         1 |           NULL |
| thread/innodb/io_ibuf_thread                | BACKGROUND |         3 |           NULL |
| thread/innodb/io_read_thread                | BACKGROUND |         4 |           NULL |
| thread/innodb/io_read_thread                | BACKGROUND |         5 |           NULL |
| thread/innodb/io_read_thread                | BACKGROUND |         6 |           NULL |
| thread/innodb/io_read_thread                | BACKGROUND |         7 |           NULL |
| thread/innodb/io_write_thread               | BACKGROUND |         8 |           NULL |
| thread/innodb/io_write_thread               | BACKGROUND |         9 |           NULL |
| thread/innodb/io_write_thread               | BACKGROUND |        10 |           NULL |
| thread/innodb/io_write_thread               | BACKGROUND |        11 |           NULL |
| thread/innodb/page_flush_coordinator_thread | BACKGROUND |        12 |           NULL |
| thread/innodb/log_checkpointer_thread       | BACKGROUND |        13 |           NULL |
| thread/innodb/log_flush_notifier_thread     | BACKGROUND |        14 |           NULL |
| thread/innodb/log_flusher_thread            | BACKGROUND |        15 |           NULL |
| thread/innodb/log_write_notifier_thread     | BACKGROUND |        16 |           NULL |
| thread/innodb/log_writer_thread             | BACKGROUND |        17 |           NULL |
| thread/innodb/log_files_governor_thread     | BACKGROUND |        18 |           NULL |
| thread/innodb/srv_lock_timeout_thread       | BACKGROUND |        23 |           NULL |
| thread/innodb/srv_error_monitor_thread      | BACKGROUND |        24 |           NULL |
| thread/innodb/srv_monitor_thread            | BACKGROUND |        25 |           NULL |
| thread/innodb/buf_resize_thread             | BACKGROUND |        26 |           NULL |
| thread/innodb/srv_master_thread             | BACKGROUND |        27 |           NULL |
| thread/innodb/dict_stats_thread             | BACKGROUND |        28 |           NULL |
| thread/innodb/fts_optimize_thread           | BACKGROUND |        29 |           NULL |
| thread/mysqlx/worker                        | BACKGROUND |        30 |           NULL |
| thread/mysqlx/worker                        | BACKGROUND |        31 |           NULL |
| thread/mysqlx/acceptor_network              | BACKGROUND |        32 |           NULL |
| thread/innodb/buf_dump_thread               | BACKGROUND |        36 |           NULL |
| thread/innodb/clone_gtid_thread             | BACKGROUND |        37 |           NULL |
| thread/innodb/srv_purge_thread              | BACKGROUND |        38 |           NULL |
| thread/innodb/srv_worker_thread             | BACKGROUND |        39 |           NULL |
| thread/innodb/srv_worker_thread             | BACKGROUND |        40 |           NULL |
| thread/innodb/srv_worker_thread             | BACKGROUND |        41 |           NULL |
| thread/sql/event_scheduler                  | FOREGROUND |        42 |              5 |
| thread/sql/signal_handler                   | BACKGROUND |        43 |           NULL |
| thread/mysqlx/acceptor_network              | BACKGROUND |        44 |           NULL |
| thread/sql/compress_gtid_table              | FOREGROUND |        46 |              7 |
| thread/sql/one_connection                   | FOREGROUND |        61 |             22 |
| thread/sql/one_connection                   | FOREGROUND |        65 |             26 |
+---------------------------------------------+------------+-----------+----------------+
39 rows in set (0.00 sec)

线程说明

线程名	中文名称	说明
srv_master_thread	主线程	InnoDB存储引擎主线程,有4个循环正常,即主循环(loop),后台循环backgroud loop),刷新循环(flush loop),暂停循环(suspend loop).其中大多数工作在主循环中完成,主循环主要负责将脏页缓存页刷新到数据文件中,执行undo purge操作,触发检查点,合并插入缓冲区,刷新redo log到磁盘中等.
io_ibuf_thread	插入缓冲线程	主要负责插入缓冲区的合并操作.将对辅助索引页的修改操作从随机变成顺序I/O,大幅度提升了效率(会先判断发生修改的辅助索引页是否在缓冲池中,如果在则直接修改;如果不在,则先存放在Change Buffer对象中.当其他读取操作把该修改对应的页从磁盘读取到缓冲池中时,就会合并该Change Buffer对象中保存的记录到辅助索引页中)
io_read_thread	读I/O操作线程	负责数据库的AIO读取操作,可以配置多个读线程,由参数innodb_read_io_threads设置,默认值为4
io_write_thread	写I/O操作线程	负责数据的AIO写操作,可配置多个写线程,由参数innodb_write_io_threads设置,默认值为4.
srv_purge_thread	undo清理线程	主要负责undo页清理操作.在MySQL5.6之后可设置独立的线程执行undo purge操作,以减少主线程负载.
srv_lock_timeout_thread	锁线程	负责锁控制和死锁检测等
srv_error_monitor_thread	错误监控线程	主要负责错误控制和错误处理
main	主线程	MySQL服务的主线程(请与InnoDB存储引擎主线程区别开),包括初始化、读取配置文件等功能
srv_worker_thread	InnoDB工作线程	InnoDB的实际工作线程,轮询从任务队列中取出任务(row select、row insert等)并执行
buf_dump_thread	InnoDB缓冲池导入/导出线程	InnoDB缓冲池热点数据页导入/导出的线程
signal_handler	信号处理线程	负责SIGTERM、SIGQUIT、SIGHUP信号处理线程

InnoDB前台线程

mysql> select name,thread_id,processlist_id from performance_schema.threads where type='FOREGROUND';
+--------------------------------+-----------+----------------+
| name                           | thread_id | processlist_id |
+--------------------------------+-----------+----------------+
| thread/sql/replica_io          |        42 |              5 |
| thread/sql/replica_sql         |        43 |              6 |
| thread/sql/event_scheduler     |        44 |              7 |
| thread/sql/replica_worker      |        46 |              9 |
| thread/sql/replica_worker      |        47 |             10 |
| thread/sql/compress_gtid_table |        50 |             11 |
| thread/sql/replica_worker      |        51 |             12 |
| thread/sql/replica_worker      |        52 |             13 |
| thread/sql/one_connection      |        55 |             16 |
+--------------------------------+-----------+----------------+
9 rows in set (0.00 sec)

线程名	中文名称	说明
compress_gtid_table	GTID压缩线程	用于压缩MySQL5.7新增的mysql.gtid_executed表中的GTID记录数量.在MySQL 5.7版本中,当从库关闭log-bin或者log_slave_updates参数之后,SQL线程每应用一个事务就会实时更新一次mysql.gtid_executed表(在MySQL 5.7中启用GTID复制时可以关闭log_slave_updates参数, 使用该表来记录GTID.但在MySQL5.6中由于没有此表,所以不能关闭log_slave_updates参数),时间一长,该表中就会存在大量的GTID记录(每个事务一行),使用该线程可以把多行记录压缩成一行
one_connection	用户连接线程	用于处理用户请求的线程
replica_io	I/O线程	用于拉取主库binlog日志的线程
replica_sql	SQL线程	用于应用从主库拉取的binlog日志的线程.注意：在多线程复制中,该线程为协调器线程,用于分发binlog日志给工作线程(slave_worker)应用,并对多个工作线程进行协调.
replica_worker	工作线程	在多线程复制场景中,接收并应用SQL线程(slave_sql)分发的主库binlog日志,多个工作线程之间的一致性依靠SQL线程(slav_sql)进行协调.