kenju's blog

About Programming and Mathematics

MySQL Online Schema Migration の選択肢

先日は以下の記事で pt-online-schema-change と Foreign Keys の挙動について見てみたが、そもそも MySQL で Online Schema Migration を実現する場合の選択肢にはどういったものがあるか見てみよう。

itiskj.hatenablog.com

確認する限り、以下のツールが現実的な選択肢:

  1. MySQL Online DDL
  2. pt-online-schema-change
  3. oak-online-alter-table
  4. Facebook's OSC
  5. SoundCloud's LHM
  6. github/gh-ost

pt-online-schema-change(以下:pt-osc) が代表的なツールで、日本国内でも利用されている様子は散見できる。

実装を確認する限り、pt-osc を始めとして、ほとんどのツールが MySQL Trigger の機能を利用している。

Tool Implementation Sync/Async
pt-osc MySQL Trigger + copy table Sync
OSC MySQL Trigger + copy table + change log Async
LHM MySQL Trigger + copy table Sync
gh-ost binlog examination (Row Based Replication) Async

Sync/Async とは、コピー先テーブルへの書き込みが、MySQL Trigger と同じ transaction space で発生するかどうかを示す。例えば、OSC では一旦 change log を挟んで書き込みをするため、Async である。

例えば、pt-osc では、INSERT, UPDATE, DELETE に対して Trigger を仕掛ける ことで、古いテーブルを copy してから rename するまでの間に発生した書き込みリクエストを、新しいテーブルに反映させることができている。

しかし、MySQL Trigger を使った場合のデメリットは、Trigger が他の live queries と同じ transaction space に存在することである。例えば、pt-osc が実行中、本番サーバで発生しているユーザからのリクエストやバッチ起因で発生している別のクエリとの間で lock contention が発生しうる(また、最枠の場合 deadlocks に陥る可能性もあるだろう)。

この問題を軽減するために、OSC では change log と呼ばれる、書き込みリクエストの履歴テーブル(WAL などで実現される redo-log のようなもの)を採用している。MySQL Trigger の書き込み先を、コピー先のテーブルではなくこの履歴テーブルにすることで、書き込み時の負荷を多少なりとも軽減させるとともに、Migration 中に発生した障害からの resiliency を強化している。が、何らかのテーブルに書き込んでいるという点では本質的に pt-osc と同様であり、完全に live queries との lock contention 問題を解決することができない。

一方、gh-ost は、MySQL Trigger ではなく binlog からの情報を用いる ことによって、この問題を解決しようとしている。彼らはこれを "Trigger-less Design" と読んでいる。もちろん Trigger-less Design にも欠点があって、読み込み/書き込みの増加によるトラフィックの増加 や、非同期イベントを処理することによるコードの複雑性といった課題を抱えてはいる。また、Foreign Keys はそもそも GitHub で利用していない ために、Foreign Keys 対応は Pending 状態でもある。

pt-online-schema-change の内部挙動と --alter-foreign-keys-method の drop_swap フォールバック問題

https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

pt-online-schema-change(以下:pt-osc)とは、MySQL においてオンラインでスキーマ変更を可能にするツールであり、 Percona Toolkit に同梱されているものの一つ。現在所属しているチームでは前から使われていて、時々 pt-osc の設定変更によるパフォーマンスチューニングや、online migration 時のエラー調査などで触ることが多かった。

昨年夏の話に遡る。当時、pt-osc を利用した Online Migration 実行時に、アプリケーションから Database へのリクエストが時々失敗し、どうやら謎のダウンタイムが存在しているようだという報告を Developer team から受けた。

その時の調査を担当したのだが、結論から言うと、Foreign Keys をもつテーブルの Migration を実行している際、一部のケースで、pt-osc の設計上ダウンタイムが発生せざるを得ない状況に陥っていたということを突き止めた。

このブログでは、その発生原因の詳細と、それを理解するための前提知識としての pt-osc の内部挙動について説明する。

Source Code

https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-online-schema-change

How to achieve online schema change

pt-osc の基本戦略としては、一時的にコピー先の Table を作成し、Trigger を利用して最新の変更を取り入れつつ、コピー元の Table から --chunk-time/--chunk-size オプションによって静的または動的に算出された行数に基づいて徐々にコピーしていく、という挙動になる。発想としてはシンプルだ。

  1. create an empty copy of the table to alter
  2. create triggers on the original table
  3. copy rows from the original table to the new table by small chunks
  4. RENAME table
  5. drop the original table

How to handle Foreign Keys

問題が起こるのは、コピー元の Table が Foreign Keys の参照を子テーブルから持たれている場合だ。どのタイミングで子テーブルの参照を、コピー元の古いテーブルからコピー先の新しいテーブルに切り替えるか、というタイミングがシビアな課題に直面する。

まず、pt-osc が Foreign Keys を保つ場合にどのように動作するかを見てみる。例えば、foo Table に対して、bar Table が子テーブルとして参照を持つとする。この場合、pt-osc は以下のような挙動をする。

f:id:itiskj:20210114004907p:plain
pt-online-schema-change - how to handle Foreign Keys

