[翻译]:MySQL Error: Too many connections

翻译:MySQL
Error: Too many connections

从昨天开始,我们突然遇到了一个问题,就是连接到数据库时,提示Too many
connections
.即使知道这个错误是由于同时打开的连接数太多导致的,然而还是有点懵.因为即使我停止了微服务,在通过show
status
查看MySQL的当前连接数,还是没有变化.

 

一天中出现了两次这样的错误,由于没有时间来解决,所以我只能暂时通过重启MySQL的做法,来暂时处理一下.但是这样终究不是长远之计.

前言: 本文是对Muhammad
Irfan的这篇博客MySQL Error: Too many
connections的翻译,如有翻译不对或不好的地方,敬请指出,大家一起学习进步。尊重原创和翻译劳动成果,转载时请注明出处。谢谢!

我们通过show
processlist
命令,发现大多数连接都处于sleep状态,也就是说,并没有被使用,也没有被释放.而HikariCP确实在微服务被停止时,被关闭了.于是猜测是微服务被关闭时,出了些问题,比如突然断网了,导致MySQL只好等待一定的超时时间,等到请求超时之后,才关闭连接.

 

而这个超时时间一般不应该是很短吗?为什么连接在微服务停止后的半个小时之后都没有被释放?

 

实际上,这个超时时间,默认是八个小时!!!

英文原文地址:

在MySQL的配置文件中,使用wait_timeout这一项来表示.

 

这么长的超时时间,对于使用连接池的有状态的Java应用来说,可能正好合适.但是对于一些不使用连接池的应用来说,如果连接没有被正确关闭,就要等待8小时超时之后才能关闭,未免有点浪费资源.这样很快就到达了MySQL的max_connections限制,导致出现Too
many connections
这个错误.

 

实际上,在我们开发阶段,因为使用了连接池,反而更加助纣为虐,因为我们需要频繁的关闭并重新打包微服务,然后重新运行,每次运行,都需要新建一个有200个连接的连接池,而我们的MySQL的**max_connections
**总共才300.这样在第二次运行时,就会出现错误.

翻译原文地址:www.cnblogs.com/kerrycode/p/8965151.html

所以,我们先调整一下MySQL的wait_timeout,将其调整成10分钟,同时,减少Hikari连接池的建立的连接数,将其调整成10.

 

而且,我注意到,只有当我的HikariCP连接池设置的很大时,出现了Too many
connections
错误时,此时停止微服务,才会导致连接并没有被完全释放.不知道是否是HikariCP连接池的bug.有时间阅读一下源码,看一下.

必赢365net手机版, 

 

我们总是在Percona
Support上收到一些关于如何避免糟糕的“Too
many connections”错误,以及参数max_connections的推荐值这样一类问题。因此在这篇文章中,我尽最大的努力以最佳答案涵盖这方面的问题,减少遇到同样问题的人的困惑。

 

我的同事Aurimas写了一篇关于如何通过GDB在线更改max_connections值的精彩文章,当MySQL服务正在运行时,无需重新启动MySQL即可摆脱“Too
many connections”错误
你可以点击这儿查看文章详细内容。

 

    在MySQL
5.5中,默认的最大同时客户端连接数为151
,如果MySQL服务器达到了max_connections的限制,你再尝试去连接MySQL服务器时就会遇到“Too
many
connections”错误。这意味着所有可用的连接都在被其它客户端使用。

 

   
MySQL在达到max_connections限制时,允许一个额外的具有SUPER权限的连接连接到MySQL去诊断连接问题,通常来说,管理员用户有SUPER权限,你应该避免授予应用程序用户(app
users) SUPPER权限。

 

MySQL的每一个客户端连接使用一个线程(也就是说连接和线程是一对一的关系),太多活动的线程也是数据库的性能杀手,通常来说,一个大量高并发连接,大量并发执行大量查询会导致明显的数据库性能下降,并且增加出现死锁的风险。在MySQL
5.5之前,MySQL的可扩展性不好,当然从这个版本之后它变的越来越好,但是如果你有数以百计的活动连接(不计算休眠(sleeping)状态的连接)则会导致使用内存的增长。每一个连接都会给线程分配线程缓存区(thread
buffer),因此还有可能在内存表(memory
tables)中要求跟多的内存以满足global buffers的要求,
最重要的是每个连接都必须使用tmp_table_size/max_headp_table.尽管它们不会立即分配给每个连接。

 

