您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL数据库性能优化之thread pool 原理分析,值得收藏

时间:2019-09-06 08:57:32  来源:  作者:

概述

今天分享下MySQL数据库的 thread pool。


一、大连接问题

mysql5.6之前处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接,新建的线程会处理该连接所发送的所有 SQL 请求,即 one-thread-per-connection 的方式,其创建连接的堆栈为:

mysqld_main
	handle_connections_sockets
		create_new_thread
			create_thread_to_handle_connection
				handle_one_connection

线程建立后,处理请求的堆栈如下:

0 mysql_execute_command
1 0x0000000000936f40 in mysql_parse
2 0x0000000000920664 in dispatch_command
3 0x000000000091e951 in do_command
4 0x00000000008c2cd4 in do_handle_one_connection
5 0x00000000008c2442 in handle_one_connection
6 0x0000003562e07851 in start_thread () from /lib64/libpthread.so.0
7 0x0000003562ae767d in clone () from /lib64/libc.so.6

二、优点及存在的问题

在连接数较小的情况下可以很快的响应客户端的请求,但当连接数非常大时会创建很多线程,这样会引起以下问题:

  1. 过多线程之间的切换会加重系统的负载,造成系统资源紧张且响应不及时;
  2. 频繁的进行线程的创建及销毁以及线程间同时无序的竟争系统资源加重了系统的负载。

thread_pool正是为了解决以上问题而产生的;


三、thread_pool

thread_pool(线程池),是指mysql 创建若干工作线程来共同处理所有连接的用户请求,用户的请求的方式不再是 ‘one thread per connection’,而是多个线程共同接收并处理多个连接的请求,在数据库的底层处理方面(mysql_execute_command),单线程的处理方式和线程池的处理方式是一致的。


四、thread_pool 的工作原理

启动 thread_pool 的mysql 会创建thread_pool_size 个thread group , 一个timer thread, 每个thread group 最多拥有thread_pool_oversubscribe个活动线程,一个listener线程,listener线程负责监听分配到thread group中的连接,并将监听到的事件放入到一个queue中,worker线程从queue中取出连接的事件并执行具体的操作,执行的过程和one thread per connection 相同。timer threaad 则是为了监听各个threadgroup的运行情况,并根据是否阴塞来创建新的worker线程。

1、thread_pool 建立连接

thread_pool 建立连接的堆栈如下:

mysqld_main
handle_connections_sockets
create_new_thread
tp_add_connection
queue_put

2、worker 处理请求

thread group中的 worker 处理请求的堆栈如下:

0 mysql_execute_command
1 0x0000000000936f40 in mysql_parse
2 0x0000000000920664 in dispatch_command
3 0x000000000091e951 in do_command
4 0x0000000000a78533 in threadpool_process_request
5 0x000000000066a10b in handle_event
6 0x000000000066a436 in worker_main
7 0x0000003562e07851 in start_thread ()
8 0x0000003562ae767d in clone ()

其中worker_main函数是woker线程的主函数,调用mysql本身的do_command 进行消息解析及处理,和one_thread_per_connection 是一样的逻辑; thread_pool 自行控制工作的线程个数,进而实现线程的管理。

3、thread_pool中线程的创建

  1. listener线程将监听事件放入mysql放入queue中时,如果发现当前thread group中的活跃线程数active_thread_count为零,则创建新的worker 线程;
  2. 正在执行的线程阻塞时,如果发现当前thread group中的活跃线程数active_thread_count为零,则创建新的worker 线程;
  3. timer线程在检测时发现没有listener线程且自上次检测以来没有新的请求时会创建新的worker线程,其中检测的时间受参数threadpool_stall_limit控制;
  4. timer线程在检测时发现没有执行过新的请求且执行队列queue 不为空时会创建新的worker线程;

worker线程的伪码如下:

MySQL数据库性能优化之thread pool 原理分析,值得收藏

 

4、thread_pool中线程的销毁

