技術共有|MySQL:timestampタイムゾーン変換によるCPU%sys高の問題

8008 ワード
mysql データベース
作者:高鵬
文章の末尾には彼の著書「MySQL主従原理32講を深く理解する」があり、MySQL主従、GTIDに関する技術知識を深く理解している.本文は学習記録ですので、間違いがあるかもしれませんのでご了承ください.
この問題は友人が出会った@風雲であり、この友人はすでに正しい判断を得ており、以下にいくつかの説明を行う.
一、問題展示以下は問題当時のシステム負荷は以下の通りである.
40.4%syはシステム呼び出し負荷の高い表現であり、友人がperfを以下のように収集した.
次に友達がpstackを採集してくれたので、大量のスレッドが以下の状態にあることに気づきました.
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これはまさにタイムゾーン転換の証拠である.
二、timestampについてtimestamp:4バイトを占有し、内部実装は新紀元時間(1970-01-01 00:00:00)以来の秒であるため、このフォーマットはユーザーに示す際に必要なタイムゾーン変換をして正確なデータを得る必要がある.次にibdファイルにアクセスして内部表現方法を確認し、私の2つのツールinnodbとbcviewを使用しました.
詳細:https://www.jianshu.com/p/719....
timestampの内部表示テストテーブルの作成
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)

内部表示を見てみましょう.
[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
  • 5 c 2 a 4 b 4 d:timestampタイプの実際のデータ十進法は154627561
    • です.
    Linuxコマンドは次のとおりです.
    [root@gp1 ~]# date -d @1546275661
Tue Jan  1 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の設定には2つの選択肢があります.
  • 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_システムとタイム_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%が激化することに注意してください.
  • はtimestampの代わりにdatetimeを使用し、新しいバージョンのdatetimeは5バイトで、timestampより1バイトしかありません.

    • 五、修復前後
    %syの使用量の比較友人によると、午前11時すぎに修正が完了したという.time_zoneを'+08:00'に変更し、修正前後のCPU使用率の比較を示します.
    修復前:
    修正後:
    六、予備スタックフレーム
  • time_zone=‘SYSTEM’変換スタックフレーム
    • #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'変換スタックフレーム
    • #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の悲観的なロックがどのように使われているかをまとめます
    Mysql結合クエリー-JOIN-複数の関連テーブルからデータをクエリー