はじめての 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 に含まれる強力なツールです。

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

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

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

前提と環境

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 を調整すれば応用可能です。