MySQL提高并发数

一、MySQL并发连接数

MySQL是单进程，多线程的架构，通过创建多个线程来服务于不同的用户连接。通常情况下，随着用户连接数的增加，MySQL内部用于处理用户连接的线程也会同步的增加，在一定范围内，增加用户并发连接，对提高系统的吞吐量有一定的帮助，然而用户并发连接数超过某个阈值，MySQL的性能反而会降低。

二、MySQL连接处理方式

MySQL内部处理用户连接的方式严格来说，有三种：

• 单线程处理所有用户连接，一般在调试时使用。
• 多线程处理用户连接，一个线程对应一个用户连接，也是MySQL默认的连接处理方式。
• 线程池处理用户连接，一个线程对应多个用户连接，比如MySQL Percona发行版提供了线程池功能。

三、线程缓存

MySQL默认的连接处理方式，即一个线程对应一个用户连接，在某些场景下，有一些性能问题。比如大量的短连接，短时间内大量的连接创建和销毁，意味着MySQL内部必须要有同样数量的线程创建和销毁，线程的创建、销毁以及线程上下文切换，会带来严重的性能消耗。

因此，MySQL开发者提出了线程缓存的概念，缓存一定数量的线程来服务用户连接，用户连接到来时，先检查线程缓存中有没有可用的线程，如果没有再去创建，如果有，直接使用线程缓存中的线程。用户连接断开时，线程放回到线程缓存中，以便后来的用户连接重用，减少线程频繁创建和销毁。

在我们平时开发过程中，我们的程序一般会使用数据库连接池，比如SpringBoot默认采用的是hikari

四、代表用户连接的数据结构THD

用户连接在MySQL实现中由一个数据结构THD表示，THD在连接建立时被创建，在连接断开时销毁，用户连接与THD的关系永远是一对一的关系，THD不能跨连接重用，THD结构大概有10K大小，它是一个C++类，在 sql_class.h文件中定义。THD用于记录各种执行状态，在SQL语句执行的过程中，THD会根据实际情况申请内存使用，尤其对于复杂的SQL，THD占用的内存大小会不断增长，平均下来，一个连接大概10M左右。

五、设置合适的连接数

MySQL能够处理的最大连接数量由参数max_connections决定，但是如何设置max_connections的值才是最合适的呢？一般来说需要根据具体的机器配置和业务要求。通常我们需要MySQL能够并发处理的用户连接数越多越好，并发用户数增加的情况下，SQL的执行响应时间不能增长太多，同时内存占用也不能太高，因此需要在并发连接数、响应时间、内存占用等方面做出平衡，并且要能满足业务的对SQL执行的吞吐量和响应时间要求。

如何确定MySQL的最大负载和最大并发度？通过压测，不断增加客户端并发数。比如起始客户端并发数为2，依次递增到4，8，16，32等等，每次递增，在没有达到性能瓶颈前，压测的TPS会随着客户端并发数的增加而增大，响应时间通常会随着客户端并发数的增加而增加，但是增加的幅度并不明显。当客户端并发数达到某个阈值时，比如128，或者256甚至更大时，TPS不再增长，反而出现下降，响应时间则会出现跳跃式的增长，此时则可以认为MySQL已经达到了当前配置的最大性能。不同的硬件配置下，MySQL最大性能的并发连接数也不相同，需要根据具体的环境测试，一般的原则是最大并发连接数等于CPU逻辑核心数的4倍。

此外，如果业务对响应时间有一定的要求，比如业务要求响应时间50ms，在低于50ms的响应时间之内，TPS越高越好。这时就需要通过压测来确定最合适的并发连接数，在响应时间不超过50ms的前提下，不断提高并发数，获取最佳性能。然而不同的机器配置，不同的业务类型，可能会有不同的结果。

六、connection相关参数

• max_connections： 最大连接数，默认为 151 ，可动态修改。
• max_connect_errors： 当同一个客户端连接出错的次数达到- max_connect_errors时，服务器将阻止该主机进行再次连接。默认为 100 ，可动态修改。
• max_user_connections： 对于单个数据库用户允许的最大同时连接数。默认为 0 ，即表示无限制，可动态修改。
• connect_timeout： 等待一个连接响应的时间，默认为 10s ，在获取连接阶段起作用，可动态修改。

通过MySQL我们可以查到相关参数说明，如下：

1、MySQL 8.0 max_connections 参数

MySQL 8.0 max_connections 参数

2、MySQL 5.7 max_connections 参数

MySQL 5.7 max_connections 参数

3、MySQL 8.0 max_user_connections 参数

MySQL 8.0 max_user_connections 参数

对于MySQL服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

