January 29, 2005

MySQL の MERGE テーブルで巨大なテーブルを効率的に運用する

[ Linux & UNIX ]

MySQL の MyISAM テーブルの最大サイズは通常4GBです。(OSのファイルシステムの制限が影響する場合はその限りではありません。) あるテーブルの最大サイズを調べるには、show table status を使ってみることができます。

mysql> show table status like 'feed' \G
*************************** 1. row ***************************
           Name: feed
           Type: MyISAM
     Row_format: Dynamic
           Rows: 201456
 Avg_row_length: 189
    Data_length: 38121168
Max_data_length: 4294967295
   Index_length: 6854656
      Data_free: 0
 Auto_increment: 201460
    Create_time: 2004-07-01 08:37:15
    Update_time: 2005-01-29 09:32:09
     Check_time: 2004-07-01 08:37:23
 Create_options:
        Comment:
1 row in set (0.00 sec)

このパラメータのうち Max_data_length が最大サイズになります。4GB 以上のデータを格納したい場合は create table 時に MAX_ROWS と AVG_ROW_LENGTH を明示的に指定する必要があります。

create table mytable (
  ...
  ...
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;

この場合 32GB のデータを格納することができます。MAX_ROWS と AVG_ROW_LENGTH は ALTER 文で修正をかけることもできるので、後日 4GB 越えのデータになってしまったなんてときでも対応することが可能です。

さて、4GB を超えるような巨大なテーブルですが、そのすべてのレコードに対して insert / update がかかるのでなければ、言い換えると、古いデータは select だけが走るようなテーブルの場合は、上記の方法でテーブルサイズを広げるよりも良い方法があります。

MySQL には MERGE テーブル (MRG_MyISAMテーブル) という機能(テーブル型)があります。MERGE テーブルはその名の通り、複数のテーブルをマージしたテーブル。複数のテーブルをまとめて、仮想的に一つのテーブルとして扱うことができるという便利なテーブル型です。

例えば A、B という二つのテーブルがあって、それぞれのテーブルのスキーマが同一である場合、C という MERGE テーブルを作成し A と B をまとめて扱います。するとプログラムからは、C を検索するだけで A と B 両方から検索することができます。更新クエリは A と B どちらに発生させるかを指定することができます。

これにより、例えばアクセスログのような、古いデータには更新が発生しないようなテーブルを分割して扱うことができるようになります。また、MERGE テーブルは myisampack で圧縮したテーブルも扱うことができます。圧縮テーブルはディスクの容量を減らすことができる反面、書き込みが不可能になるという制限があります。

古いテーブルは圧縮してしまい、更新クエリが発生するテーブルのみを非圧縮で用意します。これらのテーブルを MERGE テーブルでまとめて、更新クエリは非圧縮テーブルに発生するよう指定してやると、かなりのディスク容量を削減することができます。また、各テーブルは別ファイルとして作成されており、別々のディスクに配置することも可能なので I/O を分散することができ、処理速度を向上させることができます。

更に、更新クエリは分割されたあるテーブルにのみ発生するので、各々のテーブルにはテーブルロックがかかりません。加えて、MERGE テーブルとしてまとめた後も個々のテーブルはそれまでどおり単一のテーブルとして検索することができるので、ある特定の時期の古いデータだけが欲しい場合は、単一のテーブルを検索することで検索範囲を絞り、効率の良い検索を実行することができます。

実は昨日 FeedBack の RSS を保存しているデータベースが 4GB を突破してしまい、急遽 MERGE テーブルを作って対処しました。しばらくダウンさせてしまいましたが、現在は無事に復旧しています。(なお、現在過去のデータが検索にひっかかりにくいのはこれとは別の原因です。)

ということで、そのときやった作業内容を以て MERGE テーブルの作り方です。

まず、MERGE テーブルを作る際の方針を考えます。4GB に到達したテーブルは entry というテーブルです。プログラムからはこれまで同様 entry という名前で MERGE テーブルにアクセスしたいので、

  1. entry を entry_1st というテーブルにリネームする
  2. entry_1st を myisampack で圧縮する
  3. 新しく entry_2nd というテーブルを作る
  4. MERGEテーブルで entry テーブルをつくり entry_1st と entry_2nd を扱う
  5. 更新クエリは entry_2nd に発生するよう指定する

という手順で作業を行います。

entry を entry_1st にリネームするのは簡単で、MyISAM テーブルの場合ファイル名をかえるだけ。MySQL のデータディレクトリに entry.frm、entry.MYI、entry.MYD という三つのファイルがあるのでそれぞれを entry_1st.* にリネームします。

次に myisampack での圧縮です。これも簡単で、コマンドラインから以下のように実行するのみ。

$ sudo myisampack -v entry_1st

結構時間がかかります。注意しなければいけないのは、マニュアルに

pack_isam または myisampack を実行後、isamchk または myisamchk を実行してインデックスを再生成する必要があります。このとき、MySQL オプティマイザの効率性を上げるために、インデックスブロックをソートし、統計を作成しておくこともできます。

とある通り、myisampack は実行した後に myisamchk を実行してインデックスを再生成しなけばいけません。実はこれをすっとばしていて、後に MERGE テーブルで検索したとき

mysql> select id from entry order by id limit 10; 
ERROR 1030: Got error 124 from table handler

なんて見慣れないエラーが出てはまりました。

ということで myisampack で圧縮したら myisamchk を実行、これ必須。

$ sudo myisamchk -rq --analyze --sort-index entry_1st.MYI

テーブルがでかいと圧縮とインデックス再生成に結構時間がかかるので注意。この辺の作業をするとなると MERGE テーブルを作る際は、サービスを停止する必要がありますね。myisampack での圧縮をしなければ、無停止か数分の停止で済むと思います。

これで圧縮テーブルの entry_1st が用意できました。次は新しいテーブルを作成します。新しいテーブルはテーブル型を MyISAM で、もうスキーマは entry_1st と同じにする必要があります。

$ mysqldump -ufoo -p -d feedback > feedback.sql

とすれば、feedback データベースのスキーマを書き出すことができます。この中の entry_1st テーブルの create table 文を、コピーして entry_2nd テーブルの作成文に変更して実行しました。

これで下準備は完了です。あとは MERGE テーブルを作るだけ。MERGE テーブルも、スキーマは entry_1st と同じである必要があります。先の sql ファイルからまたもコピペします。

CREATE TABLE entry (
  ...
) TYPE = MRG_MyISAM UNION = (entry_1st,entry_2nd) INSERT_METHOD = LAST

途中省略して、最後の行が重要です。TYPE = MRG_MyISAM で MERGE テーブル型を指定します。また UNION でどのテーブルを扱うのかを指定。最後に INSERT_METHOD で指定したテーブルのうち、どれに書き込むかを指定します。(FIRST か LAST を指定できます。)

これで MERGE テーブルの作成は完了。entry テーブルに対して検索して、これまでどおり検索ができたら ok です。

もちろん MERGE テーブルには 3 つ以上のテーブルも指定できますし、手順は一緒です。

ちょっと気になっていたのが各テーブルのプライマリ・キーです。entry テーブルのプライマリ・キーは id という名前で int の auto_increment なカラムになってます。MERGE で指定したテーブルの auto_increment の値はどうなってしまうんだろう、entry_2nd はまた 0 から開始なの? それとも entry_1st の続きになるのか。また 0 から開始だと、entry テーブルにおいてプライマリ・キーの一意性が確保できなくなるので、使い物になりません。

結果、entry_2nd は entry_1st の続きから increment してくれました。ほっ。ただ、もともと entry_2nd にもデータが入っていてそのプライマリ・キーが entry_1st のデータと被っている場合などは注意が必要で、MERGE テーブル作成時に PRIMARY KEY 指定をやめるとかしないといけないそうです。

長くなりましたが、MERGE テーブルの運用はこんな具合で可能です。MERGE テーブルを使うと個々のテーブルのサイズを小さくとどめた上で巨大なテーブルを作成することができるので、可能性が結構広がります。

たとえば、WEB+DB PRESS に載っていたのですが、Seesaa Blog では MERGE テーブルを使ってアクセスログを管理していて、月ごとに新しいテーブルを作っているそうです。月ごとに新しいテーブルを用意するという方針でテーブルを追加していけば、ある特定の月のデータだけを検索したいときは MERGE テーブルではなく、個別のテーブルを検索することで、データ件数を最小限にとどめた検索が可能になります。

他にも、テーブルサイズを小さくすることができるという利点を活かして、更新クエリ用テーブルはメモリに収まる範囲内にとどめるよう運用努力をし、オンメモリでそのデータを持つと言うことも可能です。更新系クエリがメモリに収まることでディスクI/Oによる遅延を回避することができ、テーブルロックに要する時間を短縮することができるので、パフォーマンスが大幅に向上するでしょう。

ということで、たまには MySQL の運用について書いてみました。今 DBMagazine に、はてなのデータベース運用についての記事も執筆中です。

ちなみに、今回書いた myisampack の話や MERGE テーブルの話なんかは実践ハイパフォーマンスMySQL に詳しく記述されています。すべての MySQL ユーザーにおすすめの一冊です。

実践ハイパフォーマンスMySQL
ジェレミ・D. ザウドニ デレク・J. ベリング Jeremy D. Zawodny Derek J. Balling 林 秀幸
オライリージャパン (2004/10)
売り上げランキング: 10,630
通常24時間以内に発送

# MySQLカテゴリは用意してないので Linux & UNIX カテゴリにいれておきます。

Posted by naoya at January 29, 2005 11:46 AM | トラックバック (1)  b_entry.gif
トラックバック [1件]
TrackBack URL: http://mt.bloghackers.net/mt/suck-tbspams.cgi/1448
http://yamipro.ameblo.jp/entry-f416e9867d00c52431f62b8e9409097a.html
Excerpt: 尊敬するNDO::WeblogさんでMySQLでの巨大テーブルの効率的な運用方法が掲載されていました。 「FeedBack」を作られたnaoyaさんのBL...
Weblog: 闇プロジェクト::zoe
Tracked: January 29, 2005 02:16 PM
コメント [0件]
コメントする









名前、アドレスを登録しますか?