Mysql线程池优化笔记

Mysql线程池系列一(Thread pool FAQ)

首先介绍什么是mysql thread pool,干什么用的？
使用线程池主要可以达到以下两个目的：
1、在大并发的时候,性能不会因为过载而迅速下降。
2、减少性能抖动

thread pool的工作原理？
线程池使用分而治之的方法来限制和平衡并发性。与默认的thread_handling不同,线程池将连接和线程划分开,所以连接数量和执行语句的线程数不再是固定的关系,线程池可以通过
配置线程组来管理连接,然后再根据每个语句的关键字来确定是优先执行或者排队执行。

mysql thread pool和client端的connection pool的不同之处？
client段的connection pool：连接池主要用来管理客户端的连接,避免重复的连接/断开操作,而是将空闲的连接缓存起来,可以复用。从而减少了连接mysql server/断开mysql server的开销与成本,从而提升性能。
但是mysql的connection pool不能获取mysql server的查询处理能力以及当前的负载情况。
thread pool:线程池的操作是在mysql server端,并且设计就是用来管理当前并发的连接和查询.

thread pool到底能够提升多少性能?
根据Oracle Mysql官方的性能测试
在并发达到128个连接以后.没有线程池的Mysql性能会迅速降低。使用线程池以后,性能不会出现波动,会一直保持在较好的状态运行。
在读写模式下,128个连接以后,有线程池的Mysql比没有线程池的Mysql性能高出60倍。
在只读模式下,512个连接以后,有线程池的Mysql比没有线程池的Mysql性能高出18倍。

什么时候可以考虑使用thread_pool？
* show global status like ‘%threads_running%’;的值是mysql server当前并发执行语句的数量轨迹,如果这个值一直保持在40左右的区间,那么可以考虑使用thread pool。
*如果你使用了innodb_thread_concurrency参数来控制并发的事物量,那么使用线程池将会获得更好的效果。
*如果你的工作是有很多短连接组成的,那么使用线程池是有益的。

说一下oracle mysql thread pool插件的限制：
1、Oracle Mysql enterprise 6.10版本添加的,也就是说小于这个版本的企业版不支持,目前所有的oracle mysql community版本也不支持。
2、如果是windows的系统,需要是vista或者以后的版本,如果是linux,需要2.6.9以后的内核。

Mysql线程池系列二(Oracle Mysql Thread pool的安装和原理)

thread pool的组件和安装
thread pool是以插件的方式存在的,安装thread pool插件以后,会增加一些information_schema表和相关参数变量。
information_schema表包含:
TP_THREAD_STATE
TP_THREAD_GROUP_STATE
TP_THREAD_GROUP_STATS

新增加的参数变量:
thread_handling增加了一个值,loaded-dynamically,当成功加载thread pool插件的时候就是这个值了。
thread_pool_algorithm:
thread_pool_high_priority_connection:
thread_pool_prio_kickup_timer:
thread_pool_max_unused_threads:
thread_pool_size：
thread_pool_stall_limit:
如果这些值设置的不正确,那么启动mysql的时候插件会初始化失败,插件将不能加载。
这些变量的具体设置方法,会在接下来的优化章节里面详细的介绍。

thread pool插件的对象库必须放在plugin_dir变量对应的目录里。为了使thread pool生效,可以在启动mysql的时候使用–plugin-load选项。或者修改my.cnf文件.在[mysqld]区段中添加如下信息
[mysqld]
plugin-load=thread_pool.so

thread pool的原理
thread pool包含数个thread groups,每个thread group管理一组客户端连接。当连接建立以后,thread pool以轮询的方式分配他们到thread group.
thread group的数量是通过thread_pool_size配置得到的,默认是16个,最大64个,最小1个。
每个thread group最大可以有4096个线程。

线程池把连接和线程分开了,所以连接和线程不是固定对应的,线程执行从connections收到的语句,这和默认的thread_handling模式不同。

thread_handling参数
原来的版本里面有一个thread_handling参数,可以设置thread的工作模式,有两个值,
一个是no-threads,指任意时刻最多只有一个连接可以连接到mysql server,一般用于调试。另外一个是one-thread-per-connection,是指针对每个连接创建一个线程来处理这个连接的所有请求,直到连接断开,线程结束.这也是thread_handling的默认值。
由此可见,默认情况下,多少连接就会产生多少个线程,并发越大,线程越多,线程之间的资源竞争越激烈,性能越低。