Max_used_connections / max_connections * 100% = 3/512 *100% ≈ 0.0058%

七、命令

7.1 查看、修改max_connections

# 查看相关参数
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

# 修改参数 (my.cnf配置文件中也要修改)
mysql> set global max_connections = 500;
Query OK, 0 rows affected (0.79 sec)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

7.2 show status

mysql>show status like '%变量名%';

变量名如下：

• Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。

• Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。

• Connections 试图连接MySQL服务器的次数。

• Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。

• Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。

• Delayed_writes 用INSERT DELAYED写入的行数。

• Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。

• Flush_commands 执行FLUSH命令的次数。

• Handler_delete 请求从一张表中删除行的次数。

• Handler_read_first 请求读入表中第一行的次数。

• Handler_read_key 请求数字基于键读行。

• Handler_read_next 请求读入基于一个键的一行的次数。

• Handler_read_rnd 请求读入基于一个固定位置的一行的次数。

• Handler_update 请求更新表中一行的次数。

• Handler_write 请求向表中插入一行的次数。

• Key_blocks_used 用于关键字缓存的块的数量。

• Key_read_requests 请求从缓存读入一个键值的次数。

• Key_reads 从磁盘物理读入一个键值的次数。

• Key_write_requests 请求将一个关键字块写入缓存次数。

• Key_writes 将一个键值块物理写入磁盘的次数。

• Max_used_connections 同时使用的连接的最大数目。

• Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。

• Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。

• Open_tables 打开表的数量。

• Open_files 打开文件的数量。

• Open_streams 打开流的数量(主要用于日志记载）

• Opened_tables 已经打开的表的数量。

• Questions 发往服务器的查询的数量。

• Slow_queries 要花超过long_query_time时间的查询数量。

• Threads_connected 当前打开的连接的数量。

• Threads_running 不在睡眠的线程数量。

• Uptime 服务器工作了多长时间，单位秒。

例如：

mysql> show status like  'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 32    |
| Threads_connected | 10    |
| Threads_created   | 50    |
| Threads_rejected  | 0     |
| Threads_running   | 1     |
+-------------------+-------+
5 rows in set (0.00 sec)

• Threads_connected ： 这个数值指的是打开的连接数.

• Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值.

Threads_connected跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数。

八、几个常见连接错误

若 connection 相关参数设置不当，到达一定瓶颈时可能会出现连接错误的问题，下面列举下几种常见的连接错误及解决方法。

出现频繁最高的可能就是 Too many connections错误了，这个错误发生的原因是当前数据库的总连接数已经达到了max_connections 数值，当再有客户端尝试连接时及会报此错误。 MySQL 实际上允许 max_connections + 1个客户端连接，额外一个连接供具有 SUPER 特权的用户使用。当发生Too many connections 错误时，我看可以用 root 账号登录数据库，然后增大 max_connections 参数即可。其实设置适当的 max_connections即可避免此类错误再次发生，默认的 151 显然有点小，我们可以监控下数据库连接总数，根据不同环境做出调整，比如设为 500 或 1000，当然也不能设置过大。

若出现 Host is blocked because of many connection errors错误时，那就是某台主机连接错误达到 max_connect_errors参数规定的上限了。解决这个错误有两种方案，一个是进入 MySQL 命令行执行 flush hosts 命令来刷新 host ，一个是增大max_connect_errors 参数值。个人建议可以将 max_connect_errors参数调大些，比如 1000 或 2000 。

对于 max_user_connections 参数，一般采用默认 0 值即可，若想要限制单个用户的最大连接数，也可以设置下此参数，比如 max_user_connections = 100 代表单个用户最多同时发起 100 个连接。

如果客户端经常遇到 Lost connection to MySQL server at 'XXX'错误，那就要考虑增大 connect_timeout值了，默认值 10s对于网络良好的情况下是够用的，如果客户端和服务端网络有延迟的情况，可以将 connect_timeout参数调大来避免发生连接超时的错误。

九、其他优化方向

9.1 过度依赖数据库

首先，数据库并不是万能，不要把各位运算和数据都存放进去，尽量减小数据压力；其次，合理使用缓存，例如Memcache和Redis，我们都知道二八定律，它同样适用于互联网，百分之八十的请求最终落在百分之二十的业务上，所以能不查询数据库的时候尽量不要去找数据库。

9.2 谨慎使用长链接

很多时候是不需要使用长连接的，特别在一些网站中（比如PHP中mysql_pconnect），短连接能解决的问题就用短连接，长连接有可能导致大量连接得不到释放无法使用。

9.3 优化慢SQL

SQL越快，连接占用的时间就越短。

9.4 循环里不要调用DB执行SQL

避免在循环里写调用SQL的代码，而是在循环外批量执行。