信じられないDB文化「固定長DB」でもあうんです。大規模コンシューマ向けサービスのRDB設計 - 山本大の日記に引き続き、大規模コンシューマ向けサービスのRDBの意外な使い方について。
僕らのサービスでは、統計情報を手作業でセットして固定化していた。
こんなことは普通やらないけれど、しかしながら非常にシステムのパフォーマンスを安定させるのに効果があった。
Oracleの統計情報（オプティマイザ統計情報）とは
まず統計情報とは何かというところから始める。
統計情報とは、正式名称「オプティマイザ統計情報」といい、OracleがSQLを解析して最適な実行計画を作成するために利用する情報である。
実行計画を作成する機能のことをCBO（コスト・ベース・オプティマイザ）という、このオプティマイザ向けの統計的な情報だから、オプティマイザ統計情報と呼ばれる。
統計情報の実体は何かというと、データベースの各テーブル・各インデックスカラムの傾向を表したデータディクショナリデータである。
例を挙げると、例えばユーザテーブルに１００万件のデータが入っていたら、
テーブル統計情報として１００万件のレコードがあるということが記録される。
１００万件と、１０００件では最適なアクセスパスはぜんぜん違う。１００万件を全表走査などしようものなら、SQLは１００秒は返ってこない。しかし１０００行なら、インデックスアクセスしてる分だけ無駄もあるだろう。
また、インデックスカラムごとにどういった多重度の傾向があるのかというのも記録されている。
例えば、性別カラムは男／女／Nullの最大３パターンしかない（４？）が、
ユーザーIDのようにユニークなカラムであれば、レコード件数分の分布がある。
これはBツリーインデックスの選択性に直結する。
性別のような多重度の低いカラムにBツリーインデックスがはられていても全然効果無いからCBOは選択しない。
最適なインデックスを使うのと、使わないのでは１００万倍の差がでることもしばしばである。
ということで、SQLチューニングの基本中の基本は、インデックスを効果的に使うことであり、
それに紐づいて、統計情報というのはパフォーマンスチューニングの最重要事項といえる。
一般的なオプティマイザ統計情報の運用方法
通常、統計情報というのはOracleに定期的にサンプリングさせて自動採取して更新する。
こうすると、データベースのデータが成長（蓄積）されていってもSQLの実行計画作成を担当するCBO（コストベースオプティマイザ）が、最適な実行パスを算出して出してくれるから、常に最適な性能が出る。
というのが名目上の運用方法。
しかし、この運用方法には落とし穴がある。
CBOと自動算出統計情報運用の落とし穴
落とし穴とは、CBOが実行計画変更する時に発生する「ハードパース」と呼ばれる処理のコストに起因する。
このハードパース処理、よく知られていることだが結構CPUコストが高い。
データベースの負荷が高い時などは１秒〜４秒そのテーブルへのアクセスを全て停止する。
我々が構築したような大規模コンシューマ向けサービスで、１秒止まると２０件ぐらいのアクセスに影響がある。
実際１秒止まった。
副次的に何が起こったかというと、
全体的な処理遅延
↓
コネクションプールの枯渇
↓
サービス全停止
という、凄まじいコンボであった。
これは去年の年末ごろ実際に経験した事象だ。（後述する僕らの施策が行き届いてないパッケージ製品範囲での事象である）
お陰で、プロジェクトチーム全体が、土日出勤・徹夜作業をくらった。
では、それを防ぐ施策として
僕らはこの現象を当初から読んでいた。（制御しきれていない部分が火を吹いたけど）
だから、統計情報は手作業で設定し固定化するという決断をしていた。
統計情報がコロコロ変わらなければハードパースは頻発しない。
サービス停止につながるような著しい性能劣化は、起こらないのだ。
これはJoin禁止というルールがあったからこそとれた方針である。
統計情報自動採取＋CBOへのおまかせ運用の良さは、人間には制御不能なぐらい複雑なSQLのアクセスパスをちゃんと最適化してくれることだ。
しかし、僕らはJoin禁止のルールのお陰で逆の発想ができた。
Join禁止は、SQLのアクセスパスが非常にシンプルになる。
SQLがシンプルなので、統計情報を手動で設定してCBOの挙動を開発者が管理することができるのだ。
CBOの動き方
自動採取の統計情報では、サービスの初期段階ではデータ量が少ないのでテーブルスキャンに倒れていて、サービスが成長したときにデータ量が多くなるのでインデックススキャンに倒すような
アクセスパスの変動が発生することがある。
僕らが経験したようなコンシューマ向けサービスでは、急にパフォーマンスが上がることすら
説明を求められる問題事象になる。
すなわち、「設計の範疇にない事象がおこった」「サービスをコントロールできていない」という
判断になるのだ。
さて、オプティマイザ統計情報は、実はCBOの計算上は物理的なデータ件数よりも優先する。
１０００万件の物理データがあろうが、統計情報に１０万とあればCBOは１０万件のデータに最適な
SQL実行計画をはじき出す。つまり、物理データの量を騙してCBOにアクセスパスを設定させることができる。
Joinがある場合、CBOは関連テーブルのデータ量を見ながら、どちらを先にループしてJoinするかを最適化してくれる。
またはHASH JOINなのか、NESTED LOOPなのかといった結合アルゴリズムを決定してくれる。
これはパフォーマンスに大いに好影響を与える。
しかしJoin禁止のルール下では、CBOが最適化するのはどのインデックスを使うか？というのが最大のチューニングポイントとなる。
でもこれって、シンプルなSQLしか使わない環境下では設計者が意図して使ってほしいインデックスが最適なのだ。
（これまた余計なお世話で、CBOが提案するヘボイインデックスで何度泣かされたか。。）
最悪なのは、物理データが大量なのにCBOが少量データだと思い込んで、テーブルフルスキャンに倒してしまうことだ。
これを逃れるには、統計情報にある程度デカイ値を入れておけば、テーブルフルスキャンには倒れない。
ということで、統計情報を需要予測の１０倍程度の大きさで、セットしておくと、
最悪のテーブルフルスキャンは起こらないし、多少効率が最適でなくても設計者の予測通りのインデックスを利用してくれる。
そして、設計者の予測通りのインデックスとは、コンシューマサービスでは多くの場合主キーインデックスなのだ。
最善ではなく次善だが、最悪には転ばない。
これが、コンシューマサービスでは非常に重要な設計基本ポリシーと言える。
それってRBO
お気づきの方もいるかもしれないが、この設計ってOracleが捨てたRBO（ルールベースオプティマイザ）を使えば、一発解決の問題だ。
ぶっちゃけ、共に散々苦しんだOracleのDBコンサル部隊の人たちも、
最終的に、「こうったサービスでは統計情報を固定化したほうが良い」という見解をだした。
RBOをどっかで残して欲しかったなぁ。
まとめ
オプティマイザ統計情報の自動採取とCBOの横暴に任せると、
・ハードパースで、致命傷をくらうこともある。
・性能が改善することもあるが、最悪のケースも引きうる。
・設計者のコントロールを離れる。
統計情報を手作業でセットして、Joinを禁止にしたら
・ハードパースは基本起こらない。（再起動とかしない限り）
・性能は次善だが、安定する。
・設計者がコントロール可能。
まぁ、とはいえ統計情報を手作業で算出するのは、非常に骨が折れる計算式だし、
サービスの需要予測をテーブル単位でしっかり把握していないとできない。
僕の後輩のDBAをやってる女の子が、この統計情報の手計算、手設定をやってるんだけど
一時期、ゴッドハンドと呼ばれるほど、最適な数値をたたき出していた。
もう二度とやりたがらないが。。。
