5. MySQL 线程

EnigmaGcl
164 阅读7分钟

5. MySQL 线程.png

1. MySQL 分层模型

MySQL 分层模型

2. Server层线程

2.1 用户线程

客户端连接 MySQL 后，MySQL 服务端跟客户端交互的线程，客户端跟服务端线程 1:1

2.1.1 thread_cache_size

  • 参数：thread_cache_size
  • 默认值：min(8+max_connections/100, 100)
  • 作用：将用户线程缓存

2.1.2 用户线程三种状态

  • 等连接：blocked_util_new_connection（connecting）
  • 长链接空闲：protocol_classic::read_packet（sleep）
  • 执行状态：mysql_execute_command

2.2 监听线程

负责监听新连接请求，从 thread_cache 或者新建线程接管，Mysqld_socket_listener::listen_for_connection_event

密码验证逻辑是在监听线程做的还是用户线程做的？答案是用户线程

2.3 两个跟 GTID 相关的线程

2.3.1 compress_gtid_table

只在开启 gtid，但是不开 binlog 时才工作，可以不用管

2.3.2 gtid_executed_compression_period

gtid 压缩的线程

Clone_persist_gtid::periodic_write：将 gtid 持久化，有两个参数：

  • s_time_threshold=100（100ms 持久化一次）
  • s_compression_threshold=50（每 50 个 gtid 做一次压缩）

2.4 事件调度器线程（Event_scheduler）

MySQL 会将待执行的 event 保存在队列中，时间调度线程会轮询队列，拿出 event 给创建线程来做

2.5 信号处理线程

2.6 用户线程占用内存大户

2.6.1 binlog_cache_size（row）、binlog_stmt_cache_size（statement）

这两个配置是线程 binlog 缓存大小，分别表示 row 和 statement 格式缓存大小，线程独占

  • 申请时机：会话中第一次要写 binlog 时，两个一起申请，申请定义值
  • 回收时机：回收连接到 thread_cache 或销毁时，一起回收

一般建议设置为 1M、2M、10M 等不要太小的值，下面例子说明 bing_log_cache 的分配策略

binlog_cache_size 和 binlog_stmt_cache_size 默认值 32k，库里有个空表t

connect  
T0:  0K
truncate table t; 
T1:  64K

T2:  64k
truncate table t;

T3: disconnect   64k

set global binlog_cache_size=3G  
set global binlog_stmt_cache_size=3G

T4:  0K
connect0K  
T5:  6G
truncate table t; T6:

2.6.2 sort buffer（join buffer、tmp_tale）

  • 申请时机：语句执行过程中需要相应操作时申请
  • 回收时机：语句操作完成后回收

虽然用完就释放，但是要注意在并发高的情况下，可能出现内存占用过大的情形

下面例子说明 sort buffer 分配策略

connect  
set sort_buffer_size=32k;  
T0:  0K
select ... order //一个能够吃满sort_buffer 的SQL   32K
T1:  0K
set sort_buffer_size=3G  
select ... order //一个能够吃满sort_buffer 的SQL   3G
T2:  0K
disconnect

2.7 如何尽量减少长连接数量

image.png

我们常用的客户端连接池其实是没法减少 MySQL 服务端连接数的，只能加个中间层代理，所有请求走代理，然后代理创建一个连接池才能真正减少连接数量

这种情形下，连接是复用的，需要代理保存每个请求连接相关的参数，然后在执行时先保存现场，然后加载对应请求的现场，这样才能保证请求顺利执行

2.8 MySQL 线程池（企业版才有，付费内容，mariadb 免费提供）

中间层连接池能减少 MySQL 的连接数，在 MySQL 5.6 版本之前，没有引入线程池机制，默认情况下每个客户端连接会对应一个独立的线程来处理。

从 MySQL 5.6 版本开始引入了线程池机制。在使用线程池的情况下，连接与线程不再是一一对应的关系。线程池预先创建了一定数量的线程并将它们组织成多个线程组（worker 线程组）。客户端连接的请求会被放入任务队列中，线程池中的线程会从任务队列中取出任务进行处理。多个连接的请求可以被不同的线程依次处理，一个线程也可以在不同时刻处理不同连接的请求，实现了线程的复用。

