MySQLのレプリケーションとパーティショニングの設計例(4) - myisampack

さて、今回は前回までの以下の記事を踏まえた上で、もう活用しない古いデータを読み取り専用のテーブルに移行することで、テーブルの分散と処理の高速化を図ろうというお話。

前回までの内容で、以下のように、test_user__1～test_user__8までの８個のテーブル、およびtest_user_email_list、test_user_searchという、メールアドレス照合用、検索用のテーブルの計10個のテーブルを作成したのでした。

今回はさらに、以下のようなtest_deleteuserというテーブルを作成し、管理画面からのみ閲覧できるようにします。

話を簡単にするために、今回はtest_userテーブルの内容をそのままtest_deleteuserテーブルに移行し、test_user_email_list、test_user_searchにある同一ユーザーのレコードについては無視します。もしこっちも削除したい、という場合は最後のdelete_user_table.plをちょっと変更すればできますので、興味のある方はやってみてくだされ。

で、要は、test_deleteuser__Xという参照用の圧縮テーブルを作成し、定期的にtest_userの削除データをそちらに移行しよう、というわけです。

MySQLデータベース分散処理でも簡単に触れたとおり、今回は圧縮テーブルは参照系Slaveのみに生成し、更新系DBからは移行したデータはさっくり削除してしまいます。（管理画面に使っている参照系Slaveに作れば、その参照系以外の停止はありませんので、今回はその想定で話を進めます）

以下のような手順を取ることで、圧縮テーブルを生成する参照系Slave以外を停止せずに作業を行うことができます。

管理画面DBのSLAVEを停止する（管理画面のみアクセス不可とする）
管理画面DBでmyisamテーブルとして、deleteuser__Xを作成し、user__*から対象のデータをdeleteuser__Xに移行する（この時点ではuser__*からデータを削除しない）
管理画面DBのmysqldを停止し、データ移行が完了したdeleteuser__Xをmyisampackに変換する。
管理画面DBを再起動する（管理画面DBが止まっている間のレプリケーションが再度、稼働する）
管理画面DBにおいてdeleteuser__Xに移行した退会ユーザーを、更新系DBにてDELETEする（この時に、DELETE文をシェルスクリプトなどで、sleepをはさんで１レコードずつかけると、テーブルへのロックが最小限となり、ユーザー側のサービスへの影響が少なくなる）

下記に手順を示しますが、まずはこの順序をしっかり頭に入れておくことが重要なので、以下の図も合わせて参考にして頂いて、どのようなことをやっているのか頭の中で自分で再現できるまで、きっちり理解して頂ければ幸いです。

サーバー構成

手順追記

１．管理画面DBのSLAVEを停止する

mysql クライアントにて、下記を実行します。

mysql> stop slave;

２．test_deleteuser__1をmyisamテーブルとして生成し、test_user__*から対象のデータをtest_deleteuser__1へ移行する

CREATE TABLE test_deleteuser__1 (
  /** 中略 **/
) ENGINE=MyIsam DEFAULT CHARSET=utf8;

INSERT INTO test_deleteuser__1 SELECT * FROM test_user__1 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__2 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__3 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__4 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__5 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__6 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__7 WHERE status = 0;
INSERT INTO test_deleteuser__1 SELECT * FROM test_user__8 WHERE status = 0;

さらに手順５で移行されたレコードを更新系にて削除するため、test_deleteuser__1からuser_idをファイルに保存し、scpコマンドで更新系サーバーへ転送しておく。

$ mysql -u [ユーザー名] -p [スキーマ名] -e 'select user_id from test_deleteuser__1 ' > deleteuser_idlist-YYYYMMDD.txt
$ head -n 5 deleteuser_idlist-YYYYMMDD.txt
user_id
1
3
4
5
$ scp deleteuser_idlist-YYYYMMDD.txt [更新系のユーザー]@[更新系のホスト]:/tmp/

先頭にuser_idという文字が入っているがそれは取り急ぎは気にしなくてよいです。

３．管理画面DBのmysqldを停止し、test_deleteuser__1をmyisampackに変換する。

下記コマンドで、テーブルの圧縮とINDEXの再生成を行っています。これのどちらかを忘れると正常に動作しなくなりますので、ご注意ください。

$ sudo service mysqld stop 
$ cd /var/lib/mysql/[スキーマ名]/
$ sudo myisampack -v test_deleteuser__1
$ sudo myisamchk -rq --analyze --sort-index test_deleteuser__1.MYI

４．管理画面DBを再起動する。この時、一緒にレプリケーションも再開します。

$ sudo service mysqld start 
$ mysql -u user -p ... 
mysql> start slave;

５．手順２にて、test_deleteuser__1へ移行したレコードのDELETEを「更新系DBにて」行う

必ず更新系DBで行うようにしてください。そうすることで、更新系/参照系のtest_user__*からはそれらのレコードが削除され、管理画面DBのtest_deleteuser__1にのみ、該当のレコードが残ります。

手順２の時点で、user_id一覧を保存したファイルを更新系DBへと転送しているかと思います。

そのuser_idからDELETE文を生成して実行しますが、私の場合は以下のような単純なスクリプトを使ってDELETE文で一行ずつ削除しています。（まとめて削除をするとロック時間が長くなりすぎるため）

#!/usr/bin/perl
# delete_user_table.pl 

while(<>) {
  s/(^\s*|\s*$)//g;
  if (/^\d+$/) {
    $i=($_%8)+1;
    printf "delete from test_user__%d where user_id = %d LIMIT 1; \n",$i,$_;
  }
}

実際の削除はこんな感じです。

$ cat /tmp/deleteuser_idlist-YYYYMMDD.txt | perl ./delete_user_table.pl | mysql -u [ユーザー名] -p [スキーマ名] | tee ./deleteuser-YYYYMMDD.log

これで後はDELETE文の処理が終わるのをぼけーっと眺めている感じです。

ちなみにですが、更新系DBと参照系DBのレプリケーションの連携というのは、実際にはバイナリログによる更新SQL単位での連携となるために、管理画面DBにのみ存在するテーブルを生成できます。混乱しやすいので、念のため。

以上でmyisampackへの移行手順は終わりです。

さて、ふたを開けてみれば非常に単純な方法ですが、応用範囲は非常に広いと思います。

というのは、基本的には退会ユーザー以外にも、削除された日記やログなど、参照されることはあっても更新されることのないテーブル全般にこの方法が使える上、残ったテーブルのデータは当然、移行された分だけ小さくなりますので、サービスのクオリティ、負荷耐性の向上にもつながるからです。また、mergeテーブルには、myisamのテーブルとmyisampackのテーブルを混在させることも可能ですので、頭の使い方次第では非常に複雑なシステムを効率よく構築することができるかと思います。

余談ですが、こちらの方法は以下の本から得た知識が大きいです。