圧縮MyISAMテーブルで商品マスターを運用する方法

商品マスターのように参照専門で利用するテーブルならば、圧縮MyISAMが非常に適していることが多い。その方が容量が小さくなるし、ディスクI/Oが減るので高速化が期待出来るからだ。圧縮MyISAMを利用する時の問題点は、MySQLサーバ起動中にテーブルの圧縮を行えない点であろう。（正確には行えなくもないが、操作は慎重を期する必要がある。）また、圧縮MyISAMテーブルはひとたび圧縮してしまった後は、更新を加えることが出来ないのだが、如何に商品マスターといえども、一日に一度程度の頻度で更新をかけないといけないかも知れないので、これまた問題である。圧縮MyISAMテーブルを用いた運用は利点がある一方で、このような問題があるため難しい。そこで、今日は圧縮MyISAMテーブルで商品マスターを運用する方法について紹介しよう。

商品マスター作成用のMySQLサーバを用意する。

オンライントランザクションを実行しているMySQLサーバーは、オンライントランザクションに特化した構成になっているので、データのローディングなどには適していないことが予想される。そのため、ローディング専用のサーバで作業を行うわけである。

作業用サーバーでは、MyISAMに特化したチューニングを施す。まず、key_buffer_sizeは多めに取ろう。また、delay_key_write=ALLを指定するとデータのローディングが速くなるのでお勧めである。LOAD DATA INFILEなどでデータをロードする時には、myisam_max_sort_file_sizeをかなり大きな値にしておくといいだろう。（100Gとか）myisam_sort_buffer_sizeも大きくしよう。

というわけで、作業用サーバーのmy.cnfには、次のような項目が書いてあると良い。

[mysqld]
...中略...
key_buffer_size=1G
delay_key_write=ALL
myisam_sort_buffer_size=512M
myisam_max_sort_file_size=100G

無用なトラブルを避けるため、MySQLは本番と同じバージョンのものを利用しよう。

商品マスターにデータをロードする

商品マスターテーブルを、CSVやタブ区切りのテキストデータを使って更新する場合には、LOAD DATA INFILEコマンドを利用するといいだろう。既に商品マスターテーブルが存在し、データを追加する場合にはLOAD DATA INFILEコマンドにおいてREPLACEオプションを使うといい。

mysql> LOAD DATA INFILE 'new.csv' REPLACE INTO TABLE shohin_master
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

このテーブルを圧縮しないで継続的に更新したい場合には、次のようにこのテーブルのコピーを作成するといいだろう。

mysql> CREATE TABLE shohin_master_tmp LIKE shohin_master;
mysql> INSERT INTO shohin_master_tmp SELECT * FROM shohin_master;

商品マスターテーブルを圧縮する

MyISAMテーブルの圧縮にはmyisampackコマンドを利用する。そのために、まずは作業用MySQLサーバーをシャットダウンしよう。

shell> mysqladmin -uroot -p shutdown

次に、myisampackコマンドでテーブルを圧縮する。

shell> cd /path/to/datadir/database_name
shell> myisampack shohin_master_tmp
shell> myisamchk -rq --sort-index --analyze shohin_master_tmp

圧縮したテーブルを本番サーバーへコピーする

この手順は少しテクニックが必要である。コピーするべきファイルを、データベースディレクトリと同じファイルシステム上にいったんコピーしてから、mvコマンドでファイルを移動するといい。mvコマンドはディレクトリのエントリを付け替えるだけなので、コピー中にMySQLサーバーからファイルが参照されてしまうというリスクを最小限に抑えることが出来る。また、ファイルのコピーは .frm ファイルを最後に行うというのを肝に銘じよう。.frm ファイルがなければ、.MYIおよび.MYDファイルが存在していても、MySQLサーバーはそれらのファイルを参照することはない。以下、本番サーバーにて作業。

shell> cd /path/to/datadir
shell> scp tmp-server:/path/to/datadir/database_name/shohin_master_tmp.* .
shell> mv shohin_master_tmp.MY* database_name/
shell> mv shohin_master_tmp.frm database_name/

すると、この時点で本番サーバーにおいてshohin_master_tmpというテーブルが見えるはずだ。

テーブルを入れ替える

次に、コピーしたテーブルをアプリケーションが参照出来るようにする。現時点ではテーブル名がshohin_master_tmpとなっており、アプリケーションはshohin_master_tmpテーブルではなくshohin_masterテーブルを参照していることだろう。テーブル名を付け替えるには、RENAME TABLEコマンドが便利である。RENAME TABLEコマンドを使えばイッパツでテーブル名を付け替えることが出来るのである。

mysql> RENAME TABLE shohin_master to shohin_master_old, shohin_master_tmp to shohin_master;

このように、一回のRENAME TABLEコマンドで操作を行うと、他のリクエストに割り込まれることなくテーブルを入れ替えることが出来る。この操作を行うにはテーブルに対して排他ロックが必要なので、一瞬参照系の処理はブロックされることになるが、コマンドは一瞬で完了するのでサービスへの影響は殆ど皆無であろう。

不要になったテーブル（shohin_master_old）は、万が一新しいテーブルに問題があった時のためにとっておくと良い。しばらく経ってからDROPするといいだろう。

まとめ

• MyISAMテーブルへデータをロードする場合には、MyISAMに特化したチューニングをする。
• MyISAMテーブルは、myisampackコマンドで圧縮する。
• myisampackコマンドを利用する時はMySQLサーバーを停止する。
• mvコマンドで、.MYD、.MYD、.frmの順番でファイルをコピー（移動）する。
• RENAME TABLEコマンドで一度にテーブルを入れ替える。