春林，2017年加入美团，主要负责MySQL运维开发和优化工作。
MySQL得益于其开源属性、成熟的商业运作、良好的社区运营以及功能的不断迭代与完善，已经成为互联网关系型数据库的标配。可以说，X86服务器、Linux作为基础设施，跟MySQL一起构建了互联网数据存储服务的基石，三者相辅相成。
本文将分享一个工作中的实践案例：因Intel PAUSE指令周期的迭代，引发了MySQL的性能瓶颈，美团MySQL DBA团队如何基于这三者来一步步进行分析、定位和优化。希望这些思路能对大家有所启发。
一、背景
在2017年，Intel发布了新一代的服务器平台Purley，并将Intel Xeon Scalable Processor（至强可扩展处理器）重新划分为：Platinum（铂金）、Gold（金）、Silver（银）、Broze（铜）等四个等级。产品定位和框架也变得更加清晰。
因美团线上海量数据交易和存储等后端服务依赖大量高性能服务器的支撑。随着线上部分Grantly平台E系列服务器生命周期的临近，以及产品本身的发展和迭代。2019年开始，RDS（关系型数据库服务）后端存储（MySQL）开始大量上线Purley平台的Skylake CPU服务器，其中包含Silver 4110等。
Silver 4110相比上一代E5-2620 V4，支持更高的内存频率、更多的内存通道、更大的L2 Cache、更快的总线传输速率等。Intel官方数据显示Silver 4110的性能比上一代E5-2620 V4提升了10%。
然而，随着线上Skylake服务器数量的增加，以及越来越多的业务接入。美团MySQL DBA团队发现部分MySQL实例性能与预期并不相符，有时甚至出现较大程度的下降。经过持续的性能问题分析，我们定位到Skylake服务器存在性能瓶颈：
CPU负载相对较高。
TPS等吞吐量下降。
接下来，我们将从Intel CPU、ut_delay函数、PAUSE指令三方面入手，进行剖析定位，并探索相关优化方案。
二、性能问题分析
首先，基于上述两代平台的CPU（Grantly和Purley），通过基准测试，横向对比在不同OS下的性能表现。
通过基准测试数据，总结如下：
在oltp_write_only（只写）的场景下Purley 4110的性能下降较为明显。
同为Purley 4110，CentOS 7比CentOS 6 oltp_write_only（只写）性能有提升。
我们通过二维折线图，来展示性能之间的差异：
在上图中，同为Purley 4110，CentOS 7比CentOS 6性能有提升。具体提升原因，因不涉及本文重点内容，所以不在这里详细展开了。
红帽官网Release Notes显示，从内核3.10.0-229开始，引入了新的加锁机制，MCS锁。可以降低spinlock的开销，从而更高效地运行。普通spinlock在多CPU Core下，同时只能有一个CPU获取变量，并自旋，而缓存一致性协议为了保证数据的正确，会对所有CPU Cache Line状态、数据，同步、失效等操作，导致性能下降。
而MSC锁实现每个CPU都有自己的“spinlock”本地变量，只在本地自旋。避免Cache Line同步等，从而提升了相关性能。不过，社区对于spinlock的优化争议还是比较大的，后续又有大牛基于MSC实现了qspinlock，并在4.x的版本上patch了。具体实现可以参看：MCS locks and qspinlocks。
在大致了解CentOS 7性能的迭代后，接下来我们深入分析一下Skylake CPU 4110导致性能下降的缘由。
三、CPU性能跟踪
具体定位4110性能瓶颈，分如下几步:
首先，通过perf top来跟踪一下Linux CPU性能开销。
然后，通过perf record记录函数CPU周期的消耗占比。
最后，通过火焰图来验证定位热点函数。
可以看到，其中占CPU消耗占比较大为：ut_delay函数。
我们继续深挖一下函数链调用关系：
# Children Self Command Shared Object Symbol
# ........ ........ ....... ................... ..................................................................................................................................................................................
#
93.54% 0.00% mysqld libpthread-2.17.so [.] start_thread
|
---start_thread
|
|--77.07%--pfs_spawn_thread
| |
| --77.05%--handle_connection
| |
| --76.97%--do_command
| |
| |--74.30%--dispatch_command
| | |
| | |--71.16%--mysqld_stmt_execute
| | | |
| | | --70.74%--Prepared_statement::execute_loop
| | | |
| | | |--69.53%--Prepared_statement::execute
| | | | |
| | | | |--67.90%--mysql_execute_command
| | | | | |
| | | | | |--23.43%--trans_commit_stmt
| | | | | | |
| | | | | | --23.30%--ha_commit_trans
| | | | | | |
| | | | | | |--18.86%--MYSQL_BIN_LOG::commit
| | | | | | | |
| | | | | | | --18.18%--MYSQL_BIN_LOG::ordered_commit
| | | | | | | |
| | | | | | | |--8.02%--MYSQL_BIN_LOG::change_stage
| | | | | | | | |
| | | | | | | | |--2.35%--__lll_unlock_wake
| | | | | | | | | |
| | | | | | | | | --2.24%--system_call_fastpath
| | | | | | | | | |
| | | | | | | | | --2.24%--sys_futex
| | | | | | | | | |
| | | | | | | | | --2.23%--do_futex
| | | | | | | | | |
| | | | | | | | | --2.14%--futex_wake
| | | | | | | | | |
| | | | | | | | | --1.38%--wake_up_q
| | | | | | | | | |
| | | | | | | | | --1.33%--try_to_wake_up
...
滑动查看完整代码
将上述调用通过火焰图进行直观展示：
现在基本可以确定，所有的函数调用，最后大部分的消耗都在ut_delay上。
1)MySQL ut_delay实现
接下来，我们继续看一下MySQL源码中ut_delay函数的功能：
/*************************************************************//**
Runs an idle loop on CPU. The argument gives the desired delay
in microseconds on 100 MHz Pentium + Visual C++.
@return dummy value */
ulint
ut_delay(
/*=====*/
ulint delay) /*!< in: delay in microseconds on 100 MHz Pentium */
{
ulint i, j;
UT_LOW_PRIORITY_CPU();
j = 0;
for (i = 0; i < delay * 50; i++) {
j += i;
UT_RELAX_CPU();
}
UT_RESUME_PRIORITY_CPU();
return(j);
}
...
# define UT_RELAX_CPU() asm ("pause" )
# define UT_RELAX_CPU() __asm__ __volatile__ ("pause")
滑动查看完整代码
可以了解到，MySQL自旋会调用PAUSE指令，从而提升spin-wait loop的性能。
2)PAUSE指令周期的演变
我们可以看下Intel官网，也描述了在新平台架构PAUSE的改动：
上一代架构中（Grantly平台E系列）PAUSE的周期时长为10 cycles，新一代的Skylake架构中则为140 cycles。
如果程序中使用固定次数的PAUSE循环来实现一段时间的延迟，以此阻塞程序执行，可能引发非预期的延迟。
由于PAUSE周期增加，对于PAUSE敏感的应用会有一定的性能损失。
衡量程序执行性能的简化公式：
ExecutionTime(T)=InstructionCount∗TimePerCycle∗CPI
即：程序执行时间 = 程序总指令数 x 每CPU时钟周期时间 x 每指令执行所需平均时钟周期数。
MySQL内部自旋，就是通过固定次数的PAUSE循环实现。可知，PAUSE指令周期的增加，那么执行自旋的时间也会增加，即程序执行的时间也会相对增加，对系统整体的吞吐量就会有影响。
显然，Intel文档已说明不同平台、不同架构CPU PAUSE定义的周期是不一样的。
下面，我们通过一个测试用例来大致验证、对比一下新老架构CPU执行PAUSE的cycles：
#include <stdio.h>
#define TIMES 5
static inline unsigned long long rdtsc(void)
{
unsigned long low, high;
asm volatile("rdtsc" : "=a" (low), "=d" (high) );
return ((low) | (high) << 32);
}
void pause_test()
{
int i = 0;
for (i = 0; i < TIMES; i++) {
asm(
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"\
"pause\n"
::
:);
}
}
unsigned long pause_cycle()
{
unsigned long start, finish, elapsed;
start = rdtsc();
pause_test();
finish = rdtsc();
elapsed = finish - start;
printf("Pause的cycles约为:%ld\n", elapsed / 100);
return 0;
}
int main()
{
pause_cycle();
return 0;
}
滑动查看完整代码
其运行结果统计如下：
4110和5118 PAUSE周期较大，均为100多，它们属于Purley第一代架构：Skylake。
4210和5218 PAUSE相比前一代有提升，是因为它们同属Purley第二代架构：Cascadelake，该代CPU PAUSE指令有优化。
3)Intel 提升PAUSE猜想
Intel提高PAUSE指令周期的原因，推测可能是减少自旋锁冲突的概率，以及降低功耗；但反而导致PAUSE执行时间变长，降低了整体的吞吐量。
接下来，我们深入分析一下PAUSE指令导致MySQL写瓶颈的原因。
首先，通过MySQL 内部统计信息，查看一下InnoDB信号量监控数据：
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 153720
--Thread 139868617205504 has waited at row0row.cc line 1075 for 0.00 seconds the semaphore:
X-lock on RW-latch at 0x7f4298084250 created in file buf0buf.cc line 1425
a writer (thread id 139869284108032) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file not yet reserved line 0
Last time write locked in file /mnt/workspace/percona-server-5.7-redhat-binary-rocks-new/label_exp/min-centos-7-x64/test/rpmbuild/BUILD/percona-server-5.7.26-29/percona-server-5.7.26-29/storage/innobase/buf/buf0flu.cc line 1216
OS WAIT ARRAY INFO: signal count 441329
RW-shared spins 0, rounds 1498677, OS waits 111991
RW-excl spins 0, rounds 717200, OS waits 9012
RW-sx spins 47596, rounds 366136, OS waits 4100
Spin rounds per wait: 1498677.00 RW-shared, 717200.00 RW-excl, 7.69 RW-sx
滑动查看完整代码
可见写操作并阻塞在：storage/innobase/buf/buf0flu.cc第1216行调用上。
跟踪一下发生等待的源码：buf0flu.cc line 1216：
if (flush_type == BUF_FLUSH_LIST
&& is_uncompressed
&& !rw_lock_sx_lock_nowait(rw_lock, BUF_IO_WRITE)) { // 加锁前，判断锁冲突
if (!fsp_is_system_temporary(bpage->id.space())) {
/* avoiding deadlock possibility involves
doublewrite buffer, should flush it, because
it might hold the another block->lock. */
buf_dblwr_flush_buffered_writes(
buf_parallel_dblwr_partition(bpage,
flush_type));
} else {
buf_dblwr_sync_datafiles();
}
rw_lock_sx_lock_gen(rw_lock, BUF_IO_WRITE); // 加sx锁
}
...
#define rw_lock_sx_lock_nowait(M, P) \
rw_lock_sx_lock_low((M), (P), __FILE__, __LINE__)
...
rw_lock_sx_lock_func( // 加sx锁函数
/*=================*/
rw_lock_t* lock, /*!< in: pointer to rw-lock */
ulint pass, /*!< in: pass value; != 0, if the lock will
be passed to another thread to unlock */
const char* file_name,/*!< in: file name where lock requested */
ulint line) /*!< in: line where requested */
{
ulint i = 0;
sync_array_t* sync_arr;
ulint spin_count = 0;
uint64_t count_os_wait = 0;
ulint spin_wait_count = 0;
ut_ad(rw_lock_validate(lock));
ut_ad(!rw_lock_own(lock, RW_LOCK_S));
lock_loop:
if (rw_lock_sx_lock_low(lock, pass, file_name, line)) {
if (count_os_wait > 0) {
lock->count_os_wait +=
static_cast<uint32_t>(count_os_wait);
rw_lock_stats.rw_sx_os_wait_count.add(count_os_wait);
}
rw_lock_stats.rw_sx_spin_round_count.add(spin_count);
rw_lock_stats.rw_sx_spin_wait_count.add(spin_wait_count);
/* Locking succeeded */
return;
} else {
++spin_wait_count;
/* Spin waiting for the lock_word to become free */
os_rmb;
while (i < srv_n_spin_wait_rounds
&& lock->lock_word <= X_LOCK_HALF_DECR) {
if (srv_spin_wait_delay) {
ut_delay(ut_rnd_interval(
0, srv_spin_wait_delay)); // 加锁失败，调用ut_delay
}
i++;
}
spin_count += i;
if (i >= srv_n_spin_wait_rounds) {
os_thread_yield();
} else {
goto lock_loop;
}
...
ulong srv_n_spin_wait_rounds = 30;
ulong srv_spin_wait_delay = 6;
滑动查看完整代码
上述源码可知，MySQL锁等待是通过调用ut_delay做空循环实现的。
InnoDB层有三种锁：S（共享锁）、X（排他锁）和SX（共享排他锁）。SX与SX、X是互斥锁。加SX不会影响读，只会阻塞写。所以在大量写入操作时，会造成大量的锁等待，即大量的PAUSE指令。
分析到这里，我们总结一下影响吞吐量的两个因素：
自旋的时长，在MySQL5.7以及之前版本的源码定位为：spin_wait_delay * 50。
Intel CPU PAUSE的指令周期。
接下来，我们就从这两方面入手，评估优化空间以及效果。
四、针对PAUSE指令和spin参数优化与探索
1)MySQL 5.7 spin参数优化
我们可以基于现有MySQL版本、硬件等方面，来寻找优化点。
MySQL针对spin控制这块有个参数可以调整，根据参数特点进行相关优化。
①innodb_spin_wait_delay
innodb_spin_wait_delay的单位，是100MHZ的奔腾处理器处理1毫秒的时间，默认innodb_spin_wait_delay配置成6，表示最多在100MHZ的奔腾处理器上自旋6毫秒。
②innodb_sync_spin_loops
当innodb线程获取mutex资源而得不到满足时，会最多进行innodb_sync_spin_loops次尝试获取mutex资源。
其中innodb_spin_wait_delay参数对PAUSE运行时长是有影响的。针对此参数，我们进行调优测试。
同样，针对上述参数优化，我们通过基准测试来对比性能和效果：
可以总结为：
innodb_spin_wait_delay的调整对TPS、QPS 一定影响，其值趋于小，则MySQL性能有提升。反之，下降。
innodb_spin_wait_delay参数调整性能优化效果有限，性能提升的幅度还是无法满足线上业务需求。
1)spin_wait_pause_multiplier 移植
针对Skylake CPU，PAUSE造成的吞吐量下降，我们对MySQL 5.7 spin控制参数innodb_spin_wait_delay的调优并未取得明显效果。
于是，我们将目光投向了MySQL 8.0的新特性：MySQL 8.0 针对PAUSE，源码中新增了spin_wait_pause_multiplier参数，来替换之前写死的循环次数。
2）spin_wait_pause_multiplier实现
MySQL 8.0源码中，之前循环50次的逻辑修改成了可以调整循环次数的参数：spin_wait_pause_multiplier。
ulint ut_delay(ulint delay) {
ulint i, j;
/* We don't expect overflow here, as ut::spin_wait_pause_multiplier is limited
to 100, and values of delay are not larger than @@innodb_spin_wait_delay
which is limited by 1 000. Anyway, in case an overflow happened, the program
would still work (as iterations is unsigned). */
const ulint iterations = delay * ut::spin_wait_pause_multiplier;
UT_LOW_PRIORITY_CPU();
j = 0;
for (i = 0; i < iterations; i++) {
j += i;
UT_RELAX_CPU();
}
UT_RESUME_PRIORITY_CPU();
return (j);
}
...
namespace ut {
ulong spin_wait_pause_multiplier = 50;
}
滑动查看完整代码
3）移植spin_wait_pause_multiplier patch优化
既然MySQL 8.0参数spin_wait_pause_multiplier可以控制PAUSE执行的时长，那么就可以减少该值，从而降低整体PAUSE影响。
了解MySQL 8.0相关代码后，我们将该patch移植到线上的稳定版本：
MySQ >select version();
+------------------+
| version() |
+------------------+
| 5.7.26-29-mt-log |
+------------------+
1 row in set (0.00 sec)
MySQL>show global variables like '%spin%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 5 |
| innodb_sync_spin_loops | 30 |
+-----------------------------------+-------+
3 rows in set (0.00 sec)
滑动查看完整代码
由上述可知，Silver 4110的PAUSE cycles是E5-2620 v4的14倍左右。基于此，将innodb_spin_wait_pause_multiplier值调整为默认值的1/14，取稍大值：5。即将该参数由原默认的50调整为5。
最后，还是通过二维折线图来对比该patch调优后的基准测试数据：
Silver 4110移植spin_wait_pause_multiplier patch，并调整优化后，4110（patch）性能有了较大的提升。
Silver 4110（patch） 相对调优innodb_spin_wait_delay性能上更优。
Silver 4110（patch）并发线程大于64的只写场景，性能略低于E5-2620 V4 ，其他均优。
按照真实的线上读写比例，4110（patch）可以将吞吐量恢复到原先的性能水平。
上述章节中，我们测出Cascadelake CPU PAUSE周期下降了。在跟Intel技术专家确认后得知：从Purley的第二代产品Cascadelake开始，Intel将PAUSE的指令周期降低到了44。（估计Intel也发现了第一代增加PAUSE周期后的性能瓶颈问题。）
我们针对第二代CPU产品继续做基准测试，来看一下性能表现：
接着用perf diff来对比一下4110和4210在ut_delay上的开销：
可以看到4210比4110占比下降了8%。
由于PAUSE指令周期还是数倍于E5系列CPU，4210在高负载下，PAUSE的开销对MySQL吞吐量还是有较大的影响。而在128并发线程以下，性能相比4110有了较大的提升。按理，可以满足线上业务需求（该测试结果跟移植spin_wait_pause_multiplier patch性能测试数据曲线一致）。
五、总结
最后针对本篇内容，我们可以做个简单的总结：
Intel在新平台CPU产品调大了PAUSE指令周期，在高并发spinlock竞争激烈场景下，可能会造成程序性能较大损耗（特别是执行固定PAUSE次数的程序）。
针对Skylake架构CPU（比如：4110等）PAUSE指令周期较长引起性能问题的优化方法如下：
将MySQL 8.0 innodb_spin_wait_pause_multiplier patch移植到线上稳定版本（或升级到MySQL 8.0），通过降低PAUSE执行时长，来提升吞吐量。
如果是OS为CentOS 6，可以升级到CentOS 7，CentOS 7本身spinlock优化，对MySQL性能也有一定提升。
最简单、直接的方法可以替换为Cascadelake架构CPU。
针对Cascadelake架构CPU，由于Intel本身在PAUSE周期已经优化，性能上已经做了修复。当然也可以采用上述优化方案，让性能提升一个台阶。
《All in Cloud 时代，下一代云原生数据库技术与趋势》阿里巴巴集团副总裁/达摩院首席数据库科学家 李飞飞（飞刀）
《AI和云原生时代的数据库进化之路》腾讯数据库产品中心总经理 林晓斌（丁奇）
《ICBC的MySQL探索之路》工商银行软件开发中心 魏亚东
《民生银行在SQL审核方面的探索和实践》民生银行资深数据库专家 李宁宁
《OceanBase分布式数据库在西安银行的落地和实践》蚂蚁金服P9资深专家/OceanBase核心负责人 蒋志勇
《金融行业MySQL高可用实践》爱可生技术总监 明溪源
如果字段的最大可能长度超过255字节，那么长度值可能…
只能说作者太用心了，优秀
感谢详解
一般干个7-8年（即30岁左右），能做到年入40w-50w；有…
230721