大多数情况下,连接数过多这个错误出现是因为应用程序的一些不能正确的关闭连接bug导致或者是错误的设计的缘故,例如那些已经建立的连接,但是在MySQL指针(句柄)关闭之前,一直在忙着做其他事情。在应用程序没有正确的关闭连接的情况下,
wait_timeout是一个重要的参数去优化或丢弃那些没有使用或空闲的连接,将活动会话最大限度减少到最小数量。这最终将有助于避免“Too
many connections”错误。
也有一些系统即使有大量连接线程,但是系统运行良好,大部分连接是空闲的,通常,休眠的线程不会占用太大内存-
512KB或更少,
Threads_running是一个有价值的监视指标,当然它不包括休眠线程-
它表示当前活动的最大线程。而Threads_connected状态变量表示所有连接的线程,
Peter写了一篇很好的文章发布在这里,你可以从这里进一步查看详细内容。

 

   
如果你在应用程序端使用连接池, max_connections
必须比接池的最大连接数要大。
使用连接池是一个可选的方案,如果你遇到了大量的连接,那么max_connections的推荐设置是什么呢?
这个没有绝对答案,这个取决于可用的RAM总量以及每一个连接使用的内存数量,增加max_connections值会增加mysqld进程需要的文件描述符数量,注意:设置最大max_connections没有硬性限制,所以,你必须根据你数据库的负载合理设置max_connections的值,以及同时连接到MySQL的连接数量等,通常来讲,太大的max_connections是不推荐使用的,因为那些激烈竞争的连接问题可能会引起在某些条件下的锁定或性能下降,
如果活动连接使用临时表或内存表,内存使用量会变得非常高,在那些内存较小的服务器或那些应用程序端具有很多连接控制的系统上,我们应该使用较小的max_connection值,例如100-300,
16G
RAM或更高内存的服务器,设置max_connection=1000是一个较好的注意,当然,每个连接缓冲区应该具有良好的默认值,而在某些系统上,我们可以看到高达8k
最大的连接,但是这样的系统在负载峰值时通常会性能下降。

 

 

为了解决这个问题,ORACLE和MariaDB团队在MySQL内部实现了线程池(thread pool), Percona Server从MariaDB移植了此功能。它在Percona
Server中的实现可以阅读此链接。正确的配置线程池,至少对于某些类型的工作负载,您可能预期的吞吐量至少至多达数千个并发连接。

 

注意事项:请注意,在MySQL 5.6中,如果max_connections的值设置太高,会引起大量的内存分配,这个bug的报告在这里

 

 

 

结论:

 

没有一个固定的规则为max_connectins的设置合适值,因为它取决于你数据库的工作负载,考虑到每个连接的线程都需要分配内存以及昂贵的上下文切换,我建议根据你系统的负载选择合适的max_connections数量,并尽量避免同时打开太多的连接,以便应用程序正常运行。

 

————————————————————-分割线————————————————————–

 

上面是翻译部分,下面关于MySQL的“Too
many connections”有下面三个问题,简单汇总总结一下,如有不足或错误之处,敬请指正:

 

1:Too many connections
发生的场景?

 

    1、应用程序的Bug或不合理设计。

 

        1.1  没有及时关闭数据库连接或出现Bug(例如,循环里面打开数据连接或
代码里面忘记关闭数据库连接等等)。

        1.2  在应用程序层没有使用连接池。

         
………………..

 

    2、出现阻塞,导致大量连接被阻塞,在高并发的情况下导致连接数彪增。出现“Too
many connections”

 

    3、MySQL的连接对应一个线程,在慢查询较多的情况下,导致线程数与连接数持续增长。

 

    4、突发性业务彪增引起的。

   

 

2:如何避免Too many
connections错误发生

 

    1、
应用程序在完成数据相关操作后及时关闭连接。

 

    2、
使用连接池(connection
pool),有些版本可以使用线程池(MySQL企业版、MariaDB等)

 

    3、
设置wait_timeout为一个合适的值,例如wait_timeout=10秒。减少大量空闲连接。

 

    4、
监控数据库阻塞情况,避免由于异常或特殊情况导致大量连接被阻塞。

 

    5、
根据系统负载,合理设置max_connections的值,避免max_connections参数设置过小引起“MySQL
Error: Too many connections”

 

 

 

  

Leave a Comment.