如何解决MySQL中时间戳时区转换导致CPU%sy高的问题

1.问题显示

以下是出现问题时的系统负载如下：

我们可以看到40.4%sy是高的标志系统调用负载。然后朋友收集了perf如下：

接下来朋友给我收集了pstack，发现大量线程处于如下状态：

线程 38 (线程 0x7fe57a86f700 (LWP 67268)): #0 0x0000003dee4f82ce in __lll_lock_wait_private () from /lib64/libc.so.6#1 0x0000003dee49df8d in _L_lock_2163 () from /lib64/libc. so.6#2 0x0000003dee49dd47在 /lib64/libc.so.6#3 0x0000000000 7c0 的 __tz_convert () 中2e7 在 Time_zone_system::gmt_sec_to_TIME(st_mysql_time* , long) const ()#4 0x0000000000811df6 在 Field_timestampf::get_date_internal(st_mysql_time*) ()#5 0x00000000000809ea9 在 Field_temporal_with_date::val_date_temporal() ( )#6 0x00000000005f43cc in get_datetime_value(THD*,项目***，项目**，iteg*，bool*）（）＃7 0x00000000005e7ba7 in arg_comparator :: compare_dateTime（）（）＃8 0x00000 0000050005EEF4E IN INTEM_FUNC_GT IN IN IMET_FUNC_GT中, st_join_table*) ()#10 0x0000000000700e7e in sub_select(JOIN*, st_join_table*, bool) ()#11 0x000000000 06fecc1 in JOIN::exec() ()

我们可以注意到 __tz_convert 是时区转换。

2、时间戳简要说明

timestamp：占用4个字节，内部实现的是自新纪元时间（1970-01-01 00:00:00）以来的秒数，因此当这种格式显示给用户时，需要进行时区转换需要获取正确的数据。我们通过访问ibd文件来查看一下内部的表示方法，使用我的两个工具innodb和bcview，具体可以参考https://www.jianshu.com/p/719f1bbb21e8。

时间戳的内部表示

创建测试表

mysql> show '%time_zone%';+-- ----------------+--------+|变量名 |值 |+-------- ----------+--------+|系统时区 |国家标准时间||时区 | +08:00 |+------------- ---+--------+mysql> 创建表 tmm(dt 时间戳);查询正常，0 行受影响 (0.04 sec)mysql> insert into tmm values('2023-01-01 01:01: 01');查询正常，1行受影响（0.00秒）

让我们检查一下内部表示，如下所示：

[root@gp1 test]# ./bcview tmm.ibd 16 125 25|grep 00000003当前块:00000003--偏移量:00125--cnt字节:25--数据为:000001ac3502000000070d52c80000002f01105c2a 4b4d0000

排列如下：

000001ac3502：rowid

000000070d52:trx id

c80000002f0110: roll ptr

5c2a4b4d:timestamp类型的实际数据十进制为1546275661

我们使用Linux命令如下：

[root@gp1 ~]# date -d @1546275661Tue Jan 1 01:01:01 CST 2023

因为我的Linux也是CST+8时区，所以这里的数据也是和MySQL中显示的一样。我们来调整时区，看看值如下：

mysql> set time_zone='+06:00';Query OK, 0 rows受影响(0.00 sec)mysql> select * from tmm ;+- --------------------------------+| dt                                                                                      12-31 23:01:01 |+------------------------+ 组中 1 行（0.01 秒） )

你可以观看此处 我减去了 2 小时，因为我的时区从 +8 更改为 +6。

3.时间戳转换

在执行新纪元时间（将自 1970-01-01 00:00:00 以来的秒数转换为实际时间时）时，MySQL ha根据参数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。

注意，这个转换操作需要对每一行满足条件的数据进行转换。

4.问题修复方案

我们从问题栈帧开始看来这个故障使用了Time_zone_system::gmt_sec_to_TIME函数进行conv版本，因此您可以考虑以下操作：

time_zone：设置为指定的时区，例如“+08:00”。这样就不会使用OS API进行转换，而是MySQL自己的内部实现会调用Time_zone_offset::gmt_sec_to_TIME函数。但需要注意的是，如果使用MySQL自己的实现，us%会加剧。

使用日期时间而不是时间戳。新版本的datetime为5个字节，只比timestamp多了1个字节。

5.修复前后sy%使用情况对比

据朋友介绍，他在上午11点左右完成了修改，将time_zone改为'+08:00'。修改前后CPU占用率对比如下：

修复前：

修复后：

time_zone='SYSTEM'转换堆栈框架

