Shulou(Shulou.com)05/31 Report--

This article is about how to solve the problem of high CPU% sy caused by timestamp time zone conversion in MySQL. I think it is very practical, so I share it with you. I hope you can get something after reading this article.

I. problem presentation

The following is the problem: the system load at that time is as follows:

We can see that 40.4%sy shows a high load of system calls, and then a friend collects perf as follows:

Next, my friend collected the pstack for me, and I found that a large number of threads were in the following state:

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

We can note that _ _ tz_convert is evidence of a time zone shift.

II. Brief description of timestamp

Timestamp: occupies 4 bytes, and the internal implementation is seconds since New Epoch time (1970-01-01 00:00:00), so this format needs to do the necessary time zone conversion to get the correct data when it is shown to the user. Let's take a look at the internal representation by accessing the ibd file, using two of my tools, innodb and bcview, and refer to https://www.jianshu.com/p/719f1bbb21e8 in detail.

Internal representation of timestamp

Set up a test table

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 ('0-01-01-01 01 sec 01sec 01'); Query OK, 1 row affected (0.00 sec)

Let's take a look at the internal representation as follows:

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

Sort it out as follows:

000001ac3502:rowid

000000070d52:trx id

C80000002f0110:roll ptr

The actual data decimal of the 5c2a4b4d:timestamp type is 1546275661

We use the Linux command as follows:

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

Because my Linux is also in the CST + 8 time zone, and the data here is the same as in MySQL. Let's adjust the time zone and take a look at the values as follows:

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)

You can see here that I subtracted 2 hours because my time zone changed from + 8 to + 6.

III. Timestap conversion

MySQL has two options for converting seconds to actual time since New Epoch time (1970-01-01 00:00:00), depending on the setting of the parameter time_zone:

Time_zone: if set to SYSTEM, the OS session time zone obtained by sys_time_zone is used and OS API is used for conversion. Corresponding conversion function Time_zone_system::gmt_sec_to_TIME

Time_zone: if it is set to the actual time zone, such as'+ 08 time_zone 00', then use MySQL's own method for conversion. Corresponding conversion function Time_zone_offset::gmt_sec_to_TIME

In fact, both Time_zone_system and Time_zone_offset inherit from the Time_zone class, and the virtual functions of the Time_zone class are rewritten, so the upper-level calls are Time_zone::gmt_sec_to_TIME.

Note that this conversion operation is required for every row of eligible data.

IV. Problem repair plan

From the frame of the problem stack, we can see that the fault is converted using the Time_zone_system::gmt_sec_to_TIME function, so we can consider the following:

Time_zone: set to the specified time zone, such as'+ 08 time_zone 00'. In this way, instead of using OS API for transformation, MySQL's own internal implementation calls the Time_zone_offset::gmt_sec_to_TIME function. It is important to note, however, that us% will intensify if you use MySQL's own implementation.

Using datetime instead of timestamp, the new version of datetime is 5 bytes, only one more byte than timestamp.

5. Comparison of sy% usage before and after restoration

According to a friend, he finished the modification around 11:00 by changing the time_zone to'+ 08 CPU 00'. The following shows the comparison of the usage rate before and after the modification:

Before repairing:

After repair:

VI. Alternate stack frame

Time_zone='SYSTEM' conversion stack frame

# 0 Time_zone_system::gmt_sec_to_TIME (this=0x2e76948, tmp=0x7fffec0f3ff0, tweak 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' conversion stack frame

# 0 Time_zone_offset::gmt_sec_to_TIME (this=0x6723d90, tmp=0x7fffec0f3ff0, tweak 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) above is how to solve the problem of high CPU% sy caused by timestamp time zone conversion in MySQL The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.