当从队列queue中取出的connection为空时,则此线程销毁,取connection所等待的时间受参数thread_pool_idle_timeout的控制; 综上,thread_pool通过线程的创建及销毁来自动处理worker的线程个数,在负载较高时,创建的线程数目较高,负载较低时,会销毁多余的worker线程,从而降低连接个数带来的影响的同时,提升稳定性及性能。同时,threadpool中引入了Timer 线程,主要做两个事情。

  1. 定期检查每个thread_group是否阻塞,如果阻塞,则进行唤醒或创建线程的工作;
  2. 检查每个thread_group中的连接是否超时,如果超时则关掉连接并释放相应的资源;

五、线程池实现

下图是线程池的实现框架,以及关键接口

MySQL数据库性能优化之thread pool 原理分析,值得收藏

 

每一个绿色的方框代表一个group,group数目由thread_pool_size参数决定。每个group包含一个优先队列和普通队列,包含一个listener线程和若干个工作线程,listener线程和worker线程可以动态转换,worker线程数目由工作负载决定,同时受到thread_pool_oversubscribe设置影响。此外,整个线程池有一个timer线程监控group,防止group“停滞”。

关键接口

1. tp_add_connection[处理新连接]

1) 创建一个connection对象
2) 根据thread_id%group_count确定connection分配到哪个group
3) 将connection放进对应group的队列
4) 如果当前活跃线程数为0,则创建一个工作线程

2. worker_main[工作线程]

1) 调用get_event获取请求
2) 如果存在请求,则调用handle_event进行处理
3) 否则,表示队列中已经没有请求,退出结束。

3. get_event[获取请求]

1) 获取一个连接请求
2) 如果存在,则立即返回,结束
3) 若此时group内没有listener,则线程转换为listener线程,阻塞等待
4) 若存在listener,则将线程加入等待队列头部
5) 线程休眠指定的时间(thread_pool_idle_timeout)
6) 如果依然没有被唤醒,是超时,则线程结束,结束退出
7) 否则,表示队列里有连接请求到来,跳转1

备注:获取连接请求前,会判断当前的活跃线程数是否超过了thread_pool_oversubscribe+1,若超过了,则将线程进入休眠状态。

4. handle_event[处理请求]

1) 判断连接是否进行登录验证,若没有,则进行登录验证
2) 关联thd实例信息
3) 获取网络数据包,分析请求
4) 调用do_command函数循环处理请求
5) 获取thd实例的套接字句柄,判断句柄是否在epoll的监听列表中
6) 若没有,调用epoll_ctl进行关联
7) 结束

5.listener[监听线程]

1) 调用epoll_wait进行对group关联的套接字监听,阻塞等待
2) 若请求到来,从阻塞中恢复
3) 根据连接的优先级别,确定是放入普通队列还是优先队列
4) 判断队列中任务是否为空
5) 若队列为空,则listener转换为worker线程
6) 若group内没有活跃线程,则唤醒一个线程

备注:这里epoll_wait监听group内所有连接的套接字,然后将监听到的连接

请求push到队列,worker线程从队列中获取任务,然后执行。

6. timer_thread[监控线程]

1) 若没有listener线程,并且最近没有io_event事件
2) 则创建一个唤醒或创建一个工作线程
3) 若group最近一段时间没有处理请求,并且队列里面有请求,则
4) 表示group已经stall,则唤醒或创建线程
5)检查是否有连接超时

备注:timer线程通过调用check_stall判断group是否处于stall状态,通过调用timeout_check检查客户端连接是否超时。

7.tp_wait_begin[进入等待状态流程]

1) active_thread_count减1,waiting_thread_count加1
2)设置connection->waiting= true
3) 若活跃线程数为0,并且任务队列不为空,或者没有监听线程,则
4) 唤醒或创建一个线程

8.tp_wait_end[结束等待状态流程]

1) 设置connection的waiting状态为false
2) active_thread_count加1,waiting_thread_count减1

