2007年10月28日日曜日

パーティショニングによるデータベースのパフォーマンスチューニング

パーティショニンングによるデータベースのパフォーマンスチューニングを勉強した。

パーティショニングが必要な理由


MySQL では DB 上のデータ量が増加するとパフォーマンスが低下する。
以下が原因。

1. MySQL では 1度アクセスされたデータやインデックスは効率化のためにバッファにキャッシュされる。
2. データ量が増大すると、メモリ上のキャッシュサイズでは不足。
3. ディスク I/O が増大し、パフォーマンス低下へ。

テーブルや DB を分割(パーティショニング)し、アクセスされるデータがメモリ上に収まる状態をキープすることで対策する。
パーティショニングは 2 アプローチが考えられる。

1. アプリケーション側で実装
2. MySQL のサポート機能を用いて実装


アプリケーション側でのパーティショニング実装


レプリケーションは参照系の負荷分散にはなるが更新系の負荷分散の解決にはならない。
⇒ DB のパーティショニングで解決。パーティショニングアプローチには以下の 3種類が考えられる。

1. データの鮮度でパーティショニング
直近のものは参照頻度が高い。直近のものを保持するテーブルを用意する。
(バッチで順に古い DB へデータを移行)

2. データの種類でパーティショニング
ブログ記事はサーバ A, コメント記事はサーバ B みたいな感じ

3. 論理的なデータ構造でパーティショニング
アルゴリズムを決めてユーザごとにユーザテーブルを決定するなど (テーブルサイズが小さくなる)。

MySQL のパーティショニング機能での実装


MySQL 5.1 では β 版だがパーティショニングがサポートされている。

  • コンパイル時に -with-partition オプションをつける

  • ルールを設定して分割させる。
    • データの値の範囲で分割 (100 以上は A みたいな感じ)
    • データの値別に分割 (2,4,6 は A, 3,5,7 は B みたいな感じ)

    パーティショニングを実装したときの問題点


  • Join ができない。
  • ⇒ 複数回 SQL を発行して解決。O/R マッパを使用してプログラムの見通しを良くすること。

  • 全体での新着表示ができない。
  • ⇒ 全体テーブルを用意。Gearman などの非同期処理で全体テーブルを随時更新。