pt-online-schema-changeを安全につかう

この記事はMySQL Casual Advent Calendar 2014とCyberAgent エンジニア Advent Calendar 2014の19日目の記事です。

この間のMySQL Casual Talks vol.7でも少し触れていますが、pt-online-schema-change（以下、pt-osc）には安全に実行するための便利なオプションが用意されています。これらのオプションを利用すれば、オンラインでの変更に抵抗がある方でも石橋を叩きながら実行することができます。5.6でOnline DDLが使えますが、セーフティに変更したい場合は有効です。

pt-online-schema-change — Percona Toolkit Documentation

スレッド数を監視する

--max-load

pt-oscはスレッド数をチェックしながら動作を制御できます。--max-loadのデフォルトは25で、超えた場合は下記のようなログを出力して一時停止します。

（略）
Copying `db_name`.`tbl_name`:  17% 35:45 remain
Pausing because Threads_running=44. <-この行
Copying `db_name`.`tbl_name`:  17% 40:58 remain
（略）

負荷状態に余裕があれば--max-load Threads_running=100のように値を指定することで一時停止せずに続行することができます。

--critical-load

--max-loadと書式が一緒で、こちらはデフォルト値が50です。これを超えた場合はpt-oscが終了します。

（略）
2014-06-10T17:11:10 Dropping triggers...
2014-06-10T17:11:10 Dropped triggers OK.
2014-06-10T17:11:10 Dropping new table...
2014-06-10T17:11:13 Dropped new table OK.
`db_name`.`tbl_name` was not altered.
2014-06-10T17:11:10 Error copying rows from `db_name`.`tbl_name` to `db_name`.`_tbl_name_new`: Threads_running=50 exceeds its critical threshold 50

早さを求めないときは--max-loadで実行負荷を制御しつつ、--critical-loadを多めに設定する、などといったやり方もできます。

レプリケーション遅延を監視する

pt-oscは、デフォルトでスレーブのレプリケーション遅延の状態をチェックしながら実行できます。ただ、マスターとスレーブで同じ権限を持っていない場合があるため、例えばrootユーザで実行すると以下のようなメッセージが出るかもしれません。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=root
Cannot connect to D=db_name,h=192.168.0.1,u=root
Cannot connect to D=db_name,h=192.168.0.2,u=root
No slaves found.  See --recursion-method if host db_master has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
（略）

この場合はマスターからスレーブ郡へ同じユーザ権限を与えることでチェックできます。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=osc_user,p=osc_pass
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
  db_backup
（略）

--check-slave-lag

上記のようにユーザ権限を与えれば、processlistを見てレプリケーションしているスレーブ全台の遅延状況を確認しながら実行できます。ただし、例えばバックアップ用のDBがスレーブよりスペックが劣っていて、遅延は許容したい場合があったと思います。そのときは--check-slave-lagでチェック対象を1台のみに絞ることができます。

$ pt-online-schema-change --execute --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=osc_user,p=osc_pass --check-slave-lag h=192.168.0.1,u=osc_user,p=osc_pass
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
（略）

また、これを利用することでマスターとスレーブで同じユーザ権限を持っていない場合でも、レプリ遅延をチェックすることができます。適切な権限があれば。

--max-lag

レプリ遅延のしきい値を設定でき、デフォルトは1です。これを超えると一時停止し、それ以上の遅延を防止することができます。Seconds_Behind_Masterを見ているので、許容できる秒数を指定します。

--check-interval

--max-lagをチェックをする間隔で、デフォルトは1秒です。

その他

--set-vars

MySQLの変数を渡すオプションですが、主にレプリケーションさせたくない場合に--set-vars="sql_log_bin=0"を指定することが多いです。

--dry-run

pt-oscは--dry-runか--executeのどちらかを必ずオプションに指定します。 まずは--dry-runで確認しましょう。

