EC2でMySQL(運用編 VP＋Spiderで無停止負荷分散)

前回はVPでテーブルのALTERを行いましたが、ほとんど同様に通常のInnoDBテーブルをSpiderシャーディングに無停止で移行することも可能です。 さっそくやってみます。 初期テーブルは以下のとおりです。

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

これをSpider化していきますが、今回は定期的にデータを投入しながらSpider化を行ってみます。 まず、以下のようなシェルを実行し、常にデータの投入がされている状態をつくります。

$ vi insert2gift.sh

$ sh insert2gift.sh
................................

それではVPをつかって無停止でSpiderに移行を行なってみたいと思います。 基本的には、前回と同様です。 切り替え用のダミーと２つのデータノードをもつ新規Spiderテーブル、それらを束ねるVPテーブルを用意します。


データノードの設定

mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@localhost IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'123.123.123.123' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> use cloudpack;
Database changed

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

SpiderノードのSpider、ダミー、VPテーブルの設定

mysql> create table gift_new(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "gift", user "xxxxxxxxxxxxx", password "xxxxxxxxxxxxx" '
PARTITION BY LIST(MOD(id, 2)) (
    PARTITION hostb VALUES IN (0) comment 'host "111.111.111.111", port "3306"',
    PARTITION hostc VALUES IN (1) comment 'host "222.222.222.222", port "3306"'
);
Query OK, 0 rows affected (0.02 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,
   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.03 sec)

この時点では、以下のイメージのような構成になっています。

テーブルのリネーム

ここも前回同様テーブルのリネームを行い、接続先を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)

データのコピー

ここも前回同様です。

mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');
Query OK, 0 rows affected (0.01 sec)

テーブルの再リネーム

コピーが完了したら、テーブルのリネームを再度行い、移行先のSpiderテーブルをgiftにします。

mysql> rename table 
  gift to gift_vp,  
  gift_new to gift;
Query OK, 0 rows affected (0.01 sec) 

不要テーブルの削除

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

mysql> drop table 
  gift_dummy,
  gift_vp, 
  gift_delete;
Query OK, 0 rows affected (0.00 sec)

これで、移行が完了しました。 それでは、実際にテーブルの内容を見てみましょう。

host A

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
5007 rows in set (0.06 sec)

host B

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
+------+----------------------------------+----------------------------------+---------------------+
2503 rows in set (0.01 sec)

host C

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
2504 rows in set (0.00 sec)

このように、元々入っていたデータと、移行中に投入されたデータがきれいにシャーディングされていることがわかります。
本日はここまで。

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;

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

EC2でMySQL(VP編 VPってなんじゃ？)

今日はVPの話です。VPとはVertical Partitioning（垂直パーティショニング）の略で、Spiderと同じく斯波建徳さんの作ったMySQLのストレージエンジンです。Spiderの姉妹品といったところでしょうか。
このVPは、１つのテーブルを複数のテーブルにカラム分割します。

イメージとしては以下のような形です。

VIEWに似ていますが、

• INSERTできる
• 複数の分割先のテーブル（子テーブル）でカラムの重複が可能。

などの点で、VIEWと異なりますが、
これだけの違いでさまざまなことが可能になります。

VPもSpiderと同じくCommentを利用して設定します。
インストール方法は、Spider編1で紹介したSpiderのバイナリにVPも含まれているので、そちらを参照してください。
まずは試してみましょう。
たとえば以下のようなテーブルがあるとすると、

create table gift(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=InnoDB;

は、以下のように分割できます。

create table gift_key(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=InnoDB;

create table gift_detail(
  id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id)
)engine=InnoDB;

create table gift(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=VP
comment 'table_name_list "gift_key gift_detail"';

ここでVP化したgiftテーブルにデータを投入してみます。

mysql> INSERT INTO gift (shop_id, country_id, gift_type, gift_name, description, created_at)
    -> VALUES (1, 103, 1, 'ハム', 'ハムの人になれます。', CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.03 sec)

普通にINSERTできました。
また、それぞれの子テーブルに重複したカラムも値が投入され、かつgiftテーブルから１つのテーブルのようにSELECTできることがわかります。

mysql> select * from gift_key;
+----+---------+------------+-----------+-----------+
| id | shop_id | country_id | gift_type | gift_name |
+----+---------+------------+-----------+-----------+
|  1 |       1 |        103 |         1 | ハム    |
+----+---------+------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> select * from gift_detail;
+----+-----------+-----------+--------------------------------+---------------------+
| id | gift_type | gift_name | description                    | created_at          |
+----+-----------+-----------+--------------------------------+---------------------+
|  1 |         1 | ハム    | ハムの人になれます。 | 2011-09-12 22:10:16 |
+----+-----------+-----------+--------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from gift;
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
| id | shop_id | country_id | gift_type | gift_name | description                    | created_at          |
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
|  1 |       1 |        103 |         1 | ハム    | ハムの人になれます。 | 2011-09-12 22:10:16 |
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
1 row in set (0.00 sec)

今後いくつかVPの活用の仕方を紹介できればと思います。

以上です。