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

MySQLトラブルシューティング

MySQLトラブルシューティングページでは、FPT Cloud DBaaS上でMySQLサービスを利用する際によく発生する問題を特定し解決するための簡易ガイドを提供します。本セクションでは、接続エラー、構成の問題、パフォーマンス低下、バックアップ/リストアの失敗、MySQL固有のシナリオなど、頻繁に発生する問題を中心に扱います。 各トピックには、症状、根本原因、および推奨される解決策が明確に記載されており、初期のトラブルシューティングを実施したり、技術サポートチームと効率的に連携したりすることができます。 本ドキュメントの目的は、障害発生時のサービス中断を最小限に抑えつつ、安定的かつ安全で効率的にMySQLを運用できるように支援することです。

エラーカテゴリ:​

  1. Backup Error: "Please run OPTIMIZE TABLE on all listed tables…"
  2. Server Crash: JSONカラムに対する複合インデックスを使用した際のMySQLクラッシュ
  3. Metadata Lock Storm

1. Backup Error: "Please run OPTIMIZE TABLE on all listed tables…"​

1.1. 症状​

FPT Database EngineでのMySQLバックアップ操作中に、バックアップタスクが失敗し、上記のようなエラー通知がメールで送信されることがあります: 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 この問題は、FCIがMySQLバックアップに使用している Percona XtraBackupの既知の不具合 によって発生します。

1.2. 根本原因​

MySQL 8.0.29以降 、InnoDBは INSTANT ADD/DROP COLUMN をサポートしており、テーブルを再構築せずにカラムを追加または削除できます。 INSTANTコマンドの特徴:

  • テーブル全体のコピーが発生しません。
  • テーブルの再構築が発生しません。
  • InnoDBディクショナリに追加のメタデータが書き込まれるだけです（TOTAL_ROW_VERSIONS > 0で示されます）。

しかしながら、 XtraBackupはTOTAL_ROW_VERSIONS > 0のテーブルに完全には対応していません 。その結果、XtraBackupはINSTANT ADD/DROP COLUMNが使用されたテーブルを処理できず、バックアッププロセスが停止し、OPTIMIZE TABLEを要求するエラーが返されます。

1.3. 影響​

このエラーは以下の影響を引き起こします:

  • クエリパフォーマンスの低下: データが最適に並んでいないため、操作が遅くなります。
  • システム負荷の増加: システムリソースとメモリを消費します。
  • データ挿入または更新の遅延: INSERTおよびUPDATE操作に時間がかかることがあります。
  • メンテナンスおよびバックアップの困難化: 断片化したテーブルはバックアップまたはリカバリのプロセスを遅くする可能性があります。

1.4. 解決策と推奨事項​

以下のコマンドを使用して、影響を受けたテーブルを再構築しINSTANTメタデータを削除します:

CopyOPTIMIZE TABLE db.transactions;

実行後:

  • テーブルが完全に再構築されます。
  • INSTANTカラムのメタデータが削除されます。
  • TOTAL_ROW_VERSIONSが0に戻ります。
  • バックアップ操作が正常に再開されます。

注意 :

  • OPTIMIZE TABLEはテーブル全体を再構築し、WRITEロックを引き起こす可能性があります。
  • 大きなテーブルの場合、再構築プロセスにかなりの時間がかかる場合があります。この操作はオフピーク時間に実施してください。
  • 実行前に十分な一時ストレージ領域があることを確認してください。

2. Server Crash: JSONカラムに対する複合インデックスを使用した際のMySQLクラッシュ​

2.1. 症状​

