目的なしの分割テーブル
TABLE が数千万行のサイズになると、運用時間の制約が厳しくなってきます。
- デフォールト値ありのカラム追加
- インデックス追加・削除
- TABLE単位のdump
設計段階で大きくなることがわかっていると、PostgreSQLのパーティショニング機能を使って、扱い易いサイズにしようとなります。
Let'sPostgresの記事 に性能上100分割まで、と書いてあるから、mod(id, 100) で分別しようか？ と安易にやると、パーティショニングのデメリットが強く出てきて、割に合いません。
デメリット
- ちょっとしたクエリでも性能が劣化する
- クエリでいつも id が一つずつならいいのですが、範囲指定や、IN での複数指定だと、SQLも最大100個生成されて、最大100個テーブルが検索対象になります。全体計算量が変わらず、分割したオーバーヘッドの分だけ遅くなります。
- 性能バグの温床になる
- id一つ指定のクエリ、id=12345についてのクエリは
WHERE mod(id,100)=mod(12345,100) AND id=12345 AND ...を含むように作る必要があります。mod(id,100)=mod(12345,100)を忘れると、分割テーブルレベルの絞込が効かず、SELECTが100個生成され、99個が空振りして、結果だけは正しいものが返るという見つけにくい性能バグになります。
- id一つ指定のクエリ、id=12345についてのクエリは
- DAU：daily active user がそれなりにあると検索の局所化効果が出ない
- 分割テーブルの一部だけが検索対象になっていれば、メモリにすいっと載ってくれて、検索スループットが上がるというのが局所化効果です。でも DAU が十分あるサービスだと、mod(id, 100)分割しても結局全部が検索対象になって、載りきらないとswap発生です。局所化での性能チューニングは、直近の情報だけにアクセス頻度が偏るとか利用状況が予想できるときに、それに合わせた時分割戦略をとるという具合にしないといけません。
- Planning timeが長くなる
- テーブルレベルの絞込がちゃんと効く場合でもペナルティがあります。引数でidの値を与えている以上、実行計画作成のオーバーヘッドがのります。詳しくはfujii_masaoさんの http://qiita.com/fujii_masao/items/bca5067eade0872fe80b を。
- CASE WHEN ～とかにして長いSQLを使っていると、副問い合わせ数 x Planning ペナルティになります。たとえ実際には実行されない部分の副問い合わせでも。
IO性能で有利じゃないの？
- Long long ago, メモリが絶望的に高価で実装量が乏しくて、DBシステムの性能がHDDのIO性能で決まっていた頃、 TABLESPACE を複数HDDにそれぞれ作って、分割テーブルを各TABLESPACEに分散させて、IO性能を稼ぐチューニングがありました。
- 今は AWS から IO性能は「量り売り」で要件に必要なだけ購入する時代です。
Provisioned IOPS (SSD) ボリューム
AWSからみて1ドライブにしておけば、AWS EBS の snapshot 機能で、無停止無負荷高速backcupできるので、こちらのメリットが圧倒的に大きいです。
一つ前の記事 でも言及しました。
分散DBでスケールアウト
大規模分散DBを目指すなら、TABLE ではなく SCHEMA単位で分割しましょう。Instagram が PostgreSQL のスケールアウトでは先行成功例です。
もっと分散
もっと要件が厳しい（面白い）なら、 Googleの Spanner/F1 あたりを真似て作らないといけないかな。
- Scale: 10s of TB, replicated to 1000s of machines