备注:

  1. waiting_threads这个list里面的线程是空闲线程,并非等待线程,所谓空闲线程是随时可以处理任务的线程,而等待线程则是因为等待锁,或等待io操作等无法处理任务的线程。
  2. tp_wait_begin和tp_wait_end的主要作用是由于汇报状态,即使更新active_thread_count和waiting_thread_count的信息。

9. tp_init/tp_end

分别调用thread_group_init和thread_group_close来初始化和销毁线程池


六、线程池与连接池

连接池通常实现在Client端,是指应用(客户端)创建预先创建一定的连接,利用这些连接服务于客户端所有的DB请求。如果某一个时刻,空闲的连接数小于DB的请求数,则需要将请求排队,等待空闲连接处理。通过连接池可以复用连接,避免连接的频繁创建和释放,从而减少请求的平均响应时间,并且在请求繁忙时,通过请求排队,可以缓冲应用对DB的冲击。

线程池实现在server端,通过创建一定数量的线程服务DB请求,相对于one-conection-per-thread的一个线程服务一个连接的方式,线程池服务的最小单位是语句,即一个线程可以对应多个活跃的连接。通过线程池,可以将server端的服务线程数控制在一定的范围,减少了系统资源的竞争和线程上下文切换带来的消耗,同时也避免出现高连接数导致的高并发问题。连接池和线程池相辅相成,通过连接池可以减少连接的创建和释放,提高请求的平均响应时间,并能很好地控制一个应用的DB连接数,但无法控制整个应用集群的连接数规模,从而导致高连接数,通过线程池则可以很好地应对高连接数,保证server端能提供稳定的服务。

如图所示,每个web-server端维护了3个连接的连接池,对于连接池的每个连接实际不是独占db-server的一个worker,而是可能与其他连接共享。

这里假设db-server只有3个group,每个group只有一个worker,每个worker处理了2个连接的请求。

MySQL数据库性能优化之thread pool 原理分析,值得收藏

 


七、threadpool相关参数

1、thread_pool_high_prio_mode

有三个取值:transactions / statements / none

  • transactions(default): 使用优先队列和普通队列,对于事务已经开启的statement,放到优先队列中,否则放到普通队列中
  • statements:只使用优先队列
  • none: 只是用普通队列,本质上和statements相同,都是只是用一个队列

2、thread_pool_high_prio_tickets

取值0~4294967295,当开启了优先队列模式后(thread_pool_high_prio_mode=transactions),每个连接最多允许thread_pool_high_prio_tickets次被放到优先队列中,之后放到普通队列中,默认为4294967295

3、thread_pool_idle_timeout

worker线程最大空闲时间,单位为秒,超过限制后会退出,默认60

4、thread_pool_max_threads

threadpool中最大线程数目,所有group中worker线程总数超过该限制后不能继续创建更多线程,默认100000

5、thread_pool_oversubscribe

一个group中线程数过载限制,当一个group中线程数超过次限制后,继续创建worker线程会被延迟,默认3

6、thread_pool_size

threadpool中group数量,默认为cpu核心数,server启动时自动计算

7、thread_pool_stall_limit

timer线程检测间隔,单位为毫秒,默认500


八、threadpool优化

1.调度死锁解决

引入线程池解决了多线程高并发的问题,但也带来一个隐患。假设,A,B两个事务被分配到不同的group中执行,A事务已经开始,并且持有锁,但由于A所在的group比较繁忙,导致A执行一条语句后,不能立即获得调度执行;而B事务依赖A事务释放锁资源,虽然B事务可以被调度起来,但由于无法获得锁资源,导致仍然需要等待,这就是所谓的调度死锁。由于一个group会同时处理多个连接,但多个连接不是对等的。比如,有的连接是第一次发送请求;而有的连接对应的事务已经开启,并且持有了部分锁资源。为了减少锁资源争用,后者显然应该比前者优先处理,以达到尽早释放锁资源的目的。

