MySQL 5.6 から 5.7 にアップグレードする際のマスター スレーブ遅延問題のトラブルシューティング

最近、Zabbix データベースを MySQL 5.6 から 5.7 にアップグレードしたときに、マスター スレーブ遅延問題が発生しました。この問題は長い間私を悩ませており、解決されていませんでした。昨日ようやく解決しました。トラブルシューティングのプロセス全体を整理し、全員と共有しました。

環境説明:

MySQL マスターデータベースはバージョン 5.6 で、スレーブデータベースは 4 つあり、そのうち 3 つはバージョン 5.6、1 つはバージョン 5.7 です。すべてのマスターとスレーブのライブラリとテーブル構造は一致しています。5.7 スレーブデータベースは遅延が多く、5.6 スレーブデータベースは問題ありません。業務は Zabbix で監視されており、基本的にすべて挿入バッチ操作です。各挿入 SQL は約 400 ～ 1000 行のデータを挿入します。

質問：

MySQL5.7のスレーブデータベースは遅延が多く、リレーログは正常にディスクに書き込まれ、データベースへの適用は遅いです。ディスクIOとCPUに圧迫はありません。sync_binlogが20000または0、max_allowed_pa​​cket=128M、innodb_flush_log_at_trx_commit=0、bulk_insert_buffer_size = 128M、binlog_format=row、sync_relay_log=10000、並列レプリケーションは使用せず、SSLは有効ではなく、GDIDは有効ではなく、準同期は有効ではありません。

トラブルシューティングのプロセス:

1: 各パフォーマンス関連パラメータを確認し、異常がないことを確認します。

2: ネットワーク カード、ハード ディスクのチェック、サーバーの変更、データベース サーバーの再起動を行っても効果はありませんでした。5.7 の遅延がまだ存在していたため、ハードウェアの問題は除外されました。

3: 5.7 はメインデータベース 5.6 の binlog をリレーログに迅速かつ正常に同期しますが、5.7 データベースでのリレーログの再生効率は極めて低くなります。

4: 5.6 スレーブと 5.7 スレーブの show engine innodb status の結果を比較します。

=============5.6===============================
---バッファプール1
バッファプールサイズ 655359
バッファプールサイズ、バイト 10737401856
空きバッファ 1019
データベースページ 649599
古いデータベースページ 239773
変更された DB ページ 119309
保留中の読み取り 0
保留中の書き込み: LRU 0、フラッシュ リスト 0、単一ページ 0
ページは若くして作成された 10777670、若くない 181119246
13.90 若者/秒、157.51 若者以外/秒
閲覧ページ数 8853516、作成ページ数 135760152、書き込みページ数 784514803
20.96 読み取り/秒、58.17 作成/秒、507.02 書き込み/秒
バッファ プールのヒット率は 1000/1000、ヤング メイキング率は 2/1000 ではなく 0/1000
ページ先読み 0.00/秒、アクセスなしで削除 0.00/秒、ランダム先読み 0.00/秒
LRU 長さ: 649599、unzip_LRU 長さ: 0
I/O 合計[209618]:cur[2]、解凍合計[0]:cur[0]

==============5.7====================================
---バッファプール1
バッファプールサイズ 819100
バッファプールサイズ、バイト 13420134400
空きバッファ 1018
データベースページ 722328
古いデータベースページ 266620
変更された DB ページ 99073
保留中の読み取り 0
保留中の書き込み: LRU 0、フラッシュ リスト 0、単一ページ 0
若く作られたページは 37153、若くないページは 795
0.00 ヤング/秒、0.00 非ヤング/秒
閲覧ページ数 149632、作成ページ数 572696、書き込みページ数 2706369
0.00 読み取り/秒、0.00 作成/秒、0.00 書き込み/秒
バッファ プールのヒット率は 1000/1000、ヤング メイキング率は 0/1000 ではなく 0/1000
ページ先読み 0.00/秒、アクセスなしで削除 0.00/秒、ランダム先読み 0.00/秒
LRU 長さ: 722328、unzip_LRU 長さ: 453903
I/O合計[98685]:cur[0]、解凍合計[882]:cur[6]
+++++++++++++++++++++++

比較すると、unzip は 5.7 では値がありますが、5.6 では値がないことがわかりました。当初、遅延の原因は圧縮と解凍に関連しているのではないかと疑いました。

5: perf top -p pidof mysqld を使用して 5.7 スレーブ ライブラリを表示します。

libz.so.1.2.7[.]cc32 が約 6% と、mysqld よりも高い割合を占めていることがわかります。このライブラリは圧縮と解凍に関連しています。

6: innodb_compression_level を 0 に変更します (つまり、圧縮を有効にしません。デフォルトは 6、範囲は 0 ～ 9)。効果がなく、遅延がまだ存在することを確認します。のみ

libz のシェアは減少しましたが、libc-2.17.so のシェアは増加し、mysqld よりも高い約 9% になりました。 pstack を使用して、研究機関での解凍の待機問題を表示します。

7: zabbix の履歴テーブルを確認します。ディスク容量を節約するために、これらのテーブルは圧縮されています。

テーブルトレンドの作成（
itemid bigint(20) 符号なし NOT NULL,
クロック int(11) NOT NULL デフォルト '0',
num int(11) NOT NULL デフォルト '0',
value_min double(16,4) NOT NULL デフォルト '0.0000',
value_avg double(16,4) NOT NULL デフォルト '0.0000',
value_max double(16,4) NOT NULL デフォルト '0.0000',
主キー (itemid,clock)、
KEYクロック（時計）
) ENGINE=InnoDB デフォルト CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

これは圧縮パラメータ ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 に関連していると思われます。

8: すべての履歴テーブルを再構築し、ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 を削除し、再同期し、遅延を徐々に減らして回復します。

質問: 同じテーブル構造で、5.7 ではマスター スレーブ遅延が発生するのに、5.6 では発生しないのはなぜですか?これは、MySQL 5.7 の圧縮と解凍の下位互換性の問題が原因である可能性があります。これ以上調査はしませんでしたが、公式にバグを報告し、ソースコードを確認するように依頼しました: http://bugs.mysql.com/100702。

実稼働環境では、ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 を慎重に使用してください。業界の複数の専門家と話をしたところ、8.0 より前のバージョンの MySQL の圧縮はあまり信頼性が高くなく、8.0 では ZSTD を使用する方がよいと言われました。

MySQL 5.6 から 5.7 にアップグレードする際のマスタースレーブ遅延問題のトラブルシューティング手順に関するこの記事はこれで終わりです。MySQL 5.6 から 5.7 にアップグレードする際のマスタースレーブ遅延の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。