MySQL · 特性分析 · 线程池

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

概述

MySQL 原有线程调度方式有每个连接一个线程(one-thread-per-connection)和所有连接一个线程(no-threads)。

no-threads一般用于调试,生产环境一般用one-thread-per-connection方式。one-thread-per-connection 适合于低并发长连接的环境,而在高并发或大量短连接环境下,大量创建和销毁线程,以及线程上下文切换,会严重影响性能。另外 one-thread-per-connection 对于大量连接数扩展也会影响性能。

为了解决上述问题,MariaDB、Percona、Oracle MySQL 都推出了线程池方案,它们的实现方式大体相似,这里以 Percona 为例来简略介绍实现原理,同时会介绍我们在其基础上的一些改进。

实现

线程池方案下,用户的每个连接不再对应一个线程。线程池由一系列 worker 线程组成,这些worker线程被分为thread_pool_size个group。用户的连接按 round-robin 的方式映射到相应的group 中,一个连接可以由一个group中的一个或多个worker线程来处理。

  1. listener 线程
    每个group中有一个listener线程,通过epoll的方式来监听本group中连接的事件。listener线程同时也是worker线程,listener线程不是固定的。
    listener线程监听到连接事件后会将事件放入优先级队列中,listener线程作为worker线程也处理一些连接事件,以减少上下文切换。
    listener线程会检查优先级队列是否为空,如果为空表示网络空闲,listener线程会作为worker线程处理第一个监听事件,其他事件仍然放入优先级队列中。
    另外,当没有活跃线时,listener会唤醒一个线程,如果没有线程可以唤醒,且当前group只有一个线程且为listener,则创建一个线程。

  2. 优先级队列
    分为高优先级队列和普通队列,已经开启的事务并且tickets不为0,放入高优先队列,否则放入普通队列。每个连接在thread_pool_high_prio_tickets次被放到优先队列中后,会移到普通队列中。worker线程先从高优先队列取event处理,只有当高优先队列为空时才从普通队列取event处理。
    通过优先级队列,可以让已经开启的事务或短事务得到优先处理,及时提交释放锁等资源。

  3. worker 线程
    worker线程负责从优先队列取事件处理。如果没有取到event,会尝试从epoll中取一个,如果没有取到再进入等待,如果等待超过thread_pool_idle_timeout worker线程会退出。

  4. timer 线程
    每隔thread_pool_stall_limit时间检查一次。
    • listener没有接收新的事件,listener正在等待时需调用wake_or_create_thread,重新创建listener;
    • 从上一次检查起,worker线程没有收到新的事件,并且队列不为空,则认为发生了stall,需唤醒或创建worker线程;
    • 检查net_wait_timeout是否超时,如果超时退出连接,而不是退出worker线程。
  5. 何时唤醒或创建worker线程
    • 从队列中取事件时发现没有活跃线程时;
    • worker线程发生等待且没有活跃线程时;
    • timer线程认为发生了stall;

重要参数解析

  1. thread_pool_oversubscribe
    一个group中活跃线程和等待中的线程超过thread_pool_oversubscribe时,不会创建新的线程。
    此参数可以控制系统的并发数,同时可以防止调度上的死锁,考虑如下情况,A、B、C三个事务,A、B 需等待C提交。A、B先得到调度,同时活跃线程数达到了thread_pool_max_threads上限,随后C继续执行提交,此时已经没有线程来处理C提交,从而导致A、B一直等待。thread_pool_oversubscribe控制group中活跃线程和等待中的线程总数,从而防止了上述情况。

  2. thread_pool_stall_limit
    timer线程检测间隔。此参数设置过小,会导致创建过多的线程,从而产生较多的线程上下文切换,但可以及时处理锁等待的场景,避免死锁。参数设置过大,对长语句有益,但会阻塞短语句的执行。参数设置需视具体情况而定,例如99%的语句10ms内可以完成,那么我们可以将就thread_pool_stall_limit设置为10ms

一些改进

  1. lock tables read 的处理
    对于声明 lock tables read 等明确声明表锁的事件,放入高优先级队列。

  2. binlog dump线程的处理
    binlog dump线程是典型的长事务场景,当多个binlog dump线程分配到同一个group中时,group中的线程很容易超过thread_pool_oversubscribe限制,从而导致性能下降。
    优化方法是修改binlog dump线程不受thread_pool_oversubscribe限制。

  3. 丰富诊断信息information_schema.thread_group_status

     show create table THREAD_GROUP_STATUS\G
     *************************** 1. row ***************************
            Table: THREAD_GROUP_STATUS
     Create Table: CREATE TEMPORARY TABLE `THREAD_GROUP_STATUS` (
       `ID` int(21) unsigned NOT NULL DEFAULT '0',
       `THREAD_COUNT` int(21) unsigned NOT NULL DEFAULT '0',
       `ACTIVE_THREAD_COUNT` int(21) unsigned NOT NULL DEFAULT '0',
       `CONNECTION_COUNT` int(21) unsigned NOT NULL DEFAULT '0',
       `WAITING_THREAD_COUNT` int(21) unsigned NOT NULL DEFAULT '0',
       `DUMP_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
       `LOW_QUEUE_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
       `HIGH_QUEUE_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0'
     ) ENGINE=MEMORY DEFAULT CHARSET=utf8
    
  4. 线程池调度异常,无法连接的处理
    对于本地登录的用户,走老的one_thread_per_connection逻辑,从而解决无法连接的情况。

连接池和线程池的区别

最后说一点连接池和线程池的区别。连接池和线程池是两个独立的概念,连接池是在客户端的优化,缓存客户的连接,避免重复创建和销毁连接。而线程池是服务器端的优化。两者的优化角度不同,不相关,因此两种优化可以同时使用。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
22天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1585 14
|
8天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
2月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
124 10
MySQL 8.0 新特性
|
2月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
329 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
23天前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性?
【10月更文挑战第3天】MySQL8.2有哪些新特性?
29 2
|
25天前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
54 4
|
24天前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
124 0
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
198 2
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
86 6
|
26天前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析?
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析,帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划,其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议,帮助你理解和应用`EXPLAIN`,提升数据库查询效率。
47 0