この時、子テーブル bar に存在する foo への Foreign Keys の参照を変更する際の手法を、--alter-foreign-keys-method で選択できる。

  • rebuild_constraints
  • drop_swap
  • auto ... rebuild_constraintsdrop_swap かを動的に判別する
  • none ... 子テーブルの Foreign Keys の参照は、DROP TABLE された存在しないテーブルを向いたままになる

基本的には、rebuild_constraints の利用が望ましい. なぜなら、コピー元の古いテーブルからコピー先の新しいテーブルに参照を切り替える時、ALTER を子テーブルに対して実行するため、スキーマの制約条件をそのまま維持できるからだ。

一方、drop_swap 戦略を用いた場合、ALTER を用いず、古いテーブルを DROP TABLE したあとに新しいテーブルを RENAME するだけなのだ。したがって、理論上は問題なく動くかもしれないが、その間に何らかのデータ更新やデータロスがあった時に、データの整合性は保証されない。

また、ドキュメント に記載されている通り、古いテーブルを DROP TABLE してから新しいテーブルを RENAME するまでの間、若干のダウンタイムが発生する。

This method is faster and does not block, but it is riskier for two reasons. First, for a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist, and queries against it will result in an error.

これは処理を考えれば当然のことだろう。この時にアプリケーションが Database になんらかのリクエストをした場合、当然エラーが発生するので、適切な Retry 処理を入れるなどの対応が必要となってくる。

rebuild_constraints fallbacks to drop_swap

しかし、問題は、実は rebuild_constraints を明示的に指定していても、drop_swap に Fallback するケースが有る、ということだ。

きっかけは、たまたまこのブログ で以下の文言を発見したことだ。

However, if the table t2 is too large, the alter operation may take too long, and - drop_swap may be forced. The main methods involved are determined_alter_fk_method, rebuild_constraints and swap_tables of pt-online-schema-change file.

The --alter-foreign-keys-method is determined at sub determine_alter_fk_method():

その後、Source Code を追って確認してみたが、どうやら上記に合致する実装を確認できた。コピーする総行数 $n_rows が、--chunk-size および --chunk-time から動的に算出される閾値 $max_rows を超えた場合、--alter-foreign-keys-method の指定に関わらず、必ず drop_swap 戦略にフォールバックする。

コピー対象の行数が多い場合、ALTER に時間がかかることが想定されるため、より早くテーブルを切り替えるために drop_swap するという設計は理解できなくはない。

ただ、明示的に rebuild_constraints を指定しているにも関わらず、アプリケーションから Database にリクエストする際に謎のダウンタイムが存在しており、実はその原因が drop_swap への Fallback だった、ということが今回発覚したのだった。

Alternative...?

とはいえ、Foreign Keys 制約を持った大規模なテーブルの Online Schema Migration というのは、なかなかチャレンジングな技術的課題ではないだろうか。

同様の Online Schema Migration では、GitHubFacebook, SoundCloud などからツールが提供されているが、軽く確認したところ、そもそも Foreign Keys をもつテーブルがサポートされていなかったりする。

※ 間違っていたり、ブログ投稿後実装されてサポートされていたり、サポートされているツールがあったりしたらぜひ教えてほしい。

Amazon RDS for MySQL Parameters Configuration の勘所

最近 MySQL の deadlocks や performance 問題の調査をすることが多い。本番環境では Amazon RDS for MySQL を使って、Aurora Cluster で動かしているデータベースを見ることが多いのだが、たまたま見つけた AWS official blogs からの一連のブログが、丁寧によくまとまっていた。

Best practices for configuring parameters for Amazon RDS for MySQL

特に part1 の Performance 関連の設定値については、以下の設定値についての勘所がまとまってある。

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_change_buffering
  • innodb_io_capacity
  • innodb_io_capacity_max
  • innodb_purge_threads
  • innodb_stats_persistent
  • innodb_thread_concurrency
  • innodb_sync_array_size
  • innodb_flush_log_at_trx_commit
  • tmp_table_size / max_heap_table_size
  • foreign_key_checks
  • query_cache_size (<- not to use it because it's going to be deprecated in 8.0)
  • optimizer_switch
  • Innodb_read_io_threads / Innodb_write_io_threads
  • innodb_status_output_locks
  • table_open_cache
  • thread_cache_size
  • max_seeks_for_key

特に、Highly concurrent workloads なアプリケーションの場合、デフォルトの設定値では十分なパフォーマンスが出せなくなりがち。その場合、MySQL の内部の実装やデータ構造を理解しつつ、スレッドの数や I/O capacity を調整してやる必要がある。

大抵のデータベースでそうだが、パフォーマンスが問題になってくるのは、Read ではなくWrite の場合がほとんど。Read は、Application Layer での cache から、Memcached などの middleware layer での cache, Page cache や Disk cache などあらゆるレイヤーで cache してパフォーマンスを出すことができる。一方、Write の場合はそうも行かないので、concurrent に走らせたり、buffering したりするものの、transaction (mutex lock) のことを考えたり、 syscalls overhead などのオーバーヘッドを考えたりすると、思うように性能が出なかったりする。

種々の設定値を調整する際には、MySQL Server に入って値を取得したりするが、https://github.com/prometheus/mysqld_exporter を使って時系列データを観測しておくとより便利。チームに入った頃にはすでにメインのデータベースには Exporter がしかけられていたので、快適なデバッグ体験だった。