Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@uturned0(Yasunori Kuji)

はじめての Percona Toolkit - pt-online-schema-change

Last updated at Posted at 2025-09-01

はじめての Percona Toolkit

現在、大学院で卒論研究を進めており、MySQLサーバーで大量のデータ収集を行っています。しかし、長年使い続けている MacBook 2008 をサーバーにしているため、負荷が非常に高い状況です。そんな中、MySQL のあるカラムタイプを VARCHAR(64) から DATETIME に変更する必要があることが判明しました。AIさんが timestamp と名のつくカラムを VARCHAR にしてるなんて気づかなかったよ😭 人間はそんな観点のレビューしないんだよ 😭

サービスを停止することなくこのスキーマ変更を完了させるため、Percona Toolkit に挑戦することにしました。

Percona Toolkit - pt-online-schema-change とは？

pt-online-schema-change(pt-osc) は、データベースの可用性を損なうことなく大規模な MySQL テーブルのスキーマ変更を可能にする、Percona Toolkit に含まれる強力なツールです。

このツールは、以下のステップでスキーマ変更を安全に実行します。

  1. まず、オリジナルのテーブルと同じ構造を持つ新しいテーブルをバックグラウンドで作成します。
  2. 次に、その新しいテーブルに対して、目的の ALTER TABLE 文を実行します。
  3. オリジナルのテーブルにトリガーを作成し、スキーマ変更中に発生したすべての INSERT/UPDATE/DELETE を新しいテーブルにも適用します（両テーブルの同期を維持）。
  4. オリジナルテーブルから新しいテーブルへ、行データをチャンク単位で少しずつコピーします。
  5. 最後に、テーブル名を原子的に入れ替える（RENAME TABLE）ことで、新しいテーブルが本番で使われるようになります。
  6. 古いテーブルは後で削除されます。

ポイント：アプリからの読み書きは原則ブロックされません（短いメタデータロックはあり）。

前提と環境

  • MySQL: 5.7 系
  • 対象テーブル: ct.certs
  • 変更: ct_log_timestamp を VARCHAR(64) → DATETIME NULL
  • ワークロード: INSERT 多め（UPDATE なし）
  • レプリケーション: DR 用スレーブは停止中（復帰は ALTER 完了後）
  • pt-osc 実行: 別マシンから MySQL に接続（LAN 内）。pt-osc は SQL をリモート送信するだけで、データ本体は MySQL サーバ内でコピーされる（クライアントへ 25GB が流れることはない）。
    ↓ こんな感じのクエリを投げるらしい
INSERT INTO _certs_new ( ... ) 
  SELECT ... FROM certs WHERE id BETWEEN X AND Y;

事前確認と安全策チェックリスト

1) テーブルサイズの把握（I/O所要の見積もり）

SELECT
  TABLE_SCHEMA, TABLE_NAME,
  DATA_LENGTH, INDEX_LENGTH,
  (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_BYTES
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'ct' AND TABLE_NAME = 'certs';

  • 実測（作業時点）:

    • DATA_LENGTH ≈ 23.9 GB (10^9 基準) ≈ 22.28 GiB
    • INDEX_LENGTH ≈ 1.42 GB (10^9 基準) ≈ 1.32 GiB
    • 合計 ≈ 25.34 GB (≈ 23.60 GiB)

目安：オンラインコピー（pt-osc）はこの 全量を読み書きするため、ディスク I/O が所要時間の支配要因。BINLOG も増えるのでストレージ空きは多めに。

ディスク空き容量（必須）

  • 目安：テーブルサイズ（DATA+INDEX）の少なくとも 2 倍の空きが必要。
    • +1× … pt-osc が作る 影テーブル（_new） の全量コピー。
    • +1× … バイナリログ（binlog） にチャンクコピーとトリガ適用分の DML が記録される（ROW/MIXED では実データ量に近い）。
  • 余裕があれば 2.5×（UNDO/REDO、テンポラリ、OS キャッシュの揺れ）を推奨。

計算式

必要空き容量 ≈ (DATA_LENGTH + INDEX_LENGTH) × 2.0 ～ 2.5

例（今回）

TABLE SIZE ≈ 25.34 GB → 推奨空き ≈ 50 ～ 63 GB

今回は130GBの空きがあったので大丈夫だった。実データの２倍(25GBなら50GBを足して計75GB使う)というのはなかなか厳しいと思った。

2) INSERT レート（rps）の計測

  • 全体（InnoDB 行ベース）
SELECT @t:=NOW(), @v:=VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME='Innodb_rows_inserted';
DO SLEEP(10);
SELECT (VARIABLE_VALUE-@v) / TIMESTAMPDIFF(SECOND, @t, NOW()) AS rows_per_sec
FROM performance_schema.global_status
WHERE VARIABLE_NAME='Innodb_rows_inserted';
  • テーブル近似（AUTO_INCREMENT の増分）
