memorycraft: EC2でMySQL(運用編 VPで無停止ALTER)

WEBサイトを運用していると、仕様の追加変更やパフォーマンス対策など、さまざまな理由で、テーブルの構造を途中で変更する必要が出てきます。そのような場合、通常は一時的にWEBサイトを停止して、メンテナンス期間中にDBにALTERをかけます。

ですが、そういった機会が頻繁に得られない場合などは、稼働中に変更しなければならないケースもあるかと思います。そこで、前回紹介したVPを利用して、無停止でテーブルにALTERをかけてみたいと思います。

初期のテーブルが以下の通りだとすると、

create table gift(
  id int auto_increment,
  gift_name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;

ここに削除日(deleted_at)を追加し、下記のように変更したいとします。

create table gift(
  id int auto_increment,
  gift_name varchar(255),
  description text,
  created_at datetime not null,
  deleted_at datetime,
  primary key(id)
)engine=InnoDB;

それでは、VPをつかって元のテーブルと新規テーブルを入れ替えてみます。
まず初期イメージは以下の通りです。

新しいスキーマのテーブルと、入れ替え用のダミーのテーブルを用意し、VPテーブルで新スキーマテーブルとダミーテーブルをつなげておきます。

mysql> create table gift_new(
   id int auto_increment,
   gift_name varchar(255),
   description text,
   created_at datetime not null,
   deleted_at datetime,
   primary key(id)
 )engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> create table gift_dummy like gift;
Query OK, 0 rows affected (0.01 sec)

mysql> create table gift_vp(
   id int auto_increment,
   gift_name varchar(255),
   description text,
   created_at datetime not null,
   primary key(id)
 )engine=vp
 comment 'table_name_list "gift_dummy gift_new", cit "2", cil "2", ctm "1", ist "1", zru "1"';
Query OK, 0 rows affected (0.01 sec)

ここで、VPテーブルには、Spiderと同様、基本設定以外にもさまざまなオプションがあり、ここでは、cit, cil, ctm, ist, zruというオプション値を指定しています。これらはそれぞれ以下のような意味があります。(マニュアルから抜粋)

・choose_ignore_table_list(cit)
　検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
　デフォルト値は指定なし。

・choose_ignore_table_list_for_lock(cil)
　ロック付検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
　更新は行われる。
　デフォルト値は指定なし。

・choose_table_mode(ctm)
　検索時に利用するテーブルの選択モード。
  サーバパラメータvp_choose_table_modeが設定されている場合は、
　そちらが優先される。
   0：最適化モード。
   1：「table_list」の前からの記載順に利用するテーブルを決定する。
　デフォルト値は 0

・infomation_source_table(ist)
　テーブルステータス取得のモード。
   0 ：全ての子テーブルからテーブルステータス取得を行う。
   1-：指定した子テーブルからのみテーブルステータス取得を行う。
　デフォルト値は 0

・zero_record_update_mode(zru)
　0件更新の際のモード。
   0：何もしない。
   1：choose_ignore_table_list_for_lockの対象子テーブルであった場合は、
      insertする。
　デフォルト値は 0

つまり、cit "2", cil "2", ctm "1", ist "1", zru "1"　という設定は、意味として、

VPテーブルに対して検索されたときにgift_newは使用しない
VPテーブルに対して検索されたときにgift_dummyを使用する
VPテーブルのステータスチェックにはgift_dummyのステータスを使用する
VPテーブルの更新対象が0件であった場合は、INSERTする

ということを示します。

次に、各テーブルをリネームします。 gift_dummyは必要なくなったのでgift_deleteに、元のgiftをVPの接続先であるgift_dummyに、VPテーブルのgift_vpをgiftに変更します。これにより、VPテーブルの接続先テーブルが、オリジナルのテーブルと新スキーマのテーブルに切り替わり、アプリケーションからのアクセスはVPテーブルに瞬時に切り替わります。

mysql> rename table 
  gift_dummy to gift_delete,  
  gift to gift_dummy,  
  gift_vp to gift;
Query OK, 0 rows affected (0.03 sec)

vp_copy_tables()を実行します。vp_copy_tablesはVPに付属されているUDFで、VPテーブルを介して、子テーブル間でデータコピーを行います。コピー元テーブルリストとコピー先テーブルリストのテーブルが、指定した親テーブルからのみ更新される場合、コピー中にそのVPテーブルに行われた更新もコピー先にコピーされるため、テーブルへの更新を止めずにコピーすることができます。

mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');

コピーが完了したら、テーブルのリネームを再度行います。新スキーマのgift_newをgiftに、giftだったVPテーブルをgift_vpに変更します。

mysql> rename table 
  gift to gift_vp,  
  gift_new to gift;

最後に、必要のなくなったテーブルを削除します。

mysql> drop table 
  gift_dummy,
  gift_vp, 
  gift_delete;

こうして、データアクセスをさせたまま、テーブル定義の変更とデータ移行をすることができました。
本日はここまで。

2011年9月15日木曜日

EC2でMySQL(運用編 VPで無停止ALTER)