image.png 如上所示，client 有很多，连接也有很多，但是 MySQL 干活的线程是一定的，这跟 epoll 很像，一个线程用于处理连接请求，其余数据收发交给线程池来做，这样能实现少量线程服务多数连接的目的，同样的，worker 线程也需要保存请求执行的上下文，也就是图中会话相关的对象

始终要有一个概念，连接≠线程

3. InnoDB 线程

MySQL 中跟 innodb 相关的线程可以通过select thread_id,name from performance_schema.threads where name like '%innodb%' order by name;来查询

thread_idname
39thread/innodb/buf_dump_thread
29thread/innodb/buf_resize_thread
40thread/innodb/clone_gtid_thread
31thread/innodb/dict_stats_thread
32thread/innodb/fts_optimize_thread
4thread/innodb/io_ibuf_thread
5thread/innodb/io_read_thread
6thread/innodb/io_read_thread
7thread/innodb/io_read_thread
8thread/innodb/io_read_thread
9thread/innodb/io_read_thread
10thread/innodb/io_read_thread
11thread/innodb/io_write_thread
12thread/innodb/io_write_thread
13thread/innodb/io_write_thread
14thread/innodb/io_write_thread
16thread/innodb/log_checkpointer_thread
21thread/innodb/log_files_governor_thread
17thread/innodb/log_flush_notifier_thread
18thread/innodb/log_flusher_thread
19thread/innodb/log_write_notifier_thread
20thread/innodb/log_writer_thread
15thread/innodb/page_flush_coordinator_thread
27thread/innodb/srv_error_monitor_thread
26thread/innodb/srv_lock_timeout_thread
30thread/innodb/srv_master_thread
28thread/innodb/srv_monitor_thread
41thread/innodb/srv_purge_thread

3.1 buf_dump_thread

参数 innodb_buffer_pool_dump_now 的值永远是 off，执行语句 set global innodb_buffer_pool_dump_now = on，作用是执行一次 buf_dump，然后该值仍旧为 off，执行 buf_dump 就是 buf_dump_thread 来做的

文件内容如下：

image.png

参数 innodb_buffer_pool_dump_pct=x 将 buffer pool instance 中的 LRU 链表前 x% 存入 dump 文件，作用是当 MySQL 重启时，能读该文件来恢复一部分 LRU，相当于预热，避免重启后 buffer_pool 命中率下降很多

建议修改为 62.5，即 5/8，原因是 buffer_pool 中分为新老，其中新占 5/8，也就是非常活跃的数据

这个特性特别适合存储计算分离的场景，以亚马逊欧若拉为例，新增读实例时，直接将该 dump 文件传输给新增的实例恢复 buffer_pool，这样能保证新库速度很快，但是对于一般的主从结构，是不能将主库的 dump 文件给从库用的，因为磁盘上数据位置不同

3.2 ib_io_read

这不是一个线程，这是一个 InnoDB 存储引擎执行的磁盘读取操作，有多种线程都能执行该操作，该操作的触发有以下几种：

  1. 线性预读 innodb_read_ahead_threshold，默认值为 54，表示如果顺序读了 54 个页，那就将整个段后面的 10 页一起预读
  2. 随机预读 innodb_random_read_ahead，默认值为 off，表示一个段如果有 13 个页被读了，就将整个段剩余的页都预读， 建议关闭
  3. buf_read_ibuf_merge_pages —— io_ibuf_thread，作用是将 change buffer 的内容应用到页，比如从磁盘读取页，然后应用 change buffer，页变为脏页等待刷盘

3.3 page_flush_coordinator_thread

innodb_page_cleaner 默认值为 4，即有四个线程在做 flush 刷脏的动作，默认要求是不超过 innodb_buffer_pool_instances

3.4 purge thread

purge 是用来清理 undo 的

问答

  1. 为什么 OLTP 型基本要求 MySQL buffer pool 命中率要达到 99%，怎么能达到？

    buffer pool 的大小肯定是小于磁盘空间的，比如磁盘有 1T，buffer pool 只有 128GB，大概是 1/10，那么怎么用 1/10 的空间实现 99% 左右的命中率呢？这是由于二八原则的原因，不是所有数据都是频繁访问的数据，如果是正常业务，一般只会访问其中 20% 左右的数据，因此能用 1/10 左右的内存实现 99% 左右的命中率

  2. 一个正常运行的实例，rm 掉 ibdata，会怎么样？

    服务继续正常使用，但是下次重启时，会报错

avatar
文章
阅读
粉丝