如何解決MySQL中timestamp時(shí)區(qū)轉(zhuǎn)換導(dǎo)致CPU %sy高的問(wèn)題

本篇文章給大家分享的是有關(guān)如何解決MySQL中timestamp時(shí)區(qū)轉(zhuǎn)換導(dǎo)致CPU %sy高的問(wèn)題，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

一、問(wèn)題展示

下面是問(wèn)題當(dāng)時(shí)的系統(tǒng)負(fù)載如下：

我們可以看到40.4%sy 正是系統(tǒng)調(diào)用負(fù)載較高的表現(xiàn)，隨即朋友采集了perf如下：

接下來(lái)朋友采集了pstack給我，我發(fā)現(xiàn)大量的線程處于如下狀態(tài)下：

Thread 38 (Thread 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 in __tz_convert () from /lib64/libc.so.6
#3  0x00000000007c02e7 in Time_zone_system::gmt_sec_to_TIME(st_mysql_time*, long) const ()
#4  0x0000000000811df6 in Field_timestampf::get_date_internal(st_mysql_time*) ()
#5  0x0000000000809ea9 in Field_temporal_with_date::val_date_temporal() ()
#6  0x00000000005f43cc in get_datetime_value(THD*, Item***, Item**, Item*, bool*) ()
#7  0x00000000005e7ba7 in Arg_comparator::compare_datetime() ()
#8  0x00000000005eef4e in Item_func_gt::val_int() ()
#9  0x00000000006fc6ab 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 這正是時(shí)區(qū)轉(zhuǎn)換的證據(jù)。

二、關(guān)于timestamp簡(jiǎn)要說(shuō)明

timestamp：占用4字節(jié)，內(nèi)部實(shí)現(xiàn)是新紀(jì)元時(shí)間（1970-01-01 00:00:00）以來(lái)的秒，那么這種格式在展示給用戶的時(shí)候就需要做必要的時(shí)區(qū)轉(zhuǎn)換才能得到正確數(shù)據(jù)。下面我們通過(guò)訪問(wèn)ibd文件來(lái)查看一下內(nèi)部表示方法，使用到了我的兩個(gè)工具innodb和bcview，詳細(xì)參考 https://www.jianshu.com/p/719f1bbb21e8。

timestamp的內(nèi)部表示

建立一個(gè)測(cè)試表

mysql> 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)

我們來(lái)查看一下內(nèi)部表示如下：

[root@gp1 test]# ./bcview tmm.ibd 16 125 25|grep 00000003
current block:00000003--Offset:00125--cnt bytes:25--data is:000001ac3502000000070d52c80000002f01105c2a4b4d0000

整理一下如下：

• 000001ac3502：rowid

• 000000070d52：trx id

• c80000002f0110：roll ptr

• 5c2a4b4d：timestamp類型的實(shí)際數(shù)據(jù)十進(jìn)制為1546275661

我們使用Linux命令如下：

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

因?yàn)槲业腖inux也是CST +8時(shí)區(qū)這里數(shù)據(jù)也和MySQL中顯示一樣。下面我們調(diào)整一下時(shí)區(qū)再來(lái)看看取值如下：

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個(gè)小時(shí)，因?yàn)槲业臅r(shí)區(qū)從+8變?yōu)榱?6。

三、timestap轉(zhuǎn)換

在進(jìn)行新紀(jì)元時(shí)間（1970-01-01 00:00:00）以來(lái)的秒到實(shí)際時(shí)間之間轉(zhuǎn)換的時(shí)候MySQL根據(jù)參數(shù)time_zone的設(shè)置有兩種選擇：

• time_zone：設(shè)置為SYSTEM的話，使用sys_time_zone獲取的OS會(huì)話時(shí)區(qū)，同時(shí)使用OS API進(jìn)行轉(zhuǎn)換。對(duì)應(yīng)轉(zhuǎn)換函數(shù) Time_zone_system::gmt_sec_to_TIME

• time_zone：設(shè)置為實(shí)際的時(shí)區(qū)的話，比如‘+08:00’，那么使用使用MySQL自己的方法進(jìn)行轉(zhuǎn)換。對(duì)應(yīng)轉(zhuǎn)換函數(shù) Time_zone_offset::gmt_sec_to_TIME

實(shí)際上Time_zone_system和Time_zone_offset均繼承于Time_zone類，并且實(shí)現(xiàn)了Time_zone類的虛函數(shù)進(jìn)行了重寫，因此上層調(diào)用都是Time_zone::gmt_sec_to_TIME。

注意這種轉(zhuǎn)換操作是每行符合條件的數(shù)據(jù)都需要轉(zhuǎn)換的。

四、問(wèn)題修復(fù)方案

