コンシューマサービスの運用に耐えるDB性能設計とは

JOIN 禁止の話に、いまだに絡んでくれる人がいた。
■「艦これ」から、ソーシャル系のサーバ構成を考える - SQLer 生島勘富の日記

僕が以前に書いた本テーマに関するエントリは以下の３つ。

■信じられないDB文化「Join禁止」に「固定長DB」、、でも、合うんです。大規模コンシューマ向けサービスのRDB設
■信じられないDB文化「固定長DB」でもあうんです。大規模コンシューマ向けサービスのRDB設計
■ホント信じられないDB文化だけど、統計情報固定化はマジでアリ

ちょうど折よく、ウチの会社のオラクル女子が書いたエントリの続きも公開されました。
■一緒にまなぼ！「hiromi と楽しむOracleパフォーマンスチューニング！」【Vol.2 Statspackを見てみよう】

ということで僕の中でDB熱が盛り上がってきたので返答的なエントリを書きます。

「とりあえずメモリだけ気にしておけ」の設計は運用では通用しない。

　RDBMS は、大量のデータを処理することを目的に作られて、現在のハードウェア環境であれば CPU を使いきることはかなり難しく、メモリーが足りなくなることがほとんどです。特に、一つ一つの処理は単純で、大量のアクセスを捌くことが重要なソーシャル系のシステムでは、「メモリーを効率的に利用することだけを気にしていれば問題ない」と言い切っても過言ではありません。
「艦これ」から、ソーシャル系のサーバ構成を考える - SQLer 生島勘富の日記

参照元ではメモリがボトルネックのほとんどと言ってますが、さすがにこれには異論がありますね。

メモリの設計ミスを原因として発生する障害は簡単な性能試験をやれば再現することが多いので、比較的解決が簡単な事象です。

運用時に本当に厄介でアーキテクチャ的な設計を必要とするのは、トラフィックの急激な増加や経年することによるデータの変化によって引き起こされる障害です。

経験的には、「トラフィックの急激な増加に起因する障害は、メモリよりもディスクI/Oがボトルネックになることの方が多いです。
メモリよりもディスクI/Oの方がコスト高なので、ディスクI/Oが詰まる方が事象として現れやすいのは当然と言えます。

コンシューマサービスを経験されていない方は、企業システムのトランザクションのレベルでチューニングを考えるのでしょうけれど、その世界観の違いを認識できなければ参入時に手痛い代金を払わされる事になると思います。

コンシューマサービスが普通にヒットして、トラフィックが急増する時に無策でメモリだけの観点でのチューニング程度で運用に突っ込めば、今の最新ハードであろうとCPUぐらいすぐ100%で張り付きます。
想定外のトラフィックに備えられていなかったことも運営側のミスとなるというのがコンシューマサービスの世界です。

Oracleで SQLテクにモノを言わせた時に発生する経年劣化に関する問題

参照元で主張されている、SQLのテクニックにモノを言わせたようなアプリにしてしまうのは、
コンシューマーサービスの運用上ではいずれ性能低下を引き起こしかねない危険な作りだと思います。

これは開発時しか見ていないエンジニアと、運用を中心に見ているエンジニとで大きく違って見えるのではないかと思います。

SQLの書き方でパフォーマンスを最適化する方法というのはいくつもありますが、多くのテクニックはその時点の環境に最適化したSQLを作ってしまいがちです。

しかしながら、大規模データであろうとなかろうとですがデータベースの環境というものは経年するごとに変化していきます。
状況の変化に一番強いのはシンプルであるということだと思っています。
特に大量データを扱うSQLは、状況の変化に強い作りにしておくべきでしょう。

Join禁止は極端な例ですし、僕はJoin禁止とまでルール厳格化する必要はないと思っています。
しかし、それぐらいの姿勢で臨んでも悪くないぐらいSQLはシンプルである方が好ましいとは思います。

例えば、シンプルでないSQLはOracle でいえばCBO（コストベースオプティマイザ）が利用する「オプティマイザ統計情報」の更新時に性能問題が発生する可能性が非常に高くなります。

オプティマイザ統計情報についての話は、ホント信じられないDB文化だけど、統計情報固定化はマジでアリ - 山本大の日記に書きました。

