レベルエンター山本大のブログスマートフォン用の表示で見るレベルエンター山本大のブログ面白いプログラミング教育を若い人たちにBLOCKVROCKリファレンス目次はこちら2011-09-06ホント信じられないDB文化だけど、統計情報固定化はマジでアリ信じられないDB文化「固定長DB」でもあうんです。山本大の日記に引き続き、大規模コンシューマ向けサービスのRDBの意外な使い方について。僕らのサービスでは、統計情報を手作業でセットして固定化していた。こんなことは普通やらないけれど、しかしながら非常にシステムのパフォーマンスを安定させるのに効果があった。Oracleの統計情報(オプティマイザ統計情報)とはまず統計情報とは何かというところから始める。統計情報とは、正式名称「オプティマイザ統計情報」といい、OracleがSQLを解析して最適な実行計画を作成するために利用する情報である。実行計画を作成する機能のことをCBO(コスト・ベース・オプティマイザ)という、このオプティマイザ向けの統計的な情報だから、オプティマイザ統計情報と呼ばれる。統計情報の実体は何かというと、データベースの各テーブル・各インデックスカラムの傾向を表したデータディクショナリデータである。例を挙げると、例えばユーザテーブルに100万件のデータが入っていたら、テーブル統計情報として100万件のレコードがあるということが記録される。100万件と、1000件では最適なアクセスパスはぜんぜん違う。100万件を全表走査などしようものなら、SQLは100秒は返ってこない。1000行なら、インデックスアクセスしてる分だけ無駄もあるだろう。インデックスカラムごとにどういった多重度の傾向があるのかというのも記録されている。性別カラムは男/女/Nullの最大3パターンしかない(4?)が、ユーザーIDのようにユニークなカラムであれば、レコード件数分の分布がある。これはBツリーインデックスの選択性に直結する。性別のような多重度の低いカラムにBツリーインデックスがはられていても全然効果無いからCBOは選択しない。最適なインデックスを使うのと、使わないのでは100万倍の差がでることもしばしばである。いうことで、SQLチューニングの基本中の基本は、インデックスを効果的に使うことであり、それに紐づいて、統計情報というのはパフォーマンスチューニングの最重要事項といえる。いうのはOracleに定期的にサンプリングさせて自動採取して更新する。すると、データベースのデータが成長(蓄積)されていってもSQLの実行計画作成を担当するCBO(コストベースオプティマイザ)が、最適な実行パスを算出して出してくれるから、常に最適な性能が出る。いうのが名目上の運用方法。この運用方法には落とし穴がある。落とし穴とは、CBOが実行計画変更する時に発生する「ハードパース」と呼ばれる処理のコストに起因する。このハードパース処理、よく知られていることだが結構CPUコストが高い。データベースの負荷が高い時などは1秒〜4秒そのテーブルへのアクセスを全て停止する。我々が構築したような大規模コンシューマ向けサービスで、1秒止まると20件ぐらいのアクセスに影響がある。↓サービス全停止という、凄まじいコンボであった。これは去年の年末ごろ実際に経験した事象だ。それを防ぐ施策として僕らはこの現象を当初から読んでいた。(制御しきれていない部分が火を吹いたけど)だから、統計情報は手作業で設定し固定化するという決断をしていた。統計情報がコロコロ変わらなければハードパースは頻発しない。サービス停止につながるような著しい性能劣化は、起こらないのだ。+CBOへのおまかせ運用の良さは、人間には制御不能なぐらい複雑なSQLのアクセスパスをちゃんと最適化してくれることだ。僕らはJoin禁止のルールのお陰で逆の発想ができた。Join禁止は、SQLのアクセスパスが非常にシンプルになる。SQLがシンプルなので、統計情報を手動で設定してCBOの挙動を開発者が管理することができるのだ。自動採取の統計情報では、サービスの初期段階ではデータ量が少ないのでテーブルスキャンに倒れていて、サービスが成長したときにデータ量が多くなるのでインデックススキャンに倒すようなアクセスパスの変動が発生することがある。僕らが経験したようなコンシューマ向けサービスでは、急にパフォーマンスが上がることすら説明を求められる問題事象になる。「設計の範疇にない事象がおこった」「サービスをコントロールできていない」という判断になるのだ。オプティマイザ統計情報は、実はCBOの計算上は物理的なデータ件数よりも優先する。1000万件の物理データがあろうが、統計情報に10万とあればCBOは10万件のデータに最適なSQL実行計画をはじき出す。物理データの量を騙してCBOにアクセスパスを設定させることができる。Joinがある場合、CBOは関連テーブルのデータ量を見ながら、どちらを先にループしてJoinするかを最適化してくれる。LOOPなのかといった結合アルゴリズムを決定してくれる。これはパフォーマンスに大いに好影響を与える。Join禁止のルール下では、CBOが最適化するのはどのインデックスを使うか?というのが最大のチューニングポイントとなる。これって、シンプルなSQLしか使わない環境下では設計者が意図して使ってほしいインデックスが最適なのだ。(これまた余計なお世話で、CBOが提案するヘボイインデックスで何度泣かされたか。)最悪なのは、物理データが大量なのにCBOが少量データだと思い込んで、テーブルフルスキャンに倒してしまうことだ。これを逃れるには、統計情報にある程度デカイ値を入れておけば、テーブルフルスキャンには倒れない。最悪のテーブルフルスキャンは起こらないし、多少効率が最適でなくても設計者の予測通りのインデックスを利用してくれる。設計者の予測通りのインデックスとは、コンシューマサービスでは多くの場合主キーインデックスなのだ。最善ではなく次善だが、最悪には転ばない。これが、コンシューマサービスでは非常に重要な設計基本ポリシーと言える。それってRBOお気づきの方もいるかもしれないが、この設計ってOracleが捨てたRBO(ルールベースオプティマイザ)を使えば、一発解決の問題だ。9i以降RBOは姿を消し、CBOだけが生き残った。最終的に、「こうったサービスでは統計情報を固定化したほうが良い」という見解をだした。RBOをどっかで残して欲しかったなぁ。・ハードパースで、致命傷をくらうこともある。・性能が改善することもあるが、最悪のケースも引きうる。・設計者のコントロールを離れる。統計情報を手作業でセットして、Joinを禁止にしたら・ハードパースは基本起こらない。(再起動とかしない限り)・性能は次善だが、安定する。・設計者がコントロール可能。いえ統計情報を手作業で算出するのは、非常に骨が折れる計算式だし、サービスの需要予測をテーブル単位でしっかり把握していないとできない。僕の後輩のDBAをやってる女の子が、この統計情報の手計算、手設定をやってるんだけど一時期、ゴッドハンドと呼ばれるほど、最適な数値をたたき出していた。二度とやりたがらないが。禁止の話に、いまだに絡んでくれる人がいた。■「艦これ」...2011-08-08信じられないDB文化「固定長DB」でもあうんです。大規模コンシューマ向けサービスのRDB設計ずいぶん時間があいてしまったけど、大規模コンシューマ向けサ...2010-09-06信じられないDB文化「Join禁止」に「固定長DB」、、でも、合うんです。onSQLが、どれだけディスク利用しているかという情報を表示する方...2008-06-26クエリの実行プランを強制する。いま、クエリのパフォチュをしている。結局ギークでスーツが最強だろう。視線の方向に常に直進するカメラを操作する殴れる物体を作るクリックしたときに物体を移動させる物体をアニメーションさせる月別アーカイブ検索はてなブログをはじめよう!iad_otomamayさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。