メインコンテンツまでスキップ

MySQL

このページでは FPT Database Engine で報告の多い MySQL の問題を、特定に役立つ症状と解決手順とともにまとめています。自分で切り分けるとき、または FPT Support に渡す情報を集めるときに使ってください。

各項目は症状、原因、影響、対処の順で構成されています。

既知の問題​

backup が失敗し OPTIMIZE TABLE を求められる​

症状​

MySQL の backup job が失敗し、次のようなメール通知が届きます。

cluster_id : abcxyz11
cluster_name : clustername
vdc_name : ABCXYZ_VCD
org_name : ABCXYZ-ORG
start_time : 10/23/2055 00:30:02
backup_type : diff
backup_size : NoneG
backup_log : ERROR: Please run OPTIMIZE TABLE on all listed tables to fix this issue. Tables found: db/transactions...
backup_state : failed
created_at : 10/23/2055 00:31:01

原因は、FPT Cloud が FPT Database Engine の backup に使用している Percona XtraBackup の不具合です。

原因​

MySQL 8.0.29 以降、InnoDB は INSTANT ADD/DROP COLUMN に対応しています。INSTANT 操作はテーブルをコピーせず、再構築もしません。InnoDB のディクショナリにメタデータを書き込むだけで、これは TOTAL_ROW_VERSIONS > 0 として現れます。

XtraBackup はこのメタデータを持つテーブルに完全には対応していないため、INSTANT ADD/DROP COLUMN が使われたテーブルを処理できません。backup job は停止し、OPTIMIZE TABLE の実行を求めます。

影響​

  • データの配置が最適でなくなるため、クエリの性能が低下します。
  • システムの負荷が上がり、リソースとメモリを消費します。
  • INSERT と UPDATE の処理時間が長くなります。
  • 断片化したテーブルは backup と復旧の両方を遅くします。

対処​

対象のテーブルを再構築して INSTANT のメタデータを解消します。

OPTIMIZE TABLE db.transactions;

完了するとテーブルが完全に再構築され、INSTANT のカラムバージョンのメタデータが消え、TOTAL_ROW_VERSIONS が 0 に戻り、backup が正常に実行できるようになります。

警告

OPTIMIZE TABLE はテーブル全体を再構築し、WRITE ロックを取得する場合があります。大きなテーブルでは再構築に時間がかかるため、負荷の低い時間帯に計画し、事前に十分な一時領域があることを確認してください。

JSON カラム上の複合インデックスで MySQL がクラッシュする​

症状​

JSON カラムの上に構築された複合インデックスを使うクエリで、MySQL の node がクラッシュします。

22:20:45 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
...
Query (407ad76b1830): SELECT `fort_knox_funds_flows`.* FROM `fort_knox_funds_flows`
WHERE (25830440 MEMBER OF(`fort_knox_funds_flows`.`money_movements` ->> "$[*].to_user_id")
OR 25830440 MEMBER OF(`fort_knox_funds_flows`.`money_movements` ->> "$[*].from_user_id"))
ORDER BY `fort_knox_funds_flows`.`created_at` DESC LIMIT 20

これは MySQL 本体の不具合です。詳細は MySQL bug 109542 を参照してください。

原因​

MySQL 8.0.2x 以降、JSON カラム内のフィールドを参照する INDEX 定義を持つテーブルがサーバーをクラッシュさせることがあります。MySQL は JSON カラム上のインデックスを確実に作成・維持できません。

  • 複合インデックス内で JSON オブジェクトを正しく扱えず、メモリエラーや非同期処理の不具合を引き起こします。
  • 複合インデックス内での JSON データの格納と取得を最適化できません。
  • Full Disk Encryption などの storage 機能があると、症状が悪化する場合があります。

影響​

MySQL が予告なくクラッシュまたは再起動します。場合によってはその後データを復旧できず、本番環境の可用性と信頼性に直接影響します。

対処​

  • JSON カラムが関わる場合は、複合インデックスではなく単一カラムのインデックスを使います。
  • JSON カラムに直接インデックスを張ることは避けます。必要な場合は JSON の値から generated column を作り、そちらにインデックスを張ります。
  • この不具合が修正されている 8.0.42 などの新しい MySQL にアップグレードします。

slave node での metadata lock の滞留​

症状​

MySQL HA の database で、master node は正常に読み書きできているにもかかわらず、2 台の slave node のレプリケーション遅延が急増し、約 2 時間に達します。slave node では多数のスレッドが Waiting for table metadata lock の状態で滞留します。

1073  admin  10.225.65.36:25680  fpt  Query  178  Waiting for table metadata lock  SELECT COUNT(1) AS `cnt` FROM `user_notifications` ...
1075  admin  10.225.65.36:25694  fpt  Query  178  Waiting for table metadata lock  SELECT COUNT(1) AS `cnt` FROM `user_notifications` ...
...

これは対象のテーブルに対して DDL を実行した後に発生し、slave node が metadata lock の滞留状態に陥ります。

原因​

MySQL は DDL 文と DML 文の実行中、スキーマとテーブルのレベルでテーブル構造を保護するために metadata lock (MDL) を使います。

MySQL InnoDB Cluster では、ALTER TABLE、CREATE INDEX、DROP TABLE などレプリケートされた DDL のトランザクションが、slave node の applier スレッドによって順次適用されます。長時間実行中のクエリやコミットされていない DML により、ユーザーセッションが MDL ロックを保持したままだと、applier スレッドは待機します。

applier スレッドが時間内にロックを取得できないと、Global Replication Queue に残るトランザクションがその後ろで詰まります。applier スレッドが停止し、そのテーブルに触れるすべてのスレッドも同時に停止するため、slave node はアプリケーションから実質的に利用できなくなります。

影響​

クエリとトランザクションがブロックされ、データへのアクセスが妨げられ、システムのレイテンシが上昇します。レプリケーションのトランザクションが滞留してレプリケーション遅延を生み、database 全体の性能が低下します。

対処​

  1. DDL 文が対象とするテーブルに触れているアプリケーションとサービスを一時停止します。これにより新しいクエリが metadata lock を取得したり待機列に並んだりしなくなります。
  2. slave node を再起動してロックを保持しているスレッドを解放します。再起動により滞留したセッションが終了し、DDL を適用できるようになります。
ヒント

QPS の高いテーブルに DDL を実行する前に、その文が影響するテーブルとインデックスを使っているアプリケーションを切断してください。これでロックの滞留自体を避けられ、変更が database を乱すこともなくなります。

次のステップ​