SELECT @t:=NOW(), @ai:=AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='ct' AND TABLE_NAME='certs';
DO SLEEP(10);
SELECT (AUTO_INCREMENT-@ai) / TIMESTAMPDIFF(SECOND, @t, NOW()) AS approx_rows_per_sec
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='ct' AND TABLE_NAME='certs';
  • 計測結果（最大）: ~2.8 rows/sec

rps が低めなので、pt-osc のトリガによる追随オーバーヘッドは小さい。主に I/O を見ればOK。

3) 互換性と実行アルゴリズム

  • 対象列 ct_log_timestamp は 全て NULL → 型変換の互換性リスクは実質ゼロ。
  • MySQL 5.7 は INSTANT なし。通常 ALTER は COPY だが、pt-osc は トリガ＋チャンクコピー＋RENAME で可用性を確保。

4) レプリケーションと BINLOG

  • スレーブ停止中のため --max-lag は使用せず。復帰は ALTER 完了後に実施。
  • binlog 保持：スレーブ停止中に binlog が自動削除されると追従不可に。
-- 作業前に期限を延長（例）
SET GLOBAL expire_logs_days = 14;  -- 永続化は my.cnf 側でも
SHOW BINARY LOGS; -- 増え方を監視
  • binlog_format は ROW 推奨（安全）。
SHOW VARIABLES LIKE 'binlog_format';

5) セッション/サーバ設定（参考）

-- sql_mode / time_zone の確認（ノード間で統一）
SELECT @@GLOBAL.sql_mode, @@sql_mode, @@GLOBAL.time_zone, @@time_zone;

Dry-run（シミュレーション）

まずは --dry-run でシミュレート。新テーブル作成〜ALTER までが問題なく通るか、ログで確認。

pt-online-schema-change \
  --alter "MODIFY COLUMN ct_log_timestamp DATETIME NULL" \
  --chunk-index=PRIMARY \
  --chunk-time=0.5 \
  --nocheck-unique-key-change \
  --set-vars "lock_wait_timeout=5,innodb_lock_wait_timeout=5" \
  --dry-run \
  h=mb-3.local,D=ct,t=certs,u=root,p=

出力抜粋

Operation, tries, wait:
  analyze_table, 10, 1
  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.  `ct`.`certs` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table ct._certs_new OK.
