Mysql的连接情况

Mysql进程情况

复杂语句执行情况（优化前为0.034s）

现在有一个mysql的服务器，所有mysql的配置都是按照默认的配置的。每小时的访问量大约在200个用户左右，访问量不算太大，也不算太少。如果不对默认配置进行优化的情况下，当访问量达到100人左右，服务器的内存占用率就会接近90%，因此从101人开始访问就会变得很卡或者连不上，非常影响用户体验。因此我们要分析问题对相关的配置选项进行优化。

根据上面的情况我们分析出这个mysql服务器可能存在着一下几个原因，导致了上述的一些问题的存在

1. 连接人数没有设置到位导致了101人后的访问有问题

2. 空闲连接长时间没断，导致了内存占用率过大

3. Mysql没有充分利用到CPU，导致了处理速度跟不上，因此导致了访问有卡顿的现象

   
   

我们根据上面的问题对mysql的配置进行一下必要的优化，看看是不是真的有效吧

一台2核1G的虚拟机

然后在虚拟机安装mysql5.7，php7.0等

研究MySQL配置对性能的影响，为我们选择最优配置

第一：解决连接人数没有设置到位导致了101人后的访问有问题

1. back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。这里的默认值为50，也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

   
   

   
   

2. 修改max_connections参数值，默认100max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以通过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

   
   

3. max_user_connections值，默认为0。max_user_connections是指每个数据库用户的最大连接针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

根据我们服务器的情况，要实验环境解决第一个问题，我们把back_log设置成500（也就是125M），让mysql的堆增大，以适应后续的连接。然后把max_connections设置成3000（也就是750M）让连接数提高，再把max_user_connections修改成500，限制一下单用户的并行数就可以了。

第二：空闲连接长时间没断，导致了内存占用率过大

wait-timeout，MySQL客户端的数据库连接闲置最大时间值。顾名思义，这个参数是控制mysql的链接时间的。这个参数在mysql里面是默认8个小时才断开连接的。

如果有大量的空闲连接在连着数据库，那么真实大大地浪费资源。因此我把虚拟主机里面的wait-timeout改成了30分钟，这就意味着空闲连接30分钟就会自动断开。如果设置这一个配置的话你看看你们的业务场景吧，看看多长时间是最优解。

我认为一般20-30分钟就可以适应很多的场景了。我们用php连接mysql做了实验，当mysql连接达到最大值时候，后面的就连不上了。还有一种情况是，空闲连接过多也会影响到mysql的执行效率，也影响到了服务器的内存。

这个问题的解决方案，我们把空闲连接的过期时间设置成20分钟即可，空闲的连接不应该存在这么长的时间，这样子就节约一下开支了。

第三：Mysql没有充分利用到CPU，导致了处理速度跟不上，因此导致了访问有卡顿的现象

thread_concurrency，默认8

thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8

这个问题的解决方案，我们把thread_concurrency设置成与cpu对应的值就可以把cpu效率发挥到最好的状态了

我们对上述的参数做了相关的优化，然后我们现在来看一下服务器的基本情况吧

Mysql进程情况

Mysql的连接情况

Mysql执行情况

优化后，空闲连接比例有了不少的降幅这样子可以节省更多的内存，优化了CPU使用情况，执行速度也变快了，用户体验方面也不会忽然有一两个连接发生卡顿的的问题。总的来说这个优化还是看到有变化的。

我们对一个具体的案例进行了问题分析，与配置的优化。在这个过程里面，我们应该先把问题找出来，然后根据问题的特点对数据库的配置进行相关的查找，查找到了相关的参数之后我们可以用一些试探地更改一下相关参数值，然后看看执行速率是否有变化，服务器各种资源的占有率是否有优化，然后根据服务器的配置进行参数的最优配置，这样子出来的mysql一定可以达到服务器的最优的情况。所以各位看看过可以试试我的步骤去对你的数据库服务器进行一下优化。