Oracleの統計情報（オプティマイザ統計情報）とは
統計情報とは、正式名称「オプティマイザ統計情報」といい、OracleがSQLを解析して最適な実行計画を作成するために利用する情報である。
実行計画を作成する機能のことをCBO（コスト・ベース・オプティマイザ）という、このオプティマイザ向けの統計的な情報だから、オプティマイザ統計情報と呼ばれる。
統計情報の実体は何かというと、データベースの各テーブル・各インデックスカラムの傾向を表したデータディクショナリデータである。
例を挙げると、例えばユーザテーブルに１００万件のデータが入っていたら、テーブル統計情報として１００万件のレコードがあるということが記録される。
SQLチューニングの基本中の基本は、インデックスを効果的に使うことであり、
それに紐づいて、統計情報というのはパフォーマンスチューニングの最重要事項といえる。

オプティマイザ統計情報を更新すると３つの性能関連の影響を発することになります。
・オプティマイザ統計情報の更新処理そのものがCPUにかなりの負荷がかかる
・オプティマイザ統計情報が更新を受けて、SQLのハードパースが発生しCPU負荷がかかる
・オプティマイザ統計情報の更新後、SQLの実行パスが変わってしまい元の性能と大きく改善または改悪する

実際に、僕らのプロジェクトも一度、実行計画更新に関わる障害を引いた事があります。

この話もホント信じられないDB文化だけど、統計情報固定化はマジでアリ - 山本大の日記に書きました。

落とし穴とは、CBOが実行計画を変更するときに発生する「ハードパース」と呼ばれる処理のコストに起因する。
このハードパース処理、よく知られていることだが結構CPUコストが高い。
データベースの負荷が高い時などは１秒〜４秒そのテーブルへのアクセスを全て停止する。
我々が構築したような大規模コンシューマ向けサービスで、１秒止まると２０件ぐらいのアクセスに影響がある。
実際１秒止まった。
副次的に何が起こったかというと、
全体的な処理遅延
↓
コネクションプールの枯渇
↓
サービス全停止
という、凄まじいコンボであった。

それを防ぐ施策はいくつかあると思います。
これも既に書いたことではありますが、僕らの対策は下記のようなものでした。

僕らはこの現象を当初から読んでいました。（制御しきれていない部分が火を吹いたけど）
だから、オプティマイザ統計情報は手作業で設定し固定化するという決断をしていました。
オプティマイザ統計情報がコロコロ変わらなければハードパースは頻発しません。

ただし、データが成長し続けたとしても有効なオプティマイザ統計情報を算出するにはSQLがシンプルでなければ難しいです。
SQLでテクニックを披露するのは、バッチ処理やレポーティングなどのコンシューマーのアクティビティに結びつかない処理で活用するべきかなと思います。

コンシューマーサービスの細かくて膨大なトラフィックを舐めてはいけません。
世界の違いを認識して欲しいと思います。

まとめ

コンシューマサービスのDBの特徴は、
トラフィックが急激に増加する事があるということと
データが蓄積されることによって大きくデータ検索の環境が変わるというところかなと思います。

それらをきちんと回避するための設計として、メモリにフォーカスしてチューニングする、SQLテクニックを適切に使う。という解決策だけではうまくいかないと感じました。

追記

ブコメに下記の通りあった。

ソーシャルゲームにおけるJOIN禁止の理由と意味を理解していない。「不適切な実行計画」じゃなくて、テーブルで縦に割れないからって単純な理由なわけだが。他に関しても全く理解していないね。

ああ、これ言ってなかった。まぁ指摘ありがとうです。
テーブルで縦に割る設計もしてましたし、ユーザで横に割る設計もしました。
縦で割るって言うのは、ユーザが共有するべきデータ（ランキングとかマスタっぽいのとか）はテーブル単位に表領域区切って分離可能にすることです。
これもJoin禁止で採りやすかった施策の一つではありましたが、この程度であればマテビューなりで解決可能という反論を予期して、面倒なのであえてJoin禁止とかシンプルSQLの理由にあげてません。
横で割るっていうのは、ユーザ固有のデータについて、同一構成で複製したDBに持たせてID単位に振り分けてました。
その他、全く理解していないという部分は詳しく聞きたいもんです。

おまけ

冒頭で触れたウチの会社のオラクル女子というのは、まさに上記で話題にしている案件で一緒にDB担当をやっていた部下です。
上記のオプティマイザ統計情報の手動セットも彼女がやっていて、CBOの動きを読んで最適なオプティマイザ統計情報の算出をするという技を持っています。一部では「ゴッドハンド」と呼ばれていました。
下記の記事「STATSPACKの読み方講座」も結構気合いが入ってるので、よかったら一読ください！

■一緒にまなぼ！「hiromi と楽しむOracleパフォーマンスチューニング！」【Vol.2 Statspackを見てみよう】