thread pool插件提供另外的一种thread_handling方法,用来有效的管理执行线程与大量客户端连接,从而提高性能。
线程池解决的几个问题:
*高并发的多线程栈导致CPU的缓存几乎失效,线程池促进线程堆栈重用,减少CPU缓存量。
*太多的线程并发执行,上下文切换开销很高,这对操作系统的任务调度是一个很大的挑战,线程池可以把mysql活跃的并发线程控制在一个适合mysql server运行的水平。
*太多的事务并发执行会增加资源争用,在innodb引擎里,会增加获取central mutexes的时间,线程池可以控制事务的并发量。

thread pool尝试保证每个thread group中的每个thread尽量执行更多的语句,但是有些时候允许更多的线程执行一些临时的任务来提高性能。算法的工作方式如下:
*每个trhead group有一个listener,这个listener负责监听分配给thread group的statements,thread group有两种执行方案,一是立即执行,一种是排队执行。
*立即执行的条件是当前只收到一条statement,并且当前没有statements在执行。
*排队执行发生在不能立即执行的时候
*当立即执行发生的时候,是由listener线程执行的,也就是说listener在执行一些临时的statements,如果立即执行的statement很快执行完成,那么这个线程会变回listener线程,如果其他情况thread pool会考虑从新开启一个listener线程来代替它,是否需要创建listener线程是由thread pool的后台线程来监控和执行的。
当thread pool插件启动以后,每个thread group会创建一个listener线程,加上background线程,其他线程根据是否需要而创建。

thread_pool_stall_limit系统变量的含义可以理解为完成一个statement需要的时间,默认认为stalled的时间是60ms,最大可以设置为6s。配置这个参数可以让你平衡服务器的工作负载.这个值设置的越小线程启动越快,更小的值可以更好的避免死锁,更大的值通常在很多长查询的时候使用,为了避免启动太多的线程。

thread pool的焦点在于限制并发的短查询语句的数量,在一个语句执行时间没有达到stall的时候,阻止其他statements开始执行,如果一个statement执行超过了stall time,它将会继续执行,但是不在阻止其他statement开始执行。用这种方法,thread pool尝试确保每个thread group从来没有超过一个short-running statement,尽管会有多个long-running statement。让长时间执行的语句阻止其他语句的执行,这是不可取的,因为没有限制等待的最长时间.例如,在一个replication的master,一个线程一直发送binlog给slave。

一个statement因为I/O操作或者用户级别的锁被阻塞了,这个阻塞将会导致thread group无效,所以回调函数会通知thread pool确认,并且thread pool会马上在这个thread group中启动一个新的线程执行其他的statement.当被阻塞的线程返回时,thrad pool允许马上重新启动。

这里有两种队列(queue),一种是高优先级的队列(high-priority queue),和一种低优先级的队列(low-priority queue).事务中的第一个statement会被分配到低优先级的队列,剩下的statement将会被分配到高优先级的队列里(前提是这个事务已经开始执行了),或者被分配到低优先级队列。
队列的分配受到thread_pool_high_priority_connection系统变量影响,这个参数的默认值是0,表示同时使用低优先级队列和高优先级队列,如果值设置为1,所有queued statements都会被直接分配到高优先级的队列。

对于非事务的存储引擎的statements,或者是autocommit的存储引擎,都会被放入低优先级的queue处理,因为每个statement都是一个事务。因此,使用innodb和myisam混合引擎的数据库,thread pool认为innodb的优先级高于myisam的优先级,除非innodb开启了autocommit。如果autocommit开启,那么所有的statements都属于低优先级。

当thread group选择一个queue中的statement执行的时候,它会优先在高优先级的queue中查找,然后才在低优先级的queue中查找,如果找到tatement,他就会从queue中删除这个statement,然后开始执行它。

如果一个statement在低优先级的queue中等待很久,它将被thread pool移动到高优先级的queue里.等待的时间由thread_pool_prio_kickup_timer决定。

thread pool对活跃线程的重用,可以更好的使用CPU caches.这个很小的调整对性能的提升却有很大帮助。

thread group分配多个线程执行statement的情况:
*一个线程开始执行一个statement,但是执行时间达到stalled以后,thread group允许其他线程开始执行其他statement,之前的线程继续执行之前的statement。
*一个线程开始执行一个statement,但是线程被阻塞了,报告给thread pool以后,thread group允许其他线程开始执行其他statement。
*一个线程开始执行一个statement,但是线程被阻塞了,由于阻塞不是发生在代码层,所以没有报告给thread pool。当阻塞时间达到stall以后,thread group允许其他线程执行其他statement。