#0 Time_zone_system::gmt_sec_to_TIME (this=0x2e76948, tmp=0x7fffec0f3ff0, t=1546275661) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/tztime.cc:1092 #1 0x0000000000f6b65c in Time_zone::gmt_sec_to_TIME (this=0x2e76 948, tmp=0x7fffec0f3ff0, tv= ...) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/tztime.h:60# 2 0x0000000000f51643 在 Field_timestampf::get_date_internal (this=0x7ffe7ca66540, ltime=0x7fffec0f3ff0) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:6014#3 0x0000000000f4ff Field_temporal_with_date::val_str 中的 49 ( this=0x7ffe7ca66540、val_buffer=0x7fffec0f4370、val_ptr=0x7 fffec0f4370) 位于 /root/mysqlall/percona- server-locks-detail-5.7.22/sql/field.cc:5429#4 0x0000000000f11d7b 中 Field::val_str (this=0x 7ffe7ca66540 , str=0x7fffec0f4370) 位于 /root/mysqlall/percona-server-locks-detail-5.7。 22/sql/field.h:866#5  0x0000000000f4549d 在 Field::send_text (this=0x7ffe7ca66540，协议=0x7ffe7c001e88) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:1725#6 0x0000 0000014dfb82 在 Protocol_text::store (this=0x7ffe7c001e88，field=0x7ffe7ca66540) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql /protocol_classic.cc:1415#7 Item_field::send 中的 0x0000000000fb06c0 (this=0x 7ffe7c006ec0, protocol=0x7ffe7c001e88, buffer=0x7fffec0f4760) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item 。 cc:7801#8 0x000000000156b15c 在 THD::send_result_set_row (this=0x7ffe7c000b70 ， row_items=0x7ffe7c005d58) 位于 /root /mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:5026#9 0x Query_result_send 中的 0000000001565758 ::send_data (this=0x7ffe7c006e98, items=...) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2932#10 0x0000000001585490 in end_send (join=0x7ffe7c007078, qep_tab = 0x7ffe7c0078d0，end_of_records = false）位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc：2925#11 0x0000000001582059 中评估_join_record（join = 0x7ffe7c007078，qep_tab = 0x7ffe7c0 07758)

time_zone='+08:00' 转换堆栈帧

#0 Time_zone_offset::gmt_sec_to_TIME (this=0x6723d90, tmp=0x7fffec0f3ff0, t=1546275661) 位于 /root/mysqlall/percona-server-locks -detail-5.7.22/sql/tztime.cc:1418#1 0x0000000000f6b65c in Time_zone::gmt_sec_to_TIME (this=0x6723d90, tmp=0x7fffec0f3ff0, tv=...) at /root/mysqlall/percona-server-locks-detail -5.7.22/sql/tztime.h:60#2 0x0 000000000f51643 in Field_timestampf::get_date_internal (this=0x7ffe7ca66540, ltime=0x7fffec0f3ff0) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/ field.cc:6014#3 0x0000000000f4ff49 在 Field_temporal_with_date::val_str (this=0x7ffe7ca66540, val_buffer=0x7fffec0f4370, val_ptr=0x7fffec0f4370) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/ sql/field.cc： 5429#4 0x0000000000f11d7b 在 Field::val_str (this=0x7ffe7ca66540, str=0x7fffec0f4370) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.h:866#5 0x0000000000f4549d在领域： ：send_text（这= 0x7ffe7ca66540，协议= 0x7ffe7c001e88) 位于 /root/mysqlall/percona-server-locks-detail-5.7。 22/ sql/field.cc:1725#6 0x00000000014dfb82 在 Protocol_text::store (this=0x7ffe7c001e88, field=0x7ffe7ca66540) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol _classic.cc :1415# 7 0x0000000000fb06c0 in Item_field::send (this=0x7ffe7c006ec0,协议=0x7ffe7c001e88,缓冲区=0x7fffec0f4760)在/root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.cc:7801# 8 THD 中的 0x000000000156b15c: :send_result_set_row (this=0x7ffe7c000b70, row_items=0x7ffe7c005d58) 位于 /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:5026#9 0x000000000 156575 8 在 Query_result_send::send_data ( this=0x7ffe7c006e98，项目=...)位于/root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2932#10 0x0000000001585490 in end_send (join=0x7ffe7c007078，qep_tab=0x7ffe7c0078d0，结束_ of_records=false) 位于/root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925#11 0x0000000001582059 中的evaluate_join_record (join=0x7ffe7c007078, qep_tab=0x7ffe7c007758)

以上就是如何解决MySQL中时间戳时区转换导致CPU %sy高的问题。小编相信有些知识点在我们日常工作中可能会看到或者用到。希望您能从本文中了解更多信息。更多详情请关注行业资讯频道。