如果mysql实例连接数满掉了，脑袋里蹦出的第一个想法是先增大max_connection，但有种情况，解决方案却是减少max_connection。
各位看完后一定很愤怒，CPU Util，CPU Load，磁盘IO和网络IO指标都很低，毫无性能压力的服务器指标，有啥好看的（其实是有指标异常的）。别急，在毫无波澜的服务器上，mysql实例内部已经翻山倒海了，请看下图：有接近三万的活动连接，所有的连接都是执行非常简单的单条insert语句。但是，这些sql语句，都已经执行了十多分钟还未结束。而且再过不到十分钟，mysql实例将实现自我涅槃（自动重启）。某日，业务开发突然反馈，应用出现大量无法建立新连接的报错。登录上mysql实例发现，最大3万的连接数，已经满的不能再满了。实例中全部都是活动连接
大量执行简单的单行insert
没有行/表锁阻塞
如同上图中所示，所有的sql语句执行TIME都在增大，就是不见有sql执行完成。和业务开发沟通后，kill掉所有的sql。然，悲剧发生了，kill也卡住了，所有的连接都显示Killed状态，依旧顽固的赖在实例里，如下图：整个过程中，服务器性能指标都很正常，所以服务器层面没有任何报警出现。简单来讲，就是服务器说我很轻松，很happy， mysql实例说AWSL。瞬间暴涨到3万个并发连接，一定是应用突然进来了大量访问，开发也确认了，业务量是有所上升。我司针对java和C#开发了sdk和连接中间件，但是这个业务的应用是C写的，无法接入中间件，所以数据库的连接数会一直涨到满。
普通的ssd服务器，几万qps的插入肯定是没问题的，这么好的机器（112核CPU，256G内存，磁盘PCIE SSD），几十万的qps应该不在话下吧，好歹PCIE SSD比普通ssd磁盘至少有10倍的读写提升，难道就撑不住这些业务量吗？等等，这里存在一个思维误区，升级服务器配置是否能提高数据库的吞吐量？对于oracle来说，是毋容置疑的，但对于mysql，是不是这样的呢？InnoDB: Error: semaphore wait has lasted> 600 secondsSrv0srv.cc文件中1750行（mysql版本为5.6.31），后台线程会循环检查原子锁的等待时间，如果连续10次检测超时，则自杀。其实看到semaphore，基本上就知道原子锁是脱不了干系了。这个方案先要测一下，否则不能直接在线上执行，心脏没有那么大。没想到很容易就复现了，写了一个30进程3万线程的并发insert程序，成功复现了上面的问题。直接排除掉服务器硬件和操作系统因素。然后测试了一下这个解决方案，并没有啥用。看来事情没那么简单，要深入调查一下了。在实例重启前，mysql将大量的连接信息dump到了error log中，类似这样的：由于输出的日志实在太多（大概1个G），人工去统计显然不现实，只好写个脚本进行分析统计。然，error log里面只是采样数据，真实情况是不是这样的呢？真正原因是否能采样出来？是否能用其他手段，来印证采样的准确性呢？想来想去，看来要对mysql的insert源码做全链路分析了。那就要用到大名鼎鼎的systemtap了（也就是centos版的dtrace，研究oracle内核的最喜欢用dtrace了）。思路是，将mysql的insert的源码流程，划分成多个子模块，跑压力的同时用systemtap来统计模块耗时。然后拆分其中耗时的模块，进行更细粒度的耗时统计。循环几次，最终找到最耗时的几个函数。按照上面的思路，多次测试过后发现，不仅上面两个锁很耗时，还有release_lock方法耗时也非常厉害，这个是MetaData的原子锁。show engine innodb status在show engine innodb status中的SEMAPHORES模块，可以看到innodb的回旋锁的概况。但它仅仅是概况，显然信息是远远不够的。实际上，mysql对原子锁的信息采集和记录实在太少了，你根本就无法知道当前连接卡在了哪里，它在等待什么。相比之下，oracle就做的非常完善，以前通过oracle详细的event信息，顺利找到了在AMM下shared pool把buffercache内存给挤光了的故障原因。原子锁的竞争，让mysql连接都卡住，甚至kill不掉（kill也需要竞争原子锁），这个很容易理解，为啥服务器性能指标没有任何异常呢？其实是有异常，不过不在我们经常关注的那些指标上，而是在csw上，即上下文切换(Context Switch)。这个指标非常的高，有上下文切换，说明有线程切换，有线程切上来，就说明有线程切下去（休眠），这都是原子锁竞争的表现。这些都串起来了，但还差一点需要验证，是否大量的原子锁竞争，只会导致服务器的csw指标异常，而其他指标毫无波澜呢。这就需要自己动手写代码了，撸了一个简单的C，代码如下：void print_msg(char *ptr);pthread_mutex_t sum_mutex; pthread_t tids[NUM_THREADS]; pthread_mutex_init (&sum_mutex, NULL); for(i = 0; i < NUM_THREADS; i++) char *element_s=(char*)malloc(strlen(msg)+strlen(s)+1); sprintf(element_s,"%.*s%.*s",strlen(msg),msg,strlen(s),s); int ret = pthread_create(&tids[i],NULL, (void *)(&print_msg),(void *)element_s); printf("pthread_create error: error_code=%d\n", ret); pthread_mutex_destroy(&sum_mutex);void print_msg(char *ptr)for(i = 0; i < 10000000; i++) printf("---Thread ID: %x---\n",id); pthread_mutex_lock (&sum_mutex); pthread_mutex_unlock (&sum_mutex);
这段代码起了3万个线程，一起竞争原子锁。运行结果很喜人，除了csw指标上去了，其他指标基本没变化。这就解释了上面mysql实例内部原子锁大量竞争阻塞，而服务器没有任何报警的原因。之所以会有这么大量的原子锁竞争，是竞争者太多了，那么，减少最大连接数，是不是就能减少原子锁竞争呢？将max_connection改成1.5万，跑了下压力，发现insert语句依旧卡着，说明竞争依旧很激烈，但是，不再出现自动重启的现象了，而且当应用停止后，这些残留的sql会慢慢的执行完。
将max_connection改成3000，发现insert语句基本上2分钟执行完，竞争已经减轻很多了。
将max_connection改成300，语句执行毫无压力，但明显没有发挥出服务器的真正实力。
看来减少竞争者是有效果的。这个很容易理解，有竞争者就有等待者，就会存在等待者队列。这些等待者要么直接休眠，要么回旋后休眠，最后会被锁释放者一起唤醒，再进行竞争。想象一下，几万个人同时抢一个卫生间，是不是很崩溃。。。但是连接数少了，服务器性能没有充足发挥，连接数多了，并发控制成本就越来越大。如同风阻一样，车速低的时候可以忽视风阻，随着车速增加，大量的能量消耗在了对付风阻上了，而当到达了音速，音障横空出世。这就是为什么，随着mysql并发数增加，性能呈上升趋势，而到达一定的并发数后，性能就开始下降。如何选取合适的连接数，在并发和并发控制中，发挥服务器最优性能，这简直就是个平衡的艺术。而且不同服务器的最优并发连接数肯定是不一样的，这就需要写一个通用的自动化测试脚本了。不过，先对mysql5.7和mysql8.0进行测试，看看这两个版本是否也会出现同样的问题。首先对mariadb10.0.24测试一下，测试原因是我司大量的使用这个版本的mysql。结果和mysql5.6.31一样，3万个并发毫无悬念的就挂了。接下来是mysql5.7.30版本的压测，3万个并发打上去，服务器的CPU和磁盘IO都有一定的活动，并未出现完全卡死的情况，说明有一小部分的操作还能够进行下去。但是明显远未达到服务器的最大资源性能。把连接打到6万，CPU还在活动，但是磁盘IO几乎停滞了，如下图：这里有个小插曲，并发达到3万2千多的时候，死活就打不上去了。操作系统的ulimit，实例的innodb_buffer_pool_instances和innodb_open_files都调过了，没有任何作用，只有调整了操作系统的max_map_count，才能够成功突破这个限制，顺利达到6万并发。
最后是mysql8.0.21版本的压测，6万并发，服务器的CPU和磁盘IO还有一定的活动，未出现完全卡死的情况。这里还有个小插曲，当并发打到6万5千多并发后，就上不去了，如果强行打上去，会报Not enough resources to complete lock request的错误，应该是哪里设置了65535的限制，以后翻源码瞅瞅。
由上面的测试可以发现，mysql随着版本的增加，高并发的性能越来越好，其关键就在对原子锁的优化上，这样才能充分利用服务器资源，在服务器硬件扩展中，获得红利。
最后修改时间：2020-09-09 09:36:34
文章转载自
谜团的瞭望塔，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