JSONカラムを含む複合（マルチカラム）インデックスを使用すると、MySQLノードが予期せずクラッシュする場合があります: ` 22:20:45 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunc-tioning hardware.
Thread pointer: 0x407b5f0a3000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 407b6c43de60 thread_stack 0x40000
/rdsdbbin/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x30) [0x1f7bb30]
/rdsdbbin/mysql/bin/mysqld(print_fatal_signal(int)+0x27c) [0xfaa93c]
/rdsdbbin/mysql/bin/mysqld(handle_fatal_signal+0xc4) [0xfaaa64]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0x400018f2a668]
/rdsdbbin/mysql/bin/mysqld(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned char const*, un-signed long)+0x164) [0x2225a64]
/rdsdbbin/mysql/bin/mysqld(row_sel_store_mysql_rec(unsigned char*, row_prebuilt_t*, unsigned char const*, dtuple_t const*, bool, dict_index_t const*, dict_index_t const*, unsigned long const*, bool, lob::undo_vers_t*, mem_block_info_t*&)+0x19c) [0x2228e3c]
/rdsdbbin/mysql/bin/mysqld(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x1a90) [0x222e410]
/rdsdbbin/mysql/bin/mysqld(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)+0x378) [0x20de778]
/rdsdbbin/mysql/bin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x3f8) [0x10aef98]
/rdsdbbin/mysql/bin/mysqld(handler::read_range_first(key_range const*, key_range const*, bool, bool)+0x60) [0x10afa20]
/rdsdbbin/mysql/bin/mysqld(ha_innobase::read_range_first(key_range const*, key_range const*, bool, bool)+0x1c) [0x20a955c]
/rdsdbbin/mysql/bin/mysqld(handler::multi_range_read_next(char**)+0x118) [0x10b02d8]
/rdsdbbin/mysql/bin/mysqld(handler::ha_multi_range_read_next(char**)+0x34) [0x10ac174]
/rdsdbbin/mysql/bin/mysqld(IndexRangeScanIterator::Read()+0x3c) [0x12b951c]
/rdsdbbin/mysql/bin/mysqld(IndexMergeIterator::Init()+0x130) [0x12b88f0]
/rdsdbbin/mysql/bin/mysqld(filesort(THD*, Filesort*, RowIterator*, unsigned long, unsigned long long, Filesort_info*, Sort_result*, unsigned long long*)+0x178) [0x1093e18]
/rdsdbbin/mysql/bin/mysqld(SortingIterator::DoSort()+0x54) [0x1200794]
/rdsdbbin/mysql/bin/mysqld(SortingIterator::Init()+0x1c) [0x120081c]
/rdsdbbin/mysql/bin/mysqld(LimitOffsetIterator::Init()+0x20) [0x135dd40]
/rdsdbbin/mysql/bin/mysqld(Query_expression::ExecuteIteratorQuery(THD*)+0x200) [0xf18160]
/rdsdbbin/mysql/bin/mysqld(Query_expression::execute(THD*)+0x30) [0xf185b0]
/rdsdbbin/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x158) [0xea7458]
/rdsdbbin/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0xf2c) [0xe5504c]
/rdsdbbin/mysql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x340) [0xe580c0]
/rdsdbbin/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1560) [0xe59b40]
/rdsdbbin/mysql/bin/mysqld(do_command(THD*)+0x1e0) [0xe5ae40]
/rdsdbbin/mysql/bin/mysqld() [0xf9cc88]
/rdsdbbin/mysql/bin/mysqld() [0x2518490]
/lib64/libpthread.so.0(+0x722c) [0x400018fe622c]
/lib64/libc.so.6(+0xd46dc) [0x4000191da6dc]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
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
Connection ID (thread ID): 624306
Status: NOT_KILLED
cluster_name : clustername
` これはJSONカラム上の連想インデックスの使用に関連するMySQLのエラーであり、MySQLノードのクラッシュを引き起こします。このエラーの詳細は次のリンクを参照してください: https://bugs.mysql.com/bug.php?id=109542。

2.2. 根本原因​

MySQL 8.0.2x以降、JSONカラム内のフィールドを使用するINDEX定義を持つテーブルでは、上記のクラッシュが発生する場合があります。このエラーの主な原因は、MySQLが複合インデックス内のJSONカラムに対してインデックスを作成・維持しようとする際に問題が発生することです。具体的には:

  • JSONカラムとインデックスの非互換性: MySQLは複合インデックス内のJSONオブジェクトを適切に処理できず、メモリエラーや非同期処理が発生します。
  • JSONデータの保存・取得の問題: MySQLは複合インデックス内でのJSONデータの保存・取得が最適ではないため、クラッシュを引き起こします。
  • 特殊なストレージ機能（Full Disk Encryptionなど）との相互作用は、エラーの深刻度を高める可能性があります。

2.3. 影響​

このエラーが発生すると、MySQLはクラッシュまたは予期せず再起動することがあり、場合によってはクラッシュ後にデータベースが復旧できなくなることもあります。これは、特に本番環境において、サービスの可用性と信頼性に深刻な影響を及ぼします。