线程的设计可以针对不断增加的连接具有扩展性,同时他的设计也可以通过限制并发的thread来尽量避免死锁发生.但是要注意的是,阻塞的线程如果没有报告thread pool,那么thread pool就不会阻塞其他线程的运行,
这种情况可能会导致线程池死锁。
*长时间运行的statments,很少的statements将使用所有的资源,这将导致服务器拒绝所有其他的访问。
*binary log dump线程读取binlog,然后发送给slave,这是一种长时间运行的”statement”,他不会阻止其他的statements运行.
*statement可以被row级别、table级别的锁阻塞,也可以被sleep等其他原因的锁阻塞,或者其他被阻塞的没有报告thread pool的thread阻塞了。

上面每种情况,都是为了防止死锁,没有快速执行完成的statement将被移动到stalled分类,所以thread group允许其他statement开始执行。由于这个设计,当线程在执行或者被阻塞的时间内,thread pool把这些线程
标记为stalled类型,然后余下的statement将会被执行,它没有拒绝其他statments的执行.

最大的线程数可以达到max_connections和thread_pool_size的和,这种情况只有在所有的连接都在同时执行,并且每个thread group开启一个listen线程来监听新的statement。这种情况很难发生,但是理论上存在。

Mysql线程池系列三(Oracle Mysql Thread pool调优)

首先明确调优的目的是提高TPS。

thread_pool_size:
是一个非常重要的参数,控制thread pool的性能,具体表现为thread group的数量。只能在server启动之前设置,我们测试thread pool的结果如下:
*如果主存储引擎是innodb,thread_pool_size设置在16至36之间,大多数情况设置在24到36,我们还没有发现什么情况需要设置超过36，也只有一些特殊的环境需要设置小于16.
使用DBT2或者sysbench做测试的时候,innodb引擎下通常设置为36个,如果在一些写入特别多的环境,这个值可以设置的更小一些。
*如果主存储引擎是myisam,thread_pool_size需要设置的更低,我们推荐的值是4到8,更高的值可能会对性能有负面影响。

thread_pool_stall_limit:
这个参数对于处理阻塞和长时间执行的语句很重要。这个时间是从一个statement从开始执行到执行完成总花费的时间,如果超过设置值就被认定为stalled,此时线程池也开始允许执行另外
一个statement。这个值的单位是10毫秒,默认值是6，也就是默认间隔是60ms,一个statement执行超过60ms,就被认为是stalled。最大值是600,也就是6秒。一般这个值设置为你99%的statement可以执行完的时间。比如我慢查询设置的
是0.1,那么这里就设置为10。另外可以通过
SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED) FROM information_schema.TP_THREAD_GROUP_STATS;来获取stalled的比例,这个值尽量的小,为了避免stall,可以调高thread_pool_stall_limit的值。

thread_pool_prio_kickup_timer:
这个值影响低优先级的statements的queue。参数值的单位是毫秒,低优先级的statement需要等到多少毫秒才能被移动到高优先级的queue.默认是1000,也就是1秒,值的范围是(0-4294967294)。

thread_pool_high_priority_connection:
这个参数主要决定新来的statements的执行优先级。默认值是0,表示同时使用low-prority queue和high-priority queue。如果设置为1,所有的statement都会分配到high-priority queue。

thread_pool_max_unused_threads:
这个参数限制thread pool中sleeping thread的最大数量。从而限制sleeping thread对内存的使用。
如果参数的值为0,也就是默认值,意味着对sleeping thread没有限制.假设值为N，当N大于0的时候,意味着1个consumer thread和N-1个 reserve thread。意思也就是说,当一个线程执行完一个statement,将要转为sleeping状态的时候,这时sleeping状态的
线程数量已经达到了允许的sleeping thread的最大数量,那么这个线程将会退出。
关于consumer thread:sleeping thread由consumer thread和reserve thread组成,thread pool允许sleeping thread中有一个consumer thread,一个thread要转变为sleepling thread的时候,如果没有consumer thread 存在,那么
这个thread将转变为consumer thread.当一个sleeping thread要被唤醒的时候,如果存在consumer thread,那么优先唤醒consumer thread,如果consumer thread不存在,那么唤醒reserve thread。

thread_pool_algorithm:
此参数决定thread pool使用那种算法.默认值是0,表示使用较低的并发算法,在大多数测试和生产环境下效果很好。
另外一个值是1,更加积极的增加并发数量的算法,有时候会比最佳线程数量性能更好,但是随着连接的增加,性能会逐渐下降。所以这个参数主要用在实验环境。