204_mysql_innodb_5_Innodb 后台线程

Innodb引擎是多线程模型，不同后台线程, 负责处理不同业务

# 1. 前台线程（连接层）

show processlist ; show full processlist; select * from information_schema.processlist ;

# 2. 后台线程（Server\Engine）

mysql> select * from performance_schema.threads\G 说明： 如何查询到连接线程和SQL线程关系 select * from information_schema.processlist ; ---> ID=10 select * from performance_schema.threads where processlist_id=10\G

1 master thread核心线程

负责

控制刷新脏页到磁盘（CKPT）

控制日志缓冲刷新到磁盘（log buffer ---> redo）

undo页回收

合并插入缓冲(change buffer)

控制IO刷新数量

说明： innodb_io_capacity表示每秒刷新脏页的数量，默认为200。 innodb_max_dirty_pages_pct设置出发刷盘的脏页百分比，即当脏页占到缓冲区数据达到这个百分比时，就会刷新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通过判断产生重做日志（redolog）的速度来决定最合适的刷新脏页数量 因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

2 IO thread

Innodb 使用 AIO（Async IO）来处理写IO请求，提高性能， 4个核心IO thread  read/write  insert buffer/log io thread

show variables like '%innodb_%io_threads';

show engine innodb status \G;

I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread)

3 Purge Thread

事物提交后 需要purge  thread 来回收已经使用分配的undo页

show variables like '%innodb_purge_threads%';

4 page cleaner Thread

将刷脏页单独线程完成，较少 master thread 压力，提高性能

show variables like '%innodb_page_cleaners%';

5 其它线程： SQL线程/连接层的线程（前台）

其它

查询系统中所有业务表的存储引擎信息

查询系统中所有业务表的存储引擎信息 select table_schema, table_name , engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); 巡检需求： 将业务表中所有非InnoDB查询出来 mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine !='innodb'; 1. 查询所有非InnoDB表 mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb'; 2. 备份所有非InnoDB表 select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb'; 3. 修改存储引擎 mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql'; mysql> source /tmp/a.sql 3. 修改存储引擎 mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql'; mysql> source /tmp/a.sql

碎片情况

alter table world.xxx engine=innodb ALGORITHM=COPY; analyze table world.city; 转储表（推荐） create table t1_bak like t1; insert into t1_bak selewct * from t1; drop table t1 ; rename table t1_bak to t1; Mysqldump 导出 导入。 或者工具 pt-os ghost 注意： 1. 最好是空窗期做 2. 准备double的存储空间 tmpdir 3. 整理碎片只对 InnoDB 独立表空间方式有效

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。