1.前言
如果mysql实例连接数满掉了,脑袋里蹦出的第一个想法是先增大max_connection,但有种情况,解决方案却是减少max_connection。
2.冰中火
看看这张图:
各位看完后一定很愤怒,CPU Util,CPU Load,磁盘IO和网络IO指标都很低,毫无性能压力的服务器指标,有啥好看的(其实是有指标异常的)。
冰块中的火
别急,在毫无波澜的服务器上,mysql实例内部已经翻山倒海了,请看下图:
有接近三万的活动连接,所有的连接都是执行非常简单的单条insert语句。但是,这些sql语句,都已经执行了十多分钟还未结束。而且再过不到十分钟,mysql实例将实现自我涅槃(自动重启)。
简直就是在冰块中熊熊燃烧着一团火。
3.故障起因
某日,业务开发突然反馈,应用出现大量无法建立新连接的报错。
连接数爆满
登录上mysql实例发现,最大3万的连接数,已经满的不能再满了。
实例中全部都是活动连接
大量执行简单的单行insert
没有行/表锁阻塞
我司针对java和C#开发了sdk和连接中间件,但是这个业务的应用是C写的,无法接入中间件,所以数据库的连接数会一直涨到满。高配服务器低性能? 普通的ssd服务器,几万qps的插入肯定是没问题的,这么好的机器(112核CPU,256G内存,磁盘PCIE SSD),几十万的qps应该不在话下吧,好歹PCIE SSD比普通ssd磁盘至少有10倍的读写提升,难道就撑不住这些业务量吗? 思维误区 等等,这里存在一个思维误区,升级服务器配置是否能提高数据库的吞吐量?对于oracle来说,是毋容置疑的,但对于mysql,是不是这样的呢? 先调查一下 mysql的error log里有线索: InnoDB: Error: semaphore wait has lasted> 600 seconds 这么明显的提示,啥都别说了,翻源码。 自保(自杀)机制 Srv0srv.cc文件中1750行(mysql版本为5.6.31),后台线程会循环检查原子锁的等待时间,如果连续10次检测超时,则自杀。 其实看到semaphore,基本上就知道原子锁是脱不了干系了。 网上搜索一下解决方案:
修改内核参数
禁用自适应哈希索引。
耗时第一个多的是mtr_s_lock方法,这个是对B树操作的原子锁。
耗时第二多的是rw_lock_s_lock_func方法,这个是innodb的latch原子锁。
一类是不会回旋的锁,
一类是带回旋的锁(相当于oracle里的latch)。
代码描述
这段代码起了3万个线程,一起竞争原子锁。运行结果很喜人,除了csw指标上去了,其他指标基本没变化。这就解释了上面mysql实例内部原子锁大量竞争阻塞,而服务器没有任何报警的原因。 7.减少竞争者 之所以会有这么大量的原子锁竞争,是竞争者太多了,那么, 减少最大连接数,是不是就能减少原子锁竞争呢? 减少竞争将max_connection改成1.5万,跑了下压力,发现insert语句依旧卡着,说明竞争依旧很激烈,但是,不再出现自动重启的现象了,而且当应用停止后,这些残留的sql会慢慢的执行完。
将max_connection改成3000,发现insert语句基本上2分钟执行完,竞争已经减轻很多了。
将max_connection改成300,语句执行毫无压力,但明显没有发挥出服务器的真正实力。
mariadb10.0.24
mysql5.7.30
这里有个小插曲,并发达到3万2千多的时候,死活就打不上去了。 操作系统的ulimit,实例的innodb_buffer_pool_instances和innodb_open_files都调过了,没有任何作用,只有调整了操作系统的max_map_count,才能够成功突破这个限制,顺利达到6万并发。mysql8.0.21 最后是mysql8.0.21版本的压测,6万并发,服务器的CPU和磁盘IO还有一定的活动, 未出现完全卡死的情况 。
这里还有个小插曲,当并发打到6万5千多并发后,就上不去了,如果强行打上去,会报Not enough resources to complete lock request的错误,应该是哪里设置了65535的限制,以后翻源码瞅瞅。总结 由上面的测试可以发现, mysql随着版本的增加,高并发的性能越来越好 ,其关键就在对原子锁的优化上,这样才能充分利用服务器资源, 在服务器硬件扩展中,获得红利 。 全文完。