MySQL ERROR 1040: Too many connections

如题,本章主要讲下当服务器出现 ERROR 1040: Too many connections错误时的一些处理心得。

max_connections查看

## 查看最大连接数
SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 512   |
+-----------------+-------+

## 查看已使用最大连接数
SHOW VARIABLES LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 499   |
+----------------------+-------+

  

处理方案

这个问题一般有两种处理方案,解决方案非常容易,我们只需要增加max_connections连接数即可。

增加当前会话的mysql最大连接数

SET GLOBAL max_connections = 1000;

上面mysql连接值临时增加到1000,但仅适用于当前会话。一旦我们重新启动mysql服务或重新启动系统,该值将重置为默认值。

永久增加mysql最大连接数

为了永久增加mysql连接数,我们需要编辑mysql配置文件,即/etc/my.cnf

sudo vim /etc/my.cnf

## 修改
max_connections = 1000

保存文件重启MySQL即可生效。

扩多少合适?

Max_connextions并不是越大越好的,那么如何配置?

方式一

对于提高MySQL的并发,很大程度取决于内存,官方提供了一个关于innodb的内存计算方式

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ max_connections * 2MB

方式二

安装比例扩容:

max_used_connections / max_connections * 100% = [85, 90]%

最大使用连接数/最大连接数达到了80%~90%区间,就建议进行优化或者扩容了。

扩展

以下也涉及几种常见的影响MySQL性能的情况:

线程

SHOW STATUS LIKE  'Threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 1      | 
| Threads_connected | 217    |
| Threads_created   | 29     |
| Threads_running   | 88     |
+-------------------+--------+

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 10     |
+-------------------+-------+
  • Threads_cached 线程在缓存中的数量
  • Threads_connected 当前打开的连接数
  • Threads_created 创建用于处理连接的线程数。
  • Threads_running 未休眠的线程数

如果Threads_created大,则可能要增加thread_cache_size值。缓存未命中率可以计算为Threads_created / Connections

查看表锁情况

SHOW GLOBAL STATUS LIKE 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 90    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
  • Table_locks_immediate 立即获得表锁请求的次数
  • Table_locks_waited 无法立即获得对表锁的请求的次数,需要等待。这个值过高说明性能可能出现了问题,并影响连接的释放

慢查询

show variables like '%slow%';

+---------------------------+----------------------------------------------+
| Variable_name             | Value                                        |
+---------------------------+----------------------------------------------+
| slow_launch_time          | 2                                            |
| slow_query_log            | On                                           |
+---------------------------+----------------------------------------------+

 

线程详情

## 查看每个线程的详细信息
SHOW PROCESSLIST;
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+
| Id     | User     | Host             | db           | Command | Time  | State       | Info             |
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+
|      3 | xxxadmin | localhost        | NULL         | Sleep   |     1 | cleaning up | NULL             |
|      4 | xxxadmin | localhost        | NULL         | Sleep   |     0 | cleaning up | NULL             |
|      5 | xxxadmin | localhost        | NULL         | Sleep   |     6 | cleaning up | NULL             |
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+

总结

当然,以上只是一个大概的解决思路,无论使用哪一种方式,都需要结合实际业务场景去扩容。

另外,对于生产环境,设置恰当的告警阈值,也是很有必要的。

最后,在编程时,由于用MySQL语句调用数据库执行SQL,会分配一个线程操作MySQL,所以在结束调用后,需要回收连接,避免泄漏。

参考文章

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

MySQL Calculator

posted @ 2020-12-05 23:22  WilburXu  阅读(788)  评论(0编辑  收藏  举报