pt-online-schema-change 実行中のパフォーマンス

CREATE TABLE IF NOT EXISTS tweet (
    tid  INT(10)       AUTO_INCREMENT,
    body VARCHAR(255),
    PRIMARY KEY (tid)
)Engine=InnoDB DEFAULT CHARSET=utf8;

上のようなテーブルを作ってデータを200万件投入した状態でパフォーマンス測定を実施してみた。

データを投入するスクリプトを用意。

#!/usr/bin/env perl

use strict;
use warnings;
use utf8;

use DBI;
my $limit = $ARGV[0] ? $ARGV[0] : 100; # データ投入件数を決定。デフォルト100件

my $dbs =join(":", qw/DBI mysql foo localhost/);
my $user = 'user';
my $pass = 'password';
my $dbh = DBI->connect($dbs, $user, $pass);
my $ins = 'INSERT INTO tweet (body) VALUES (?)';
my  $sth =$dbh->prepare($ins);
for (my $i = 1; $i <= $limit; $i++) {
    print $i."\n" if $i % 1000 == 0;
    $sth->execute($i);
}
$sth->finish;
$dbh->disconnect;

これを insert.pl として保存。

1) 1万件のデータインサート

比較として1万件のデータインサートだけを実行した場合の実行時間を測定。

% time perl insert.pl  10000  
perl insert.pl 10000  2.23s user 0.73s system 0% cpu 2min44.99sec

2) 通常のALTER TABLE文

1万件のデータインサート実行中に 下のALTER TABLEを実行した場合の実行時間を測定

ALTER TABLE tweet Engine=InnoDB;

mysql> ALTER TABLE tweet Engine=InnoDB;
Query OK, 2000000 rows affected (1 min 54.24 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

3) online-schema-change

1万件のデータインサート実行中に 下のコマンドを実行した場合の実行時間を測定

% /usr/bin/pt-online-schema-change --alter 'ENGINE=InnoDB' D=foo,t=tweet,h=localhost,u=root --ask-pass  --execute

% /usr/bin/pt-online-schema-change --alter 'ENGINE=InnoDB' D=foo,t=tweet,h=localhost,u=root --ask-pass  --execute
Enter MySQL password: 
Altering `foo`.`tweet`...
Creating new table...
Created new table foo._tweet_new OK.
Altering new table...
Altered `foo`.`_tweet_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 1850111 rows...
Copying `foo`.`tweet`:  24% 01:30 remain
Copying `foo`.`tweet`:  44% 01:15 remain
Copying `foo`.`tweet`:  64% 00:49 remain
Copying `foo`.`tweet`:  85% 00:21 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `foo`.`_tweet_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `foo`.`tweet`.

測定結果

測定作業	実行時間
1) の実行にかかった時間	2min44.99sec
2) の実行にかかった時間	4min05.62sec
3) の実行にかかった時間	4min59.34sec

何回かテストした結果、online-schema-change使ったほうが通常のALTER文の実行よりも実行時間が長い傾向があった。
ギリギリのパフォーマンスでDBを運用している際はやっぱりALTER TABLEを使うのは厳しい感じだね。

今回の検証はさくらVPSで行ったので環境変えればもしかしたら同じぐらいの実行速度でクエリを実行できるかも（期待薄だけど）。