导致|深入理解 MySQL导致 CPU %sy 高的问题-爱可生 zone|cpu|理解|mysql|爱可生

文章插图
一、问题展示
下面是问题当时的系统负载如下：
我们可以看到 40.4%sy 正是系统调用负载较高的表现，随即朋友采集了 perf 如下：
接下来朋友采集了 pstack 给我，我发现大量的线程处于如下状态下：
Thread 38 (Thread 0x7fe57a86f700 (LWP 67268)):
#00x0000003dee4f82ce in __lll_lock_wait_private () from /lib64/libc.so.6
#10x0000003dee49df8d in _L_lock_2163 () from /lib64/libc.so.6
#20x0000003dee49dd47 in __tz_convert () from /lib64/libc.so.6
#30x00000000007c02e7 in Time_zone_system::gmt_sec_to_TIME(st_mysql_time*, long) const ()
#40x0000000000811df6 in Field_timestampf::get_date_internal(st_mysql_time*) ()
#50x0000000000809ea9 in Field_temporal_with_date::val_date_temporal() ()
#60x00000000005f43cc in get_datetime_value(THD*, Item***, Item**, Item*, bool*) ()
#70x00000000005e7ba7 in Arg_comparator::compare_datetime() ()
#80x00000000005eef4e in Item_func_gt::val_int() ()
#90x00000000006fc6ab in evaluate_join_record(JOIN*, st_join_table*) ()
#10 0x0000000000700e7e in sub_select(JOIN*, st_join_table*, bool) ()
#11 0x00000000006fecc1 in JOIN::exec() ()
我们可以注意一下 __tz_convert 这正是时区转换的证据。
二、关于 timestamp 简要说明
timestamp：占用 4 字节，内部实现是新纪元时间（1970-01-01 00:00:00）以来的秒，那么这种格式在展示给用户的时候就需要做必要的时区转换才能得到正确数据。下面我们通过访问 ibd 文件来查看一下内部表示方法，使用到了我的两个工具 innodb 和 bcview。
timestamp 的内部表示
建立一个测试表
ysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name| Value|
+------------------+--------+
| system_time_zone | CST|
| time_zone| +08:00 |
+------------------+--------+
mysql> create table tmm(dt timestamp);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tmm values('2019-01-01 01:01:01');
Query OK, 1 row affected (0.00 sec)
我们来查看一下内部表示如下：
[root@gp1 test]# ./bcview tmm.ibd 16 125 25|grep 00000003current block:00000003--Offset:00125--cnt bytes:25--data is:000001ac3502000000070d52c80000002f01105c2a4b4d0000
整理一下如下：
000001ac3502：rowid
000000070d52：trx id
c80000002f0110：roll ptr
5c2a4b4d：timestamp 类型的实际数据十进制为 1546275661
我们使用 Linux 命令如下：
[root@gp1 ~]# date -d @1546275661Tue Jan1 01:01:01 CST 2019
因为我的 Linux 也是 CST +8 时区这里数据也和 MySQL 中显示一样。下面我们调整一下时区再来看看取值如下：
mysql> set time_zone='+06:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from tmm;+---------------------+| dt|+---------------------+| 2018-12-31 23:01:01 |+---------------------+1 row in set (0.01 sec)
这里可以看到减去了 2 个小时，因为我的时区从 +8 变为了 +6。
三、timestap 转换
在进行新纪元时间（1970-01-01 00:00:00）以来的秒到实际时间之间转换的时候 MySQL 根据参数 time_zone 的设置有两种选择：
time_zone 设置为 SYSTEM 的话：使用 sys_time_zone 获取的 OS 会话时区，同时使用 OS API 进行转换。对应转换函数 Time_zone_system::gmt_sec_to_TIME
time_zone 设置为实际的时区的话：比如 ‘+08:00’，那么使用使用 MySQL 自己的方法进行转换。对应转换函数 Time_zone_offset::gmt_sec_to_TIME
实际上 Time_zone_system 和 Time_zone_offset 均继承于 Time_zone 类，并且实现了 Time_zone 类的虚函数进行了重写，因此上层调用都是 Time_zone::gmt_sec_to_TIME。
注意这种转换操作是每行符合条件的数据都需要转换的。
四、问题修复方案
我们从问题栈帧来看这个故障使用的是 Time_zone_system::gmt_sec_to_TIME 函数进行转换的，因此可以考虑如下：
time_zone：设置为指定的时区，比如 ‘+08:00’。这样就不会使用 OS API 进行转换了，而转为 MySQL 自己的内部实现调用 Time_zone_offset::gmt_sec_to_TIME 函数。但是需要注意的是，如果使用 MySQL 自己的实现那么 us% 会加剧。
使用 datetime 代替 timestamp，新版本 datetime 为 5 个字节，只比 timestamp 多一个字节。
五、修复前后
sy% 使用量对比据朋友说他大概在上午 11 点多完成了修改，做的方式是将 time_zone 修改为 ‘+08:00’，下面展示修改前后 CPU 使用率的对比：
修复前：

文章插图
修复后：

文章插图
六、备用栈帧
time_zone=‘SYSTEM’转换栈帧
#0Time_zone_system::gmt_sec_to_TIME (this=0x2e76948, tmp=0x7fffec0f3ff0, t=1546275661) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/tztime.cc:1092