因此mysql数据库在group里面添加一个优先级队列,将已经持有锁的连接,或者已经开启的事务的连接发起的请求放入优先队列,工作线程首先从优先队列获取任务执行。

2.大查询处理

假设一种场景,某个group里面的连接都是大查询,那么group里面的工作线程数很快就会达到thread_pool_oversubscribe参数设置值,对于后续的连接请求,则会响应不及时(没有更多的连接来处理),这时候group就发生了stall。通过前面分析知道,timer线程会定期检查这种情况,并创建一个新的worker线程来处理请求。如果长查询来源于业务请求,则此时所有group都面临这种问题,此时主机可能会由于负载过大,导致hang住的情况。这种情况线程池本身无能为力,因为源头可能是烂SQL并发,或者SQL没有走对执行计划导致,通过其他方法,比如SQL高低水位限流或者SQL过滤手段可以应急处理。但是,还有另外一种情况,就是dump任务。很多下游依赖于数据库的原始数据,通常通过dump命令将数据拉到下游,而这种dump任务通常都是耗时比较长,所以也可以认为是大查询。如果dump任务集中在一个group内,并导致其他正常业务请求无法立即响应,这个是不能容忍的,因为此时数据库并没有压力,只是因为采用了线程池策略,才导致了请求响应不及时,为了解决这个问题,mysql数据库将group中处理dump任务的线程不计入thread_pool_oversubscribe累计值,避免上述问题。



Tags:MySQL 优化   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
单表优化除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是...【详细内容】
2020-04-17  Tags: MySQL 优化  点击:(73)  评论:(0)  加入收藏
当MySQL单表记录数过大时,增加、删除、修改和查询的性能将急剧下降,您可以参考以下步骤进行优化。单表优化 除非你预计未来你的单表数据会不断的持续上涨,否则不要一开始就考虑...【详细内容】
2020-04-14  Tags: MySQL 优化  点击:(70)  评论:(0)  加入收藏
谁在消耗cpu?用户+系统+IO等待+软硬中断+空闲 祸首是谁?用户用户空间CPU消耗,各种逻辑运算正在进行大量tps函数/排序/类型转化/逻辑IO访问…用户空间消耗大量cpu,产生...【详细内容】
2020-03-09  Tags: MySQL 优化  点击:(144)  评论:(0)  加入收藏
千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议...【详细内容】
2020-02-11  Tags: MySQL 优化  点击:(60)  评论:(0)  加入收藏
概述优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。对于一名DBA来说,掌握一门语言配合自己的工作是非常必要的。相对于shell的...【详细内容】
2019-10-09  Tags: MySQL 优化  点击:(607)  评论:(0)  加入收藏
mysql5.6之前处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接,新建的线程会处理该连接所发送的所有 SQL 请求,即 one-thread-per-connection 的方式,其创建连接的堆栈为:...【详细内容】
2019-09-06  Tags: MySQL 优化  点击:(160)  评论:(0)  加入收藏
1 前言在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。 2 优化...【详细内容】
2019-09-05  Tags: MySQL 优化  点击:(151)  评论:(0)  加入收藏
MySQL 对于很多 Linux 从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些...【详细内容】
2019-09-03  Tags: MySQL 优化  点击:(157)  评论:(0)  加入收藏
数据千万级别之多,占用的存储空间也比较大,可想而知它不会存储在一块连续的物理空间上,而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较,就用更多的时间查找与比...【详细内容】
2019-08-27  Tags: MySQL 优化  点击:(268)  评论:(0)  加入收藏
个人的观点,这种大表的优化,不一定上来就要分库分表,因为表一旦被拆分,开发、运维的复杂度会直线上升,而大多数公司是欠缺这种能力的。所以MySQL中几百万甚至小几千万的表,先考虑...【详细内容】
2019-08-26  Tags: MySQL 优化  点击:(907)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条