$ pt-online-schema-change --dry-run --alter="engine=InnoDB" h=localhost,D=db_name,t=tbl_name,u=root
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `db_name`.`tbl_name` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db_name._tbl_name_new OK.
Altering new table...
Altered `db_name`.`_tbl_name_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2014-12-18T17:01:26 Dropping new table...
2014-12-18T17:01:26 Dropped new table OK.
Dry run complete.  `db_name`.`tbl_name` was not altered.

--nodrop-old-table

pt-oscは、新しいテーブルに変更内容を適用し、リネームしてから既存のテーブルを削除する仕組みになっていますが、このオプションで既存のテーブルが_tbl_name_oldとして残ります。万が一のためだったり、テストする場合などに利用できるでしょう。

通常時

（略）
2014-12-18T16:53:18 Dropping old table...
2014-12-18T16:53:18 Dropped old table `db_name`.`_tbl_name_old` OK.
（略）

オプション指定時

（略）
Not dropping old table because --no-drop-old-table was specified.
（略）

--progress

デフォルトは30秒ごとの進捗（time,30）が表示されますがこれを変更できます。値はtime,percentageなどが利用できます。

$ pt-online-schema-change --execute --progress=time,1 --alter="（略）" h=localhost,D=db_name,t=tbl_name,u=ptosc,p=ptosc
Found 2 slaves:
  db_slave
  db_backup
Will check slave lag on:
  db_slave
  db_backup
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `db_name`.`tbl_name`...
Creating new table...
Created new table db_name._tbl_name_new OK.
Waiting forever for new table `db_name`.`_tbl_name_new` to replicate to db_master...
Altering new table...
Altered `db_name`.`_tbl_name_new` OK.
2014-12-18T23:43:43 Creating triggers...
2014-12-18T23:43:43 Created triggers OK.
2014-12-18T23:43:43 Copying approximately 1591002 rows...
Copying `db_name`.`tbl_name`:   1% 01:20 remain
Copying `db_name`.`tbl_name`:   7% 00:24 remain
Copying `db_name`.`tbl_name`:  12% 00:20 remain
Copying `db_name`.`tbl_name`:  18% 00:17 remain
Copying `db_name`.`tbl_name`:  23% 00:16 remain
Copying `db_name`.`tbl_name`:  29% 00:14 remain
Copying `db_name`.`tbl_name`:  34% 00:13 remain
Copying `db_name`.`tbl_name`:  39% 00:12 remain
Copying `db_name`.`tbl_name`:  45% 00:10 remain
Copying `db_name`.`tbl_name`:  48% 00:10 remain
Copying `db_name`.`tbl_name`:  53% 00:09 remain
Copying `db_name`.`tbl_name`:  58% 00:08 remain
Copying `db_name`.`tbl_name`:  64% 00:07 remain
Copying `db_name`.`tbl_name`:  69% 00:06 remain
Copying `db_name`.`tbl_name`:  75% 00:04 remain
Copying `db_name`.`tbl_name`:  80% 00:03 remain
Copying `db_name`.`tbl_name`:  85% 00:02 remain
Copying `db_name`.`tbl_name`:  91% 00:01 remain
Copying `db_name`.`tbl_name`:  96% 00:00 remain
2014-12-18T23:44:03 Copied rows OK.
2014-12-18T23:44:03 Swapping tables...
2014-12-18T23:44:03 Swapped original and new tables OK.
2014-12-18T23:44:03 Dropping old table...
2014-12-18T23:44:04 Dropped old table `db_name`.`_tbl_name_old` OK.
2014-12-18T23:44:04 Dropping triggers...
2014-12-18T23:44:04 Dropped triggers OK.
Successfully altered `db_name`.`tbl_name`.

まとめ

オンラインでの実行が前提だけあってセーフティなオプションがいろいろあります。 これらを利用すれば、本番環境のDBで実行する恐怖が少しは薄れるかなと思います。 ただし、ピーク帯の実行は極力控えましょう！

CyberAgent エンジニア Advent Calendar 2014の20日目は関西弁でお馴染みの@kakerukaeruです！お楽しみに！