我們從問(wèn)題棧幀來(lái)看這個(gè)故障使用的是 Time_zone_system::gmt_sec_to_TIME 函數(shù)進(jìn)行轉(zhuǎn)換的，因此可以考慮如下：

• time_zone：設(shè)置為指定的時(shí)區(qū)，比如‘+08:00’。這樣就不會(huì)使用OS API進(jìn)行轉(zhuǎn)換了，而轉(zhuǎn)為MySQL自己的內(nèi)部實(shí)現(xiàn) 調(diào)用 Time_zone_offset::gmt_sec_to_TIME函數(shù)。但是需要注意的是，如果使用MySQL自己的實(shí)現(xiàn)那么us%會(huì)加劇。

• 使用datetime代替timestamp，新版本datetime為5個(gè)字節(jié)，只比timestamp多一個(gè)字節(jié)。

五、修復(fù)前后sy%使用量對(duì)比

據(jù)朋友說(shuō)他大概在上午11點(diǎn)多完成了修改，做的方式是將 time_zone修改為‘+08:00’，下面展示修改前后CPU使用率的對(duì)比：

修復(fù)前：

修復(fù)后：

六、備用棧幀

• time_zone=‘SYSTEM’轉(zhuǎn)換棧幀

  #0  Time_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
  #1  0x0000000000f6b65c in Time_zone::gmt_sec_to_TIME (this=0x2e76948, tmp=0x7fffec0f3ff0, tv=...) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/tztime.h:60
  #2  0x0000000000f51643 in Field_timestampf::get_date_internal (this=0x7ffe7ca66540, ltime=0x7fffec0f3ff0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:6014
  #3  0x0000000000f4ff49 in Field_temporal_with_date::val_str (this=0x7ffe7ca66540, val_buffer=0x7fffec0f4370, val_ptr=0x7fffec0f4370)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:5429
  #4  0x0000000000f11d7b in Field::val_str (this=0x7ffe7ca66540, str=0x7fffec0f4370) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.h:866
  #5  0x0000000000f4549d in Field::send_text (this=0x7ffe7ca66540, protocol=0x7ffe7c001e88) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:1725
  #6  0x00000000014dfb82 in Protocol_text::store (this=0x7ffe7c001e88, field=0x7ffe7ca66540)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:1415
  #7  0x0000000000fb06c0 in Item_field::send (this=0x7ffe7c006ec0, protocol=0x7ffe7c001e88, buffer=0x7fffec0f4760)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.cc:7801
  #8  0x000000000156b15c in THD::send_result_set_row (this=0x7ffe7c000b70, row_items=0x7ffe7c005d58)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:5026
  #9  0x0000000001565758 in Query_result_send::send_data (this=0x7ffe7c006e98, items=...) at /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)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
  #11 0x0000000001582059 in evaluate_join_record (join=0x7ffe7c007078, qep_tab=0x7ffe7c007758)

• time_zone=‘+08:00’轉(zhuǎn)換棧幀

#0  Time_zone_offset::gmt_sec_to_TIME (this=0x6723d90, tmp=0x7fffec0f3ff0, t=1546275661) at /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  0x0000000000f51643 in Field_timestampf::get_date_internal (this=0x7ffe7ca66540, ltime=0x7fffec0f3ff0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:6014
#3  0x0000000000f4ff49 in Field_temporal_with_date::val_str (this=0x7ffe7ca66540, val_buffer=0x7fffec0f4370, val_ptr=0x7fffec0f4370)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:5429
#4  0x0000000000f11d7b in Field::val_str (this=0x7ffe7ca66540, str=0x7fffec0f4370) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.h:866
#5  0x0000000000f4549d in Field::send_text (this=0x7ffe7ca66540, protocol=0x7ffe7c001e88) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/field.cc:1725
#6  0x00000000014dfb82 in Protocol_text::store (this=0x7ffe7c001e88, field=0x7ffe7ca66540)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/protocol_classic.cc:1415
#7  0x0000000000fb06c0 in Item_field::send (this=0x7ffe7c006ec0, protocol=0x7ffe7c001e88, buffer=0x7fffec0f4760)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.cc:7801
#8  0x000000000156b15c in THD::send_result_set_row (this=0x7ffe7c000b70, row_items=0x7ffe7c005d58)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_class.cc:5026
#9  0x0000000001565758 in Query_result_send::send_data (this=0x7ffe7c006e98, items=...) at /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)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
#11 0x0000000001582059 in evaluate_join_record (join=0x7ffe7c007078, qep_tab=0x7ffe7c007758)

以上就是如何解決MySQL中timestamp時(shí)區(qū)轉(zhuǎn)換導(dǎo)致CPU %sy高的問(wèn)題，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。