2.5. 解決策と推奨事項​

  • JSONカラムを扱う際は、複合インデックスの代わりに単一インデックスを使用してください。
  • JSONカラムに直接インデックスを作成するのを避けるか、必要な場合はgenerated columnを使用してJSON内の特定の値にインデックスを付けてください。
  • MySQLをより新しいバージョン（例: 8.0.42）に更新してください。この不具合は報告後のいくつかのMySQLバージョンで確認され、修正されています。

3. Metadata Lock Storm​

3.1. 症状​

HAモデルでMySQLを使用している場合、次のような状況が発生することがあります。MySQLデータベースクラスターは正常に動作しており（マスターノードは問題なく読み取り/書き込みが可能）、2つのスレーブノードではデータの遅延が大幅に増加する（約2時間まで）ことがあります。同時に、スレーブノード上で複数のスレッドが「Waiting for table metadata lock」状態で以下のように現れます: 1073 admin 10.225.65.36:25680 fpt Query 178 Waiting for table metadata lock SELECT COUNT(1) AScntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1075 admin 10.225.65.36:25694 fpt Query 178 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat 1077 admin 10.225.65.36:34558 fpt Query 176 Waiting for table metadata lock SELECT COUNT(1) AS cntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1078 admin 10.225.65.36:34570 fpt Query 174 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat 1083 agent 10.225.65.46:36868 NULL Query 167 Waiting for table metadata lock SELECT table_schema, table_name, column_name, auto_increment,\n CAST(pow(2, case data_type\n 1116 admin 10.225.65.36:49646 fpt Query 125 Waiting for table metadata lock SELECT COUNT(1) AS cntFROMuser_notifications UserNotificationEntity WHERE ( ((UserNotificat
1118 admin 10.225.65.36:49652 fpt Query 124 Waiting for table metadata lock SELECT COUNT(1) AS cnt FROM user_notifications UserNotificationEntity WHERE ( ((UserNotificat このエラーは、user_notificationsテーブルに対してDDLコマンドを実行した後に発生し、データベース（2つのスレーブノード）がMetadata Lock Storm状態になります。

3.2. 根本原因​

MySQLは、Data Definition Language（DDL）およびData Manipulation Language（DML）コマンドを実行する際に、テーブルの構造情報（スキーマレベルおよびテーブルレベルを含む）を保護するためにMetadata Lock（MDL）を使用します。 MySQL InnoDB Cluster環境では、レプリケートされたDDLトランザクション（ALTER TABLE、CREATE INDEX、DROP TABLEなど）は、applierスレッドによってスレーブノード上で順次実行されます。しかし、長いクエリや未コミットのDMLなどの理由により、ユーザーセッションがMDLロックを保持している場合があります。これによりapplierスレッドがロックを取得するまで長時間待機することになります。 applierスレッドが時間内にロックを取得できない場合、GRキュー（Global Replication Queue）内の他のトランザクションが渋滞します（上記の例のuser_notificationsテーブルのように）。その結果、スレーブノード上のapplierスレッドがハングし、テーブルにアクセスするスレッドもハングし、スレーブノードへのアクセスが困難となり、アプリケーションエラーを引き起こします。

3.3. 影響​

このエラーが発生すると、ユーザーのクエリおよびトランザクションがブロックされ、データアクセスの中断およびシステム遅延の増加を引き起こします。MySQL Cluster環境では、これによりレプリケーショントランザクションが詰まり、データ遅延を発生させ、システム全体のパフォーマンスを低下させる可能性があります。

3.4. 解決策と推奨事項​

この場合にMetadata Lock Stormを回避するには、以下の手順を実施してください:

  • ステップ1: DDLタスクに関連するテーブルにアクセスしているアプリケーションやサービスを一時的に停止する → 目的: メタデータロックを保持するまたはロックを待機する新しいクエリの生成を防ぎます。
  • ステップ2: スレーブノードを再起動してロックを保持しているスレッドを解放する → 再起動により「スタック」したセッションやスレッドが終了し、DDLプロセスが正常に適用されます。

推奨事項 : QPS（Queries Per Second）の高いテーブルに対してDDLコマンドを使用してデータベースクラスターをアップグレードする際は、DDL文が影響するオブジェクト（テーブル、インデックスなど）からアプリケーションを切断してください。これによりMetadata Lock Stormを回避し、システムの中断なくスムーズなアップグレードプロセスを確保できます。

にDang Tranが最終更新