DDLが取得するMDLの調査(私的)まとめ

Transcript

1. DDLが取得するMDL (私的)まとめ MySQLアンカンファレンス #9 @tom__bo
2. やりたいことやりたかったこと • メタデータロック(MDL)の理解を深めて、DDL実行をより安全 にしたい • 経験則ではなく事前に競合しうる操作を把握したい • 競合しうるMDLがわかれば、そのMDL取得中の処理時間を見積もって 問題ないか判断できそう • なぜMDL? • DDLやバックアップ実行時に、並行して実行されるDMLと競合すると 影響範囲が大きくなりがち • pt-osc, gh-ostを使ってもMDLを取らないわけではない • lock_wait_timeoutを下げることで影響度合いの調整はできる （振り返り）
3. よくやるMDLの確認方法 • performance_schema.metadata_locks テーブル • 取得中/取得待ちのMDLしか確認できない • 実際にクエリ単位で競合させて確認するのは大変 • 思いついて単純に再現できるケースしか確認できない （振り返り）
4. MDLの取得・解放を詳細に確認する方法 • プリントデバッグ方式でロギング • コード修正が必要 • 柔軟な出力が可能 • トレースファイルを取得する • トレースを有効にすると出力が多すぎる • MDL取得部分はDBUG_TRACEがないのでコード修正が必要 • それぞれの処理のところでgdbで止める • 逐次実行を進めるのは大変 （振り返り）
5. LogErr()でログ出力 • ロックの取得、解放、アップグレード部分でロギング • MDL_context::acquire_lock(), ::release_lock(), ::upgrade_shared_lock() サンプル実装: https://github.com/tom--bo/mysql-server/commit/e343e5cfd0ed980f663bb989deafd81b9c4bc4c9 （振り返り）
6. MDLのロックタイプ ロックタイプ 略記 説明 MDL_INTENTION_EXCLUSIVE IX スコープ付きロック用のIXロック、スキーマの削除防止 MDL_SHARED S メタデータのみを参照するため MDL_SHARED_HIGH_PRIO SH I_Sなどで排他ロックを待たずに即時取得を可能にする MDL_SHARED_READ SR データの読みを行う場合 MDL_SHARED_WRITE SW データの更新を行う場合 MDL_SHARED_WRITE_LOW_PRIO SWLP 優先度が低いSWロック MDL_SHARED_UPGRADABLE SU あとから排他ロックに昇格可能なロック MDL_SHARED_READ_ONLY SRO 読み取り専用で他の更新をすべてブロックする MDL_SHARED_NO_WRITE SNW データの変更を禁止するためのロック MDL_SHARED_NO_READ_WRITE SNRW データの読み書きをブロックする MDL_EXCLUSIVE X メタデータとデータの両方を変更するときにとる （振り返り）
7. ロックの競合表 • 全体 （振り返り）
8. MDLの期間 • ステートメント、トランザクション、特定期間の3種類 期間 説明 MDL_STATEMENT • ステートメントの実行期間の間ロックする • ステートメントの終了時に自動的に開放される MDL_TRANSACTION • トランザクションの実行期間の間ロックする • トランザクションの終了時に開放される MDL_EXPLICIT • 特定の期間ロックする • ステートメントやトランザクションの終了後ものこり、明示的に release_lock()を呼ぶことで開放される （振り返り）
9. 次回 • 任意の2つのステートメントが取得するMDLのうち競合するも のを列挙する • MDLを取得している間の処理内容を簡単に把握する方法を 模索する • ステートメントが取得するMDLsは同じと仮定する • => 例外はある(統計情報の更新など) （振り返り）
10. 先に結論 • 任意の2つのステートメントが取得するMDLのうち競合するも のを列挙する • DDLは対象のテーブルに対して”TABLEレベル”の排他(X)ロック を取るので、同テーブルへのクエリ(DML)はすべて競合する • Online DDLでは実行時のタイミング次第で実行できることもあ るが、DDL実行中にコントロールすることは現実的ではない • =>「DDLはロングトランザクションの実行中を避ける」一択
11. 調査中にわかったこと • DDLの取るMDLのDURATION正確ではない...？ • enum_mdl_durationにはINSTANTを表すものが無いせい？ • 対象テーブル以外にもインスタントなMDLを高頻度で取る • Data Dictionary(DD), ACL_CACHE, COLUMN_STATISTICS, COMMIT • ほとんどのDDLは複数のNamespaceにMDLを取る • GLOBAL, BACKUP_LOCK, SCHEMA, TABLE, TABLESPACE
12. MDLロギング結果サンプル ログ全体: https://github.com/tom--bo/mdl_logging_logs/blob/main/sample.log
13. MDLロギング結果サンプル DDLの取るMDLのDURATION正確ではない...？ ログ全体: https://github.com/tom--bo/mdl_logging_logs/blob/main/sample.log
14. MDLロギング結果サンプル 対象テーブル以外にもインスタントなMDLを高頻度で取る ログ全体: https://github.com/tom--bo/mdl_logging_logs/blob/main/sample.log
15. MDLロギング結果サンプル ほとんどのDDLは複数のNamespaceにMDLを取る ログ全体: https://github.com/tom--bo/mdl_logging_logs/blob/main/sample.log
16. DDLが取得するMDL Query GLOBAL BACKUP_LOCK SCHEMA TABLE TABLESPACE COLUMN_STATISTICS CREATE_TABLE_T1 IX IX IX X X - DROP_TABLE_T1 IX IX IX X X - ADD_PRIMARY_KEY IX IX IX X X SR DROP_PRIMARY_KEY IX IX IX X X SR DROP_PK_AND_ADD_ANOTHER_COL IX IX IX X X SR ADD_INDEX IX IX IX X IX SR DROP_INDEX IX IX IX X IX SR RENAME_INDEX IX IX IX X IX SR ADD_FULLTEXT_INDEX IX IX IX X X SR DROP_FULLTEXT_INDEX IX IX IX X IX SR ADD_SPATIAL_INDEX IX IX IX X IX SR DROP_SPATIAL_INDEX IX IX IX X IX SR ADD_COLUMN_ALGO_INSTANT IX IX IX X IX SR ADD_COLUMN_ALGO_INPLACE IX IX IX X X SR ADD_COLUMN_ALGO_COPY IX IX IX X X SR DROP_COLUMN IX IX IX X X SR RENAME_COLUMN IX IX IX X IX SR CHANGE_COLUMN_DEFAULT_VAL IX IX IX X IX SR CHANGE_DATA_TYPE IX IX IX X X SR MAKE_COLUMN_NULL IX IX IX X X SR MAKE_COLUMN_NOT_NULL IX IX IX X X SR 表:クエリ実行中にオブジェクトに対して取得するMDLの最上位レベル
17. DMLが取得するMDL Query GLOBAL BACKUP_LOCK SCHEMA TABLE TABLESPACE COLUMN_STATISTICS SELECT - - - SR - - SELECT_FOR_UPDATE IX - - SW - - INSERT IX - - SW - - UPDATE IX - - SW - - DELETE IX - - SW - - INSERT_INTO_SELECT IX - - SW - - 表:クエリ実行中にオブジェクトに対して取得するMDLの最上位レベル
18. Ops系のクエリが取得するMDL Query GLOBAL BACKUP_LOCK SCHEMA TABLE TABLESPACE COLUMN_STATISTICS FLUSH_TABLES - - - - - - FLUSH_TABLES_WITH_READ_LOCK S - - - - - FLUSH_TABLES_T1_WITH_READ_LOCK IX IX SNW SR LOCK_TABLES_T1_READ - - - SRO - - LOCK_TABLES_T1_READ_LOCAL - - - SRO - - LOCK_TABLES_T1_WRITE IX - IX SNRW IX - LOCK_TABLES_T1_LOW_PRIO_WRITE IX - IX SNRW IX - ANALYZE_TABLE - IX IX SR - - CHECK_TABLE - IX IX SR - - LOCK_INSTANCE_FOR_BACKUP - S - - - - OPTIMIZE_TABLE IX IX IX X X SR 表:クエリ実行中にオブジェクトに対して取得するMDLの最上位レベル
19. まとめ • DDLは対象のテーブルに対して”TABLEレベル”の排他(X)ロック を取るので、同テーブルへのクエリ(DML)はすべて競合する • DDL安全実行を目指した調査はここまで • FK込みやSTORED PROCEDURE/Function, etc...のMDLは整理したい
20. オンラインDDLが実行できる期間を観測 Method Namespace Schema Table Type Duration MDL_context::acquire_lock GLOBAL MDL_INTENTION_EXCLUSIVE MDL_STATEMENT MDL_context::acquire_lock BACKUP_LOCK MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock SCHEMA d1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock TABLE d1 t1 MDL_SHARED_UPGRADABLE MDL_TRANSACTION MDL_context::acquire_lock TABLESPACE d1/t1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock GLOBAL MDL_INTENTION_EXCLUSIVE MDL_STATEMENT MDL_context::acquire_lock TABLE d1 t1 MDL_SHARED_UPGRADABLE MDL_TRANSACTION MDL_context::acquire_lock TABLE d1 #sql-1f4c7_9 MDL_EXCLUSIVE MDL_STATEMENT MDL_context::acquire_lock TABLE d1 t1 MDL_EXCLUSIVE MDL_TRANSACTION MDL_context::upgrade_shared_lock TABLE d1 t1 MDL_EXCLUSIVE MDL_context::downgrade_lock TABLE d1 t1 MDL_SHARED_UPGRADABLE (DDLによる変換処理...) MDL_context::acquire_lock TABLE d1 t1 MDL_EXCLUSIVE MDL_TRANSACTION MDL_context::upgrade_shared_lock TABLE d1 t1 MDL_EXCLUSIVE MDL_context::acquire_lock TABLESPACE d1/t1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock GLOBAL MDL_INTENTION_EXCLUSIVE MDL_STATEMENT MDL_context::acquire_lock GLOBAL MDL_INTENTION_EXCLUSIVE MDL_STATEMENT MDL_context::acquire_lock TABLESPACE d1/t1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock TABLESPACE d1/t1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock TABLESPACE d1/t1 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::acquire_lock COMMIT MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION MDL_context::release_lock COMMIT MDL_context::acquire_lock COLUMN_STATISTICS d1 t1 MDL_SHARED_READ MDL_STATEMENT MDL_context::acquire_lock COLUMN_STATISTICS d1 t1 MDL_SHARED_READ MDL_STATEMENT MDL_context::release_lock COLUMN_STATISTICS d1 t1 MDL_context::release_lock COLUMN_STATISTICS d1 t1 MDL_context::release_lock TABLE d1 #sql-1f4c7_9 MDL_context::release_lock GLOBAL MDL_context::release_lock TABLESPACE d1/t1 MDL_context::release_lock TABLE d1 t1 MDL_context::release_lock SCHEMA d1 MDL_context::release_lock BACKUP_LOCK MDL_context::acquire_lock TABLE dummy_sdi_db SDI_122 MDL_SHARED MDL_TRANSACTION MDL_context::release_lock TABLE dummy_sdi_db SDI_122 表: ALTER TABLE ADD INDEX実行中に取得されるMDL(時系列)