2011年9月15日木曜日

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;



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