SQLを使うとスケーラビリティの問題が起きる？ それはエコじゃないね
コメント欄でAPサーバでスケーラビリティが上がるというお話を頂いたので、それについて書きたいと思います。
■前回のサンプルについて
まず、前回「Martin Fowler氏のサンプルよりもストアドプロシージャにした方がいい」というお話を書きました。
Martin Fowler氏のオブジェクト指向アプローチがメンテしやすいか、判りやすいかは個人差があると思います。個人的にはストアドプロシージャの方がメンテしやすいし、Rubyに貼り付けられたSQLはイヤだなと思う。
好き嫌いは個人差ですからさておいて、Martin Fowler氏サンプルも、DBに対するアクセスするクラスは一本化されているので、そのクラスのスタブを作ればテーブルがなくても作れるだろうか？
イヤイヤ、Martin Fowler氏のオブジェクト指向アプローチも、元は、JoinしていないSQLを使っていて分かれていたわけだから、スタブと本番が大幅に変わる可能性は非常に高い。
その変更が予想できないから、テーブル変更があればデスマになるわけです。
ですから、私にはオブジェクト指向アプローチのスタブを信じて作るなんてことはできないし、無駄が多いと思います。
ちなみに「普通はJoinしない書き方なんかしないだろう？」という方もいるかもしれませんが、O/Rマッパー使ってチューニングしなければそうなります。
■スケーラビリティについて
パフォーマンス・スケーラビリティについてです。
SELECT COUNT(*)
FROM でっかいテーブルSELECT COUNT(*), SUM(数値項目), MAX(項目), AVG(数値項目),…
FROM でっかいテーブル
というようなSQLを投げてもパフォーマンスは変わらない。
当たり前ですが、
（クライアントへ）データ転送 > データスキャン >>>> 四則演算
つまり、DBで1回テーブルスキャンするのと、そのときに集計処理をついでに行うのはパフォーマンスには差が出ませんから、DBサーバにとって四則演算をどこかで肩代わりしても意味がないのです。
Martin Fowler氏のサンプルで言えば、ここで特定の商品の受注金額のサマリーを取っています。
line_items.each {|line| result += line.cost}
eachで回っている間の処理 result += line.cost に加えて、他の四則演算処理を加えても、演算処理をゼロにしても、処理時間は（体感的に）変わりません。line_items.eachで回る時間と考えて問題がありません。
スケーラビリティをあげるためにDBサーバで処理するよりも、APサーバで処理した方が良いという人もいますけれど、四則演算をAPサーバで肩代わりするための処理を考えてみましょう。
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
結果は合計金額の数バイトであっても、それを求めるために数万件のレコードを転送する必要があるかもしれないわけで、データの重複が多ければ多いほど計算に必要なデータな数多くなりますから、時間がかかります。
しかも、もう1つ問題があって、DBサーバはメモリにキャッシュしたものは、全ユーザーで再利用できますけれど、APサーバでは複数ユーザーでメモリに展開したデータを再利用できません（DBに対するセッションを再利用する機能はありますけれど）。HTMLのような非効率なものを吐き出さないといけないので、タダでさえパンクしがちなのに、オブジェクト指向にロジックを寄せるから、APサーバが何台あっても足りない（もちろん足りる小さなプロジェクトもあるけれど）。
スケーラビリティが上がるというのは、「APサーバの増設が簡単」ということになろうかと思います。確かに簡単に追加できますが、それはエコじゃないね～。
APサーバで処理すれば、パンクしたとき増やせばいいのですが、APサーバで処理した方がDBの処理も増えていますからスケーラビリティが落ちるのです。
HDDやメモリに比べるとネットワークは非常に遅いので、ここを挟んでループを廻すと、パフォーマンスは予想できない（無限に遅くなるからね）。
「とにかく、滅茶苦茶遅くなる」としか言いようがない。
■SQLでも遅いシステムがある
もっとも、SQLも下手糞が作ったら本当にひどいネスティッドループになる（ハッシュジョインになるという、通なツッコミはうれしいけど……）。
ひどいネスティッドループというのは、つまりこんなのです。
ここに1000人分の電話番号がありますから、
電話帳を使って名前を調べてください。
（ご存知のとおり電話帳は名前順に並んでいます）
で、1000回電話帳の1ページ目からめくります。
というような処理です。
技術者が書く処理じゃないけれど、SQLの実行計画を見なかったらこうなってもおかしくない。SQLを書くけれど、SQLの実行計画を見たことがないという人も存在するので、こんな処理は全然珍しくない、いくらでもありますよ。
10個ぐらいテーブルを使った長ったらしいSQLを書いて、実行計画を見ていなかったら数時間返って来ないSQLになるかもしれません。
しかし、電話番号順に並んだリストを準備すれば解決します。
つまり、SQLで処理したら遅いとか、スケーラビリティがないというのは間違いです。
下手糞はゼロですから、下手糞な人がいることはひとまず議論から外さないと意味がないです。
■まとめ
繰り返しますが、以下の処理を見て
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
APサーバで処理するメリットがどこにあるのか、少なくとも私には理解不能です。
アセンブラとC言語の比較じゃないです。アセンブラはがんばってもC言語の生産性を超えられませんが、SQLは努力次第でオブジェクト指向で行う生産性を軽く超えられます。
Martin Fowler氏も、パフォーマンスに致命的な問題が出れば、SQLに切り替えるべきと書かれています。が、最初からSQLで処理した方がいいのでは？ ということですね。
それでも、「SQLなんて使わない方がいいんだ！」って言う人もいるけれど、それは、「遅いシステムを高い料金で買って欲しい。だって、技術習得するのがイヤなんだもん」ということになります。
これ、客の立場に立ったらあり得ない。
メンテナンスについても、技術者の能力だけの問題ですから、私のような素人上がりの素人感覚では、あり得ない理屈に聞こえるのです。
多分、ここでコメントをくれる方は、普通より積極的で技術力が高い方だと思うのですよ。
だからこそ判って欲しいと思っているし、自分が間違っていないか常に検証しています。
あるプロジェクトでいろんなしがらみで、SQLを使わないという選択肢もありえますけれど、それに技術者として疑問を持ち続けて欲しいです。
次回は、上流の人たちがSQLを習得するべきと書きましたが、「上流とはもっと上流なんだぜ！」ってコメントを頂いたので、そのことについて書きたいと思います。
コメント
インドリ
この記事を読んで思い出したのですが、下手なSQLはパフォーマンスを劣化させる事を理由に採用しないという人が居ました。
でもそれって、下手なSQLを書くこと自体が問題ですよね。
クライアント／サーバーシステムにおけるパフォーマンスの複雑さを理解せずに、SQLを駄目だとか一部の事を言う人が居るんですよね・・・
WANのシステム設計はLANとは違うんだけどね・・・
> でもそれって、下手なSQLを書くこと自体が問題ですよね。
そうなんですよ。
でも、そういう人はいっぱいいますから避けたいというのも、あながち間違った判断ともいいがたい。
中間層の技術者では「教育します」とはなかなか言えませんからね。
それでも、冷静に考えたらDBのデータを使う処理はDB内で完結する方がよいはずです。結果的に、サーバの台数が3分の2～半分にはできると思う。
エコですし運用コストも下がります。
まっとうな技術者が作れば、当然、開発費も下がるしデスマにもならない。
教育コストぐらいすぐにペイするのに。
私は天邪鬼なので、私が言ってることが普通になったら、多分、違うことを言い出しますけれど、現状では、もっと多くの人がSQLをまっとうに使う、理解することが業界を良くする第一歩だと思っています。
なかじまゆうじ
「SQLを使うとスケーラビリティの問題が起きる？ それはエコじゃないね」
確かにエコじゃないんですが、CPU８コアにメモリ8GBでギリギリのDBサーバにこれ以上の負荷をかけるくらいなら、安いAPサーバをたくさん用意したいです。
要は「時と場合による」なのですが、その「時と場合」が分からないヤツが多いのも事実ではあります。
> CPU８コアにメモリ8GBでギリギリ
伝わってないようですね……。
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送
【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
をよく見ましょう。
CPUの使用率をちゃんと取ってますか？
DBにロジックがないのに、CPU8コアなんてまず要らないですよ。
例えば、8コアのCPUが50%を超えるほど使われるなら、その前に、NICかメモリー（HDD）に問題がおきます。
現状のハードウェアは、DBサーバにはCPUが強すぎるのです。
普通の自動車にジェット機のエンジンを載せているようなものです。
ボトルネックの意味が分かってない人が、「現状考えられる最高のものです」みたいな提案をよくやってますけどね……。
シングルコアでNICを4枚とか、6枚とかの方が遙かに効果が上がります。
冷静にボトルネックをよく考えてみましょう。
なかじまゆうじ
> 冷静にボトルネックをよく考えてみましょう
ごめんなさい。意図が伝わっていないようですね。
DBサーバはデータの保存が最優先で、かつシステム上シングルポイントになりやすいので、他のサーバに比べてどうしても高価な機材を必要とします。
それに比べ、APサーバはスケールアウトできるので、安価な機材で構成することが可能です。
もちろん、DBサーバが遊んでいるときは、もっと仕事をさせれば良いのです。
しかし、どっかのインターネット証券屋さんのように、明らかにDBサーバがボトルネックになっている場合には、それ以上DBサーバに高価な機材を持ち込むよりはAPサーバ上で処理を行った方が良い場合もあります。
この辺が、「時と場合による」部分です。
まぁ、DBサーバがボトルネックになっているシステムは、割とちゃんとしたシステムの場合が多いですけどね。
繰り返します。
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送
【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
をよく見ましょう。
安価なAPサーバが増えることで、DBサーバに対する負荷がかえって増えます。
APサーバの処理も増えます。
システム全体の処理を考えたら、DBサーバで処理した方がDBサーバも、APサーバも負荷が軽くなるのです。
ですからAPサーバで処理して、DB１台、AP５台で処理しているとしたら、DBサーバで処理すれば、DB１台、AP２～３台で処理できるようになります。
スケーラビリティを考えれば、DB１台、AP５台なら、DB１台、AP２台、HTTP２台とするのが良いでしょうね。
下手糞が設計するとAPとHTTPは大抵同じマシンになってますから（笑）
よろしければ、こちらもご覧ください。
http://www.g1sys.co.jp/column/_2006_11_10.html
ちなみにSBMの障害は、ロックエスカレーションが原因じゃないかと思いますけど……。断定は出来ませんから知らん振りして書いてます。
証券屋さんとか、DBがボトルネックになっているのは、CPUなのか、メモリーなのか、HDDなのか、ネットワークなのか、現物を見ないと一概に言えません。
結局、下手糞なんですよ。
論理的に考えれば、APサーバに処理を振っても、DBサーバの（全体）処理は減るどころか増えます。
なかじまゆうじ
> 結局、下手糞なんですよ。
> 論理的に考えれば、APサーバに処理を振っても、DBサーバの（全体）処理は減るどころか増えます。
なるほど、生島さんが楽天証券やクリック証券のシステムを担当されると、相当サーバ台数が減らせるんですね。
http://itpro.nikkeibp.co.jp/article/JIREI/20070530/272660/
ちなみに、E to Cで秒間アクセスが数万を超えるような高負荷WebシステムではAPサーバを置きません。APサーバを経由する時間がもったいないですから。
楽天証券やクリック証券を担当してみたいけど（笑）
APサーバに処理はさせてないってことなんじゃないですか？
秒間数万アクセスなら、HTMLを返すだけで大変ですから、APサーバで処理は極力抑えないと大変でしょうね。
DB ⇔ HTTP（AP）の転送量も出来る限り抑えないとすぐにパンクするでしょう。
要するに大きくなれば、DBサーバで処理しないといけない。
ってことで、私が言ってることの証明じゃないですか？
つまり、APサーバで処理していれば、DBサーバに処理を移すことによって台数を減らすことができる ＝ APサーバで処理することはスケーラビリティが落ちる。
もともと、DBサーバで処理していてパンクしたら、APサーバに処理を移したらもっと早くパンクするわけです。
なかじまゆうじ
> 秒間数万アクセスなら、HTMLを返すだけで大変ですから、APサーバで処理は極力抑えないと大変でしょうね。
APサーバ（この場合、正確にはWebサーバ）での処理量を押さえることに意味は無いです。Webサーバは安価ですから、処理量の強化は容易です。
> DB ⇔ HTTP（AP）の転送量も出来る限り抑えないとすぐにパンクするでしょう。
今どき、LAN内であればネットワークの高速化など、安い話です。
spq
事情もわからない他社のトラブルを自分の都合のいいように解釈して自論を展開してもどうかと思いますよ。SBMさんとかのエンジニアに失礼では？
SQLをもっと勉強しなさいってのはすごい賛成できますし、実際いい仕事をされているでしょうけど、一緒には働きたくないですね。我田引水っぷりが強烈すぎます。
あと、答えられない質問や意見は素っ頓狂なこと言ってはぐらかすのは、それぐらいじゃないと社長なんかやってられないからですか？
もう一回繰り返しますね。
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送
【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
APサーバで処理したら、どこの処理が減るのでしょう？
spqさん
こんにちは。
SBMさんのエンジニアに対しては…確かに失礼ですけれど、1%以下の顧客が来ただけで「処理がオーバーフローして…」を言ったら、1%以下の顧客でどんな処理をしたらオーバーフローするのかは、考察するネタになって、イロイロ言われるのは仕方ないのではないでしょうか？
なかじまゆうじ
> APサーバで処理したら、どこの処理が減るのでしょう？
ご自身でお気づきになりませんか？
自分は、トータルの処理量を問題にしていません。そして、APサーバの処理能力増強やネットワークの高速化は安価だと主張しています。
なかじまゆうじ
追伸
「トータルの処理量」という書き方は良くないですね。「1パス」とか「ターンアラウンド」とか「1回のデータアクセスの」とかと捉えてください。その時間にかかっている負荷全体という意味ではないです。
繰り返しているとおりです。
【DBサーバで処理した場合】
HDD → メモリ → CPU（四則演算・ソート） → （結果のみ）転送
【APサーバで処理した場合】
（DBサーバの）HDD → メモリ → （計算に必要なデータ）転送
→ （APサーバの）メモリ → CPU（四則演算・ソート）
この件について後で記事に詳しく書きますけれど、APサーバで処理したときのDBサーバの処理を考えてみましょう。
四則演算・ソートが減って、データ転送量・SQL文の受信量・受信回数（つまりSQL特有のでかいオーバヘッド）が増えます。
で、私は
（四則演算・ソート） ＜ （データ転送量・SQL受信量・受信回数の増加）
と考えているため、DBサーバの処理量は減ってないからスケーラビリティが落ちると主張しているわけです。
ですから、
（四則演算・ソート） ＞ （データ転送量・SQL受信量・受信回数の増加）
となると主張していただいて、それが「なるほど正しい」と思えばすぐに納得します。
APサーバの処理量の増加は、そりゃひどいものですからエコじゃないですね。