Altering new table...
Altered `ct`.`_certs_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.
2025-09-01T22:43:27 Dropping new table...
2025-09-01T22:43:27 Dropped new table OK.
Dry run complete.  `ct`.`certs` was not altered.

実行（バックグラウンド + ログ）

ターミナル切断を避けるため nohup。もしくは tmux/screen もおすすめ。

nohup pt-online-schema-change \
  --alter "MODIFY COLUMN ct_log_timestamp DATETIME NULL" \
  --execute \
  --chunk-index=PRIMARY \
  --chunk-time=0.5 \
  --nocheck-unique-key-change \
  --set-vars "lock_wait_timeout=5,innodb_lock_wait_timeout=5" \
  h=mb-3.local,D=ct,t=certs,u=root,p= \
  > ptosc_certs.log 2>&1 &

tail -f ptosc_certs.log

進捗ログの例

残り時間が出るの めっちゃ親切 ☺️☺️☺️

$ tail -f ptosc_certs.log
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `ct`.`certs`...
Creating new table...
Created new table ct._certs_new OK.
Altering new table...
Altered `ct`.`_certs_new` OK.
2025-09-01T22:45:34 Creating triggers...
2025-09-01T22:45:34 Created triggers OK.
2025-09-01T22:45:34 Copying approximately 1334501 rows...
Copying `ct`.`certs`:   1% 33:44 remain
Copying `ct`.`certs`:   2% 33:41 remain
^@Copying `ct`.`certs`:   4% 34:07 remain
Copying `ct`.`certs`:   5% 34:48 remain
^@Copying `ct`.`certs`:   6% 35:03 remain
Copying `ct`.`certs`:   7% 34:45 remain
^@Copying `ct`.`certs`:   9% 34:24 remain
Copying `ct`.`certs`:  10% 34:15 remain
^@Copying `ct`.`certs`:  11% 34:02 remain
Copying `ct`.`certs`:  12% 33:46 remain
^@Copying `ct`.`certs`:  14% 33:22 remain
Copying `ct`.`certs`:  15% 33:05 remain
^@Copying `ct`.`certs`:  16% 32:54 remain
Copying `ct`.`certs`:  17% 32:34 remain
^@Copying `ct`.`certs`:  18% 32:05 remain
Copying `ct`.`certs`:  20% 31:30 remain

--chunk-time は安全に振るほど（小さくするほど）所要時間は延びます。負荷に余裕があれば 0.5 → 1.0 に調整。

モニタリング項目（実行中）

  • pt-osc ログ：コピー進捗、ETA、トリガ作成/削除、エラー
  • MySQL：Threads_running、SHOW ENGINE INNODB STATUS\G の I/O、Innodb_buffer_pool_reads
  • BINLOG：SHOW BINARY LOGS;（容量の増え方）
  • OS：ディスク待ち（iostat）、空き容量

切断対策：tmux/screen だと対話的に安全。nohup でもログは必ずファイルへ。

完了後の確認と後片付け

-- 列型が変わっていること
SHOW CREATE TABLE ct.certs\G

-- 期待どおり NULL のまま（今回の前提）
SELECT COUNT(*) FROM ct.certs WHERE ct_log_timestamp IS NOT NULL;
  • 残骸（_certs_new や pt_osc_* トリガ）が無いことを確認。
  • DR スレーブの起動：START SLAVE; → SHOW SLAVE STATUS\G で catch-up を監視。binlog が不足したら再シード。

終盤の挙動とスワップ（“99% 00:00 remain” から約30分）

pt-osc の終盤は、ログ上は 99% 00:00 remain のままでも、取りこぼしレンジの穴埋め（copy nibble） を細かく繰り返します。SHOW FULL PROCESSLIST を観察すると、以下のようなクエリが 少しずつ範囲を進め ていくのが見えます。

-- 例：範囲が徐々に前進する
INSERT LOW_PRIORITY IGNORE INTO `ct`.`_certs_new` (...)
SELECT ... FROM `ct`.`certs` FORCE INDEX(`PRIMARY`)
WHERE (`id` >= '2569904') AND (`id` <= '2570245')
LOCK IN SHARE MODE /* pt-online-schema-change copy nibble */;

-- 次チャンクの境界探索（終盤はごく小刻み）
SELECT /*!40001 SQL_NO_CACHE */ `id`
FROM `ct`.`certs` FORCE INDEX(`PRIMARY`)
WHERE (`id` >= '2128033')
ORDER BY `id` LIMIT 228, 2 /* next chunk boundary */;

この“穴埋め”が MAX(id) に到達し、_certs_new と certs の先頭～末尾が揃うと、次のフェーズ（Analyze → Swap）に進みます。今回の実行では、99% 表示のあと およそ30分 で以下の順に進行しました。

2025-09-02T00:16:10 Copied rows OK.
2025-09-02T00:16:10 Analyzing new table...
2025-09-02T00:16:10 Swapping tables...
2025-09-02T00:16:31 Swapped original and new tables OK.
2025-09-02T00:16:31 Dropping old table...
2025-09-02T00:16:33 Dropped old table `ct`.`_certs_old` OK.
2025-09-02T00:16:33 Dropping triggers...
2025-09-02T00:16:34 Dropped triggers OK.
Successfully altered `ct`.`certs`.

スワップ中に発生したデッドロック（API 側）

スワップ直前～直後に、アプリ側の INSERT が Deadlock found when trying to get lock; try restarting transaction（ER_LOCK_DEADLOCK 1213） を多数吐きました。これは、

  • pt-osc の最終フェーズでの 短時間のロック競合（RENAME に伴うメタデータロックの取得、直前の差分反映）
  • あるいはトリガ適用とアプリ INSERT の ロック順序の不一致

が原因で一時的に起こり得るものです。対処：

  • スワップ直前だけ API を 30～60 秒停止（未コミットは必ず COMMIT）。
  • アプリ側で 1213 をリトライ（指数バックオフで 2～3 回）。
  • 可能なら トランザクションを短く、オートコミット ON。

メモ：lock_wait_timeout は行ロック待ちのタイムアウトで、デッドロック検出（1213）とは別です。デッドロックは即時に一方がロールバックされます。

まとめ

  • VARCHAR(64) → DATETIME の直接 ALTER は 5.7 だとテーブル全コピー＆長時間ブロックになりがち。
  • pt-online-schema-change はトリガとチャンクコピーで 可用性を保ちながら安全に移行できる。
  • 今回は 全 NULL という条件により変換エラーのリスクは低く、I/O と BINLOG の管理に集中できた。
  • 実行前の サイズ見積もり と INSERT レート計測（最大 ~2.8 rps）、binlog 保持期間 の確認が、安心運用の肝になった。

参考スニペットはすべてこのページのコードブロックに保存。状況に応じて --chunk-time や --max-lag を調整すれば応用可能です。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?