MySQL连接线程之Thread参数调优

点击上方蓝字关注我哦

一、MySQL线程概述

thread_cache_size 功能在mysql数据库配置文件中是非常重要的一项功能了，如果对thread_cache_size优化做得好我们可以让服务器跑得非常快，设置不好就会发现很小访问量就非常的卡。

MySQL连接除了能通过网络方式，还可以通过命名管道的方式，不论是哪种方式连接MySQL，在MySQL中都是通过线程的方式管理所有客户端请求的。每一个客户端连接都会有一个与之对应的连接线程。

在MySQL中实现了一个Thread Cache池，将空闲的连接线程存放其中，而不是完成请求后就销毁。这样当有新的连接请求时，MySQL首先会检查Thread Cache中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。

二、线程参数介绍

具体参数：

Thread_cache_size：ThreadCache池中应该存放的连接线程数。

查询Thread_cache_size的配置：

show variables like "thread_cache_size";

Thread_stack：每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。

查看连接线程相关的系统变量的设置值：show variables likethread_cache%;如上图，系统默认连接线程设置thread_cache_size=64，通过分析计算连接线程池最多将缓存约三十个连接线程左右，每个连接线程创建之初，系统分配一定的内存堆栈给这些线程。

三、生产环境的调优

查看系统被连接的次数及当前系统中连接线程的状态值：

show status like connections;

show status like %thread%;

如果我们在MySQL服务器配置文件中设置了thread_cache_size,当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

Threads_created：表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。

根据官网资料和生产实际环境验证，如果以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能，通过比较Connections 和Threads_created 状态的变量，可以看到这个变量的作用。(“--->？”表示要调整的值)  根据物理内存设置规则如下：

1、Thread Cache参数分析

系统启动到现在共接受到客户端的连接10246394次，共创建了2766个连接线程，当前有8个连接线程处于和客户端连接的状态，而49个连接状态的线程中只有一个处于active 状态，即只有一个正在处理客户端提交的请求，而在ThreadCache池中共缓存了49个连接线程。

Thread Cache 命中率：

Thread_Cache_Hit = (Connections - Threads_created) / Connections *100%;一般在系统稳定运行一段时间后，ThreadCache命中率应该保持在90%左右才算正常。

实际应用：

针对16G/32G的机器，一般设置512K够用了，当然如果遇到下面的错误提示就应该考虑增加这个值。

2、thread_cache_size参数设置分析

如果是短连接，适当设置大一点，因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。

对于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在50-100左右。

3、Thread Stack参数报错

根据业务侧后台java程序进行数据处理时，thread_stack报错提示进行分析，并提供解决方法如下。

官方相关thread_stack的信息分析:

The default (192KB) is large enough for normal operation. If thethread stack size is too small, it limits the complexity of the SQLstatements that the server can handle, the recursion depth of storedprocedures, and other memory-consuming actions.

根据官方对该参数的信息分析如下：

Thread Stack默认值（192KB）足够正常运行。如果线程堆栈大小太小，则会限制服务器可以处理的SQL语句的复杂性，存储过程的递归深度以及其他消耗内存的操作。可以使用showvariables where `variable_name` =“thread_stack”;查询当前数据库的默认线程栈的大小，一般情况下都能正常使用，但是当查询语句或者存储过程复杂时会报Threadstack overrun（超限）错误，此时只要修改增加默认配置就可以了。

四、Thread的配置修改

解决方法：

在数据库中修改配置文件my.cnf只有这个文件才能生效，然后重启服务数据库。

[mysqld] 

thread_stack = 512k

thread_cache_size = 64

重启数据库之后：

通过以上处理方式，根据业务侧后台java程序调取数据过程中发生thread_stack报错，进行调优，然后通知业务侧重新开启java程序数据处理目前一切正常。

END

长按二维码识别

精彩内容早知道