SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

シェルが使えないレンタルサーバで Facebook API を使う

備忘録

　PHP 5.2 なら JSON は標準で入っていますからこんな苦労はないですが、レンタルサーバで PHP 5.1.6、シェルは使えない。という環境で Facebook API を使うのに嵌まった。シェルが使えないサーバで開発ってこんなに大変なのね（苦笑）

　もう二度とやりたくないけど、せっかくだから備忘録です。

手順

　Facebook SDK for PHP をダウンロードしてクライアントで解凍。
　　　　facebook.php
　　　　base_facebook.php
　をパスが通るところに置く。

　class-json.php をダウンロードしてパスが通るところに置く。
　　　　class-json.php

　base_facebook.php の以下のところを

if (!function_exists('json_decode')) {
  throw new Exception('Facebook needs the JSON PHP extension.');
}

　こんな風に書き換える。

if ( !function_exists('json_decode') ) {
	function json_decode( $string, $assoc_array = false ) {
		global $wp_json;

		if ( !is_a($wp_json, 'Services_JSON') ) {
			require_once( 'class-json.php' ); //パス通は適当に指定する
			$wp_json = new Services_JSON();
		}

		$res = $wp_json->decode( $string );
		if ( $assoc_array )
			$res = _json_decode_object_helper( $res );
		return $res;
	}
	function _json_decode_object_helper($data) {
		if ( is_object($data) )
			$data = get_object_vars($data);
		return is_array($data) ? array_map(__FUNCTION__, $data) : $data;
	}
}

　あとは、ソーシャルメディアAPI リファレンス この辺の通りにすれば多分できると思います。

　WordPress は JSON を使って動いているはずなので、その辺を参考にしました（笑）
　SDKを書き換えているので自己責任でお願いします。

　こんなことに嵌まるとは、もう歳やね……。

もうちょっと「艦これ」からSQLを考えてみる１

艦これ

　かなり前に下書きをしていたんだけれど、バタバタしていてほったらかしになっていたものです。タイミングがズレたけど、まだ「艦これ」をやっている人がいるみたいなのでアップします。

私が「艦これ」の設計をするなら

　「軍艦を擬人化する」という発想は、生まれ変わっても私からは出てこないし、企画書を見せられても反応すらできないとは思う。ですから、「企画した人は凄いな〜」とは思うけれど、そんな分からないことを考えても意味ないので、遊んでいる半分以上の時間は、「自分ならどう設計するか」を考えていました。

　一番気になったのが、

　「何でこんなに遅いのか？」
　「10万同時アクセスで落ちるとしたらどんな構造か？」

ということでした。

　MySQL Cluster を利用しているとして、10分ほど遊んで考えたのが次のようなモノでした。

　APサーバは分割するけれど、DBは分割しない。
　更新系はNoSQL（API）を利用する。
　非同期処理。

　　　　# で、もうちょっとやってみて……
　　　　# ネット対戦型じゃないパチンコレベルと理解して……
　　　　# ナメとるのかと……
　　　　# こんなしょうもないシステムならクライアントでせいよ！って……

　まあ、それはさておき、私が考える設計なら、コネクションプールやメモリーの設定で余程のヘマをしない限り10万同時アクセス程度では止まらない。

　DBをサーバ群毎に分割して独立させているのは意外で、それならもっと落ちにくくなるはずです。しかし、そこまでしても落ちるというのは、どんなことをしているのか？とイロイロと考えてみました。ベターを予測するよりヘボい方は無限にパターンがあるので難しい。難しいから面白いのでやめられない。

シーケンシャルでSQLを流す

　止まった理由を考えるのに、私の感覚では派手なアニメーションは負荷分散のために存在する（システム側の人は普通そうでしょう）と思っているので、シーケンシャルな処理はわざわざ考えないと出てこない。

　しかし、画面が切り替わらずにしばらくしてエラー画面が出るということがあったので、もしかしてシーケンシャルに処理をしているんじゃないか？とまず想像しました。ところが、シーケンシャルに処理をしたとしても、その程度じゃあ、そうは落ちない。

「じゃあ、NoSQL（NDB API）は使ってないんじゃないか？……それでも、そう簡単には落ちないな〜。何が問題なんだろう？」

　どうも不安定になったとき、みんなが「F5 連打する」ことの影響が大きそう。

……前回の記事を書いたときに、「資源増加」を思い出し、
「そのタイミングでアップデートしているんじゃないか？」
「いや、それならアクティブユーザ以外の情報も更新が必要になる……」
「まさか、それはないだろう」

「あっ！SELECT前後に UPDATE 流してるな！」

　と思い至りました。（もちろん、予想でしかない）そう言えば、修理する画面に遷るときに時間が掛かり、落とされることもあったし、SELECTだけでそんなことが起きるとは考えにくいけれど、SELECTするときに、資源が増えるタイミングを超えていたら、増えた資源分を UPDATE する。という仕様なら、DBサーバの負荷は劇的に上がる。

私ならこんなテーブルレイアウト

　「艦これ」は時間経過で資源が増えてきます。私がその後も遊んでいる「Clash of Clans」も、時間経過で資源が増えてきますから、恐らく、これは無料でも遊べて、もっと遊ぶには課金をするというタイプのゲームにとって一般的な考え方なのでしょう。（オンライン麻雀とか将棋でも似た概念がありますしね）

　「艦これ」にそういう考え方があったのかは分かりませんが、「Clash of Clans」ではレベルに応じて資源の増え方が速くなり、課金してアイテムを購入すれば、更に、一時的に増える速度を上げられる……。そんな仕様変更は起こり得るイメージで設計します。

　単純でアクセス数の多いシステムなので非正規化したくなるけれど、そういう仕様変更に対する許容度は非正規化すると小さくなる。ですから、正規化して、恐らくサロゲートキーは利用せず、ナチュラルキーでこんな感じになります。（最大数などを別テーブルにするかも知れませんがその辺は読み替えて！）

　資源が増えるのが一律（確か、現状そうでしょう）で、レベルの最大が999だとしたら（知らんけど）、増加マスタ（ネーミング下手くそでゴメン）に1〜999までの999件を同じデータで埋めておく。（あるいは、1件だけ作ってJOINしない）

　まあ、パフォーマンスや負荷にはほとんど影響しないのですが、それでもアクセスが増えたら効いてくる可能性もある。増加マスタはAPサーバにキャッシュしてそちらで計算しても良いけれど、UPDATEを流すことと比べたら誤差です。

具体的なSQL

　SQLで全部計算するとすると、こんな感じのSQLになります。
　※ 本当はこちらにあるようにストアドファンクションを利用すべきです。

SELECT
	……
	, CASE WHEN 
		sub.弾薬数 
		+ (b.弾薬増加数 * sub.通常分数) 
		+ (b.弾薬増加数 * c.弾薬増加倍数 * sub.ブースト分数) > b.弾薬最大値 
		THEN b.弾薬最大値 
		ELSE sub.弾薬数
			+ (b.弾薬増加数 * sub.通常分数) 
			+ (b.弾薬増加数 * c.弾薬増加倍数 * sub.ブースト分数)
	END	AS 現在弾薬数 
	, CURRENT_TIMESTAMP
	, ……
FROM
	(SELECT 
		a.*
		, CASE WHEN a.ブースト終了時間 > a.前回更新時間 THEN -- ブースト中
			CASE WHEN a.ブースト終了時間 > CURRENT_TIMESTAMP THEN 
				TIMESTAMPDIFF(MINUTE, a.前回更新時間, CURRENT_TIMESTAMP)
			ELSE
				TIMESTAMPDIFF(MINUTE, a.前回更新時間, a.ブースト終了時間)
			END  
		ELSE 0 END AS ブースト分数
		, CASE WHEN a.ブースト終了時間 > a.前回更新時間 THEN -- ブースト中
			CASE WHEN a.ブースト終了時間 > CURRENT_TIMESTAMP THEN
				0
			ELSE
				TIMESTAMPDIFF(MINUTE, a.ブースト終了時間, CURRENT_TIMESTAMP)
			END  
		ELSE TIMESTAMPDIFF(MINUTE, a.前回更新時間, CURRENT_TIMESTAMP) END AS 通常分数
	FROM 
		ユーザステータス AS a
	WHERE 
		a.id = ?
	) AS sub
	INNER JOIN 増加マスタ AS b
		ON sub.ユーザレベル = b.ユーザレベル
	INNER JOIN ブーストマスタ AS c
		ON sub.ブーストレベル = c.ブーストレベル;

　SQLのダサさが目一杯出た形ですね。
　ブーストマスタの概念がなく、レベルによって増加スピードに差がないなら、例えば、増加マスタテーブルに「ユーザレベル＝ 0」のデータを1件だけレコードを作っておき、

SELECT 
	-- カラムの羅列（省略）
	, CURRENT_TIMESTAMP
FROM ユーザステータス AS u
	, 増加マスタ AS z
WHERE
	u.ユーザID = ?;

　としておいてもDBサーバの負荷は変わりません。増加スピードをマジックナンバでハードコーディングするよりはよっぽどマシです。APサーバ側で定数にするのではなく、全てのマスタデータはDBで管理するべきでしょう。

　ブーストという概念があろうとなかろうと、APサーバ側で必要な情報は変わらないから、SQLで処理していれば直さないと行けないのはSQLだけとなります。

APサーバでやってると勘違いが起きる？

　SQLで処理すると、ストアドファンクションを使わなければかなり格好の悪いコーディングになります。先ほどのSQLではいくつかCASE式を使っていますが、これは単純な導出項目ですからDBサーバで計算しようが、APサーバで計算しようが負荷という意味では誤差です。

　ですから、

SELECT 
	-- カラムの羅列（省略）
	, CURRENT_TIMESTAMP
FROM 
	ユーザステータス AS a
	INNER JOIN 増加マスタ AS b
		ON a.ユーザレベル = b.ユーザレベル
	INNER JOIN ブーストマスタ AS c
		ON a.ブーストレベル = c.ブーストレベル
WHERE 
		a.id = ?;

　として、APサーバで計算してもそれで良いのですけれど……。

　DBサーバにとって何が負荷が大きいか分からない人が、APサーバで処理をやっていると、「せっかく計算したし実データを更新しておこう」という発想が生まれている様な気がするのです。
　いくらなんでも、そんなことはないか……。
　でも、それぐらいのことをしないと、計算上は止まらないんですけどね……。

シナリオ変更も予想（提案）する

　「艦これ」のようなシステムであれば、グラフィック担当、声優さんの制約があるので、システム側の一存で私がDB周りとプログラム担当で参加したとしてら、システム側の人間として、

　「資源の増え方を変えることができる」

という提案はします。
最低でも上ぐらいの準備をしておきます。

　例えば、「艦これ」であれば

　課金アイテムを買えば、大本営に「xxxを攻略のため、しばらくの時間、資源を多く廻して欲しい」という交渉を行える。というシナリオに変えて、500円払えば、24時間の間、資源の増え方が2倍になる。交渉のときに連れていく秘書？とレベルによっては、x倍になることも（レアイベント）もある。

　とすれば、課金で資源を買うよりも、「課金をしたくなる」という鬼の設定（笑）にもできるわけです。

　もちろん、新たなイベントを作るのは、声優さんをもう一度招集したり、グラフィックを増やしたりすることになるので現実的には難しいでしょうが、「システム的には簡単なのでいつでもできますよ」ぐらいのことは、システム側から提案すべきです。

　つづく。

「艦これ」もう少し思い出して考えてみた

艦これ

「艦これ」はあんまり関係ないのですが、スケールアウトについて。

　「艦これ」は既に MySQL Cluster を使っているなら関係ないのですが、何度も書きますけれど、「JOIN禁止」はスケールアウトと関係ない。

　Twitter規模になれば別の考え方が必要になりますが、取りあえず今は DBサーバ は1台だけれど、「スケールアウトのためにJOIN禁止」は「究極のバッドテクニック」ですのでやらないように！

　単純だから良いも、複雑だから良いもない。

　必要なのは効率的かどうかを考えることだけで、それはどんなシステムでも同じ。

「艦これ」は……

　「艦これ」は MySQL Cluster を使っているとのこと。私が始めた頃は、4・5個の鎮守府（サーバ群）があった様に記憶しています。

　MySQL Clusterと聞いて、DBは1つのクラスタ構成で処理されていると思ったのですが、障害は鎮守府（サーバ群）単位で起きている様で、「DBサーバの不調」である鎮守府で障害が起きている。というアナウンスがしばしばあった様です。

　つまり、図の様に

鎮守府（サーバ群）毎に、DBサーバクラスタを配置して、それぞれのDBサーバは連携してないというイメージのようで、全体が落ちないということは、DBサーバがシングルポイントになっている訳ではないということですね。

　ユーザ数60万、アクティブユーザ35万、同時ユーザ10万、更に増え続けていると言われていますが、鎮守府（サーバ群）単位で見ればもっと少ない。ということは、MySQL Clusterは、負荷分散ではなく、フォールトトレランスのために使っているのかな？

　私の記憶の範囲では、ゲーム内で他のユーザのデータが必要になる処理は、「ランキング」と「演習」しかなかったように思います。

　ランキングが鎮守府（サーバ群）単位なのか、全体なのか分かりませんし、リアルタイムなのか、ディレイあり（バッチ処理）なのかは分かりませんが、まず間違いなくバッチ処理でしょう。バッチ処理なら、鎮守府（サーバ群）を跨いでも全く大したことはない。

　また、「演習」では1日2回対戦相手を5名選んでいる（つまりバッチ処理やね）ようですが、リアルタイムで対戦するわけではなく、自分が誰かの対戦相手になったかどうかも分かりません。更に、鎮守府（サーバ群）を超えての割り当てはないのでは？

　一般的なネット対戦型ゲームなら、ユーザのデータが変化すると同時に対戦相手のデータも変化しますが、対戦相手のデータに変化のない「艦これ」は非常に単純で、基本的なゲーム部分はスタンドアローンでも十分可能でしょう。
　演習の相手は架空でも分からない（？）からね……。

更新処理はどうなってる？

　コネクション数や、それに関わるメモリーがボトルネックにならないように調整したら、次に来るのは「データ更新時の問題」になります。

　これはLOCKの問題や、I/Oの問題など様々なことが起こりますが、それは個別に見なければ分かりませんのでおくとして、更新については、亜種はいくつもありますが、「艦これ」のようなシステムなら、主に以下の様な処理になるでしょう。




　私なら、APサーバの負荷を下げるためにクライアントに抽選をさせてしまうかな……。まあ、抽選の元になるデータをクライアントに送信するのは嫌がる人が多いでしょうが、解析したり、改造したりする違法な人達は、ピックアップして退会させるなり、訴えるなりすればいいしね……。
　　　　＃ 将来、対戦型にバージョンアップするために……。
　　　　＃ まあ、そりゃそうかもな……。

　逆に、もし、ログが必要なぐらい厳格なシステムであれば、ログは、UPDATE より軽い INSERTだけ（書き換えたらログじゃない）になりますから、ログだけ書き込んだら処理が続けられるようにします。ログが必要なシステムで、ログのINSERT とデータの UPDATE が終わるまで次に進まないシステムでは、大量アクセスになればそれだけ厳しくなる。

　余談ですが、似たような処理で、例えば、ECサイトであれば、注文ボタンでログ（というか仮注文）を書いて確認画面に遷移、確定ボタンでログのIDと共に実際のテーブルに書き込む。という処理もありますね。1回のユーザアクションで2回書き込むのは厳しいですが、ページ遷移が挟まれば、コネクションを掴みっぱなしの処理とは違い、DBサーバにとって大変な時間稼ぎになります。
　尤も、確定ボタン押下時のボトルネックは、大抵確認メールの方ですけど（苦笑）

個人的な感想

　私が無課金ユーザ（初回入会ボーナスの500円分は使いましたが）だからかも知れませんが、「艦これ」って待ち時間ばっかりで、激しくサーバにアクセスしようとしてもできない。同時オンラインのユーザが10万いても、一人一人は、1時間に何回も更新してないと思うので、外から見る限りそんなにシビアとは思えないのです。

　MySQL Clusterのベンチマーク http://www-jp.mysql.com/why-mysql/benchmarks/mysql-cluster/ を見る限り、2台のサーバで1秒間に100万回の UPDATE文を流せている。
もちろん、これは理論値に近いテストで、現実にはそのままのパフォーマンスは出ないでしょう。しかし、10万の同時ユーザが平均10秒に1回の更新（そんなにできる！？）が行われるとしても、1秒1万回です。
つまり、鎮守府を分けないで2台で組んだ最小のクラスタ構成でも、NoSQL の API を使っていれば理論上は100倍以上、現実的にはあと10倍ぐらいは対応できてもおかしくない。

　それでも「艦これ」が、
　　　　同時ユーザ10万 ÷ 鎮守府の数
　でトラブルるなら……。スタートラインのコネクション数のパンクの可能性も高いわけです。

　「艦これ」のようなシステムであればアニメーションの間は、APサーバも、DBサーバも別の処理が可能です。それをシーケンシャルに処理していたら、いずれも待ち時間が発生し、パンクの可能性も、ユーザのストレスも高まることになるでしょう。
（私はイラチなので滅茶苦茶ストレスが溜まった……）何度か落とされたタイミングのイメージでは、シーケンシャルな処理にして、NoSQLではなく、UPDATE の SQL を流す最悪のパターンの可能性すらあるんじゃないかと……。

　　　　# 時間経過毎に資源が増えますが、まさか、その度にUPDATEを流すとか……。
　　　　# まさか……、そんな……設計にはなってないよね。

　まあ、私には調べようがないけれど、例えば「艦これ」の対戦シーンなら、ユーザの1アクションに対して数回のUPDATE文を流すことになるハズです。ここで NoSQL(API) を使えるかどうかは非常に大きい。Oracle などの SQL をショートカットするような API がない場合は、この通信が1回で終わるように引数に詰め込んだストアドプロシージャにするべきでしょう。

　いずれにしても、「艦これ」に限らず、似たようなソーシャル系のゲームであればアニメーションという非常に長い余裕を作ることができます。「艦これ」ユーザはそのアニメーションを楽しんでくれる訳です。ですから、そのユーザを飽きないように何種類かアニメーションを用意して、クライアントでランダム（レアも用意）にアニメを流して時間稼ぎして、非同期処理すれば DBサーバに対する流量調節は十分にできるでしょう。

　私なら、「ここでx秒飽きさせないアニメーションをよろしく！」とか、厚かましく求めるけどね（苦笑）尤も、センスがないので「何をもって飽きさせない」かは、全く分からないのですが……。

当たり前の話が……

　プログラムをやったことのない人は、パチンコでリーチが掛かって祈ったり、叩いたりする人達が結構います（苦笑）「艦これ」もパチンコと同じように、演出（アニメーション）の途中で抽選をしていると感じる人もいるかも知れないけれど……。

　まあ、どのタイミングで抽選していても、祈ろうが、叩こうが関係ないですが（苦笑）

　パチンコでも「艦これ」と同じように派手な演出がありますが、もちろん、演出のほぼ全て（最近は演出を追加するためのボタンがついてたり）がチェッカーに球が通った瞬間に決まっています。

　「艦これ」もパチンコと同じで、「艦娘」の製造に20分掛かっても、クリックした瞬間（正確にはAPサーバに行ってからでしょうが）に何ができるかは確定しているハズです。戦闘画面では陣形を決定した瞬間に、「何が、どの順番に、どれだけのダメージを受けるか」（次のユーザアクションの）夜戦の選択に入るまでの全ての演出が決まっています。（違ったら腰抜かすほど驚くわ……）

　それが分かっていて、「艦これ」の待ち時間に耐えられるというのは……。イラチの私には理解できません……。

　「それを言っちゃぁ、お終めぇよ〜」ってことなんでしょうが……、もしかして、分かってない人の方が多いんじゃないかと……。

「艦これ」から、ソーシャル系のサーバ構成を考える

SQL概論 艦これ

　私は、ソーシャル系とは縁遠い仕事ばっかりしているのですが、そういう依頼も若干増えてきたので話題になっている「艦これ」をお盆にやってみた。

　残念ながら、「艦これ」の魅力は分からなかった。しかし、ミッションを用意されると、「クリアーしたい」という欲求から意地になるのは、何となく理解できました。それより、同時に始めた「Clash of Clans」には嵌まりました。気になっていた「ゲームの中に如何に自然に課金システムを取り入れるか」という課題についても、個人的には「Clash of Clans」の方が上手に解決しているように思います。

　「艦これ」は、同時アクセスが10万以上あって、何度かシステム障害があったとのこと（そりゃあるでしょうが……）。私の興味の方向性は、課金システムであったり、システム構成にあるので、「艦これ」のシステム障害の方が強い興味の対象になります（苦笑）

　というわけで、「ソーシャル系で起こりがちな問題」というのを考えてみたい。
　「艦これ」は既に辞めて、参加待ちの人に席を譲りましたので、記憶の範囲になってますし、あくまで想像ですのでご了承ください。

SQLを使うか、NoSQLを使うか

　SIer系の技術者でも SQL が苦手な人が多いのですが、ソーシャル系の技術者ではもっと SQL が苦手な人が多いようです。そんな状況で、なぜ RDBMS を選択するのか私にはイマイチ理解できないのですが、「艦これ」に必要なデータは（画像、音声ファイルを除けば）非定型なものがなく、マスターデータは、攻略Wikiにほぼ再現されている程度です。整理すればエクセルでも事足りるレベルですので、確かに RDBMS を使った方が楽に開発できると思います。

　噂によると、「艦これ」では、MySQL Cluster を使っているとのことです。MySQL Cluster は良くできていて、SQL と NoSQL（SQLをショートカットしたAPI）のいずれでも利用可能ですから、「艦これ」のシステムを考えれば、参照系はSQL、更新系はNoSQL(API)で使うのが妥当でしょう。

　もし、更新系でNoSQL(API)を使用していなければ、かなり「痛い設計」ですが、有料オプション（のはず）
※ CGE(Carrier Grade Edition)に含まれていて、CGEは一部有料ですが、NoSQL(API)は無料のようです。
なので、大人の事情で NoSQL(API)を使ってない可能性もありますね。しかし、変更点は10人日程度あればできるでしょうから、当初、SQLを使っていたとしても、もう直している頃でしょう（ですよね！）

JOINを避ける？

　MySQL Cluster を使うなら、これまで書いてきたように効率的に SQL を書かないと意味がない。JOIN を避けたりしたら返ってDBサーバの負荷が掛かります。

　しかし、MySQL Cluster を使わないと考えて、「（お手製の）スケールアウトに備えて JOIN を禁止する」という話をよく聞きます。（例えばこんな記事 http://d.hatena.ne.jp/iad_otomamay/20110808/1312805917）

　「艦これ」は直接関係ないのですが、「JOINの禁止」が本当に正しいか考えてみます。

　結論から言うと、JOIN を避けスケールアウトしたところで DBサーバのパンクの可能性は減らないどころか、逆に増えることになります。

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

　そのメモリーの使い方ですが、データを毎回ハードディスクから取り出していては間に合わないので、メモリーの7〜8割をデータのキャッシュに割り当て、残りを作業エリアに割り当てるのが一般的でしょう。

　残りの作業エリアがどのように利用されるかは、コネクションとセッションについて理解する必要があります。APサーバとDBサーバは、まず、コネクションを確立します。コネクションを確立する処理は非常に重く、処理の度に行うわけにはいかないので、通常、接続したままにして使い回します（コネクションプール）

　図の様になり、DBサーバはコネクションの中にセッションを作り、セッションの中でクエリーを発行します。

　クエリーを処理するためにセッション毎に利用するワークメモリーの大きさは、初期パラメータの Sort_Buffer_Size で指定します。（動的にも変更可能）
　通常、Sort_Buffer_Size は 128KByte〜2MByteぐらいで指定します。

　一連の処理で、別々にコミットしたい処理などのときにセッションを分けることがありますが、ソーシャル系のシステムなら、ほぼ、1コネクションに対して、1セッションで十分でしょう。しかし、同時アクセスが10万でその通りDBサーバで処理するなら、小さめの 128KByte の Sort_Buffer_Size を指定しても、

　128KByte × 100,000コネクション（セッション） ＝ 12.8GByte の作業メモリーが必要になります。

　MySQL の設計上の最大コネクション数は10万ですが、1つのコネクションにつき OS のスレッドを1つ必要とし、OS もスレッド毎にメモリーを確保しますので、現実的には DBサーバは10万もの同時アクセスはなかなかこなせません。ですから、APサーバがコネクションプールを作って、DBサーバに対する最大のアクセス数をコントロールするわけです。そのため、大量アクセス時のボトルネックは コネクション数か、それに対するメモリー量 となることがほとんどです。

　※ DWH（Data Ware House）などのシステムは、同時接続ユーザ数が数名〜数十名となるので、コネクション数がボトルネックになることはまずなく、1つの処理でスワップするような大きな処理の場合は、分割が必要になります。

JOINを避けてDBサーバをスケールアウト

　「艦これ」のようなシステムの場合、一人のユーザ情報が2KByteで100万ユーザいたとしても 2GByteですから、マスター系は一切スケールアウトは必要ありません。そのようなシステムでJOINを避けてスケールアウトしたとすると、図の様になるでしょう。
　

　JOINをするような処理の場合、1処理が終わるまでの間、どちらのコネクションも解放できません（しません）から、スケールアウトしてサーバ数を増やしても、スケールアウトしなかったDBサーバのコネクション数を増やすことができず、スケールアウトする前と同じ状態のボトルネックになってしまう（Sort_Buffer_Size を小さくできれば話は別ですが……）3台にスケールアウトすれば、スケールアウトしないDBサーバへのコネクションプール数は、スケールアウトしたDBサーバへのコネクションプール数の3分の1にしなくてはならないのですが、全部、スケールアウト前のコネクションプール数にしたままにして、スケールアウトした方が不安定になるなんてことも、見たことがあるような、ないような……（苦笑）

　JOINに限らず、APサーバで処理をすることによって遅くなるなら、DBサーバに対するコネクションを掴んだままになるため、結果的にDBサーバに負荷を掛けてしまうのです。

　つまり、無駄に遅いだけで、ユーザにも、システムにも何も良いことはありません。

「複雑だから負荷が高い」と思うのは大きなお世話！

　複雑だろうが、単純だろうが、シビアな要件になればなるほど、「速いことが正義」になります。

　速ければ、たとえその瞬間の負荷が高くても、すぐにリソースを解放してくれるので問題ないし、複雑なことで負荷が限界を超えるなら、その処理でディスクソートが入るということになりますから、極端に遅くなるためすぐに分かります。（その処理が行われるセッションだけ、動的に Sort_Buffer_Size を大きくしても良いのですが、ご利用は計画的に！）

　つまり、書いてはいけないほど複雑かどうかは、「メンテできるか」を除けば、「速いかどうか」で判断すればよい。「メンテできるか」は要するに要員のスキルの問題ですから、RDBMSを使いながら（APIを使わずに）SQLを避けて得られるメリットは、「下手糞に合わせる」以外にないということになる訳です。

　くれぐれも、プロなら、「遅いけれどサーバの負荷は下がる」という世迷い言はいわないように！

感想

　今回、20年ぶりぐらいに「艦これ」と「Clash of Clans」と2つのゲームをやってみました。
　私はソーシャル系のシステムは未経験なので、実際、どれぐらいの負荷が掛かっているかは、外から見たシステムの状況から理論値で予想するしかありません。

　「Clash of Clans」も、それなりに同時ユーザがいてそうで、システムの複雑さ、そのパフォーマンスと安定性をみると、Flashゲームというハンデを差し引いても、「Clash of Clans」の方が何倍も上ですね。

　尤も、「Clash of Clans」は何らかの NoSQL（多分、MongoDBでしょう）を使っているはずですが（苦笑）

　クライアント処理のアニメーションなんて、レスポンスが遅いことの隠れ蓑（スプラッシュ画面と同じ）ですからね。「艦これ」なら数秒は余裕がある。
　最近のハードウェア環境で、「艦これ」の状態が良いときのレスポンスで良いなら、10万同時アクセスぐらい捌けそうなものなんですけどね……。

　もちろん、「艦これ」がこんな幼稚な理由でトラブっているのではないと信じていますけれど、MySQL を使いながら SQL を避けたときに起きる挙動に似てるな〜。とは、個人的に思っています。（違うことを祈る！）

　とにかく、RDBMS は SQL が最大のボトルネックになるのです。

　SQLを使わないことでそのボトルネックを避けてるつもりでも、NoSQL の API を使わない限り、必ず最大のボトルネックの SQL を通ってしまいますから、RDBMS を使うなら、シビアなシステムになればなるほど、SQL を極めなければいけない。

　年々、RDBMS を使いながら SQL を避ける人が増えていますが、SQL を使いきれば数倍〜数十倍のレスポンスが出せる場合がほとんどです。ということは、難しく考えなくても、同じハードウェア環境で数倍〜数十倍のアクセスに耐えられる。

　実に単純なことなんですけどね……。

SQL文を組み立てるには？SQLが書ける様になるには？

SQL概論

　SQL文ができない人は、どこまでも文法で考えようとしている。

　私が、SQLに違和感を感じずに理解することができたのは、文法を気にしてなかったからで、私の勉強法というか、どうやって習得してきたのかを整理してみました。

例えば IN と EXISTS の違い

　カラオケで説明します。
　カラオケ10万曲から、とある曲リストと一致する曲を抽出するとします。

　手続き型では、次のいずれかになるでしょう。（細かいところは端折るよ）

Aパターン

	foreach (string 曲名 in 曲リスト){
		結果セット.add(カラオケ.find(曲名));
	}

Bパターン

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　Bパターンなんて使わないと思いますか？
　「とある曲リスト」が「男性ヴォーカリストの曲」でも？


　COBOL世代の人は処理時間が

　　　　　カラオケ.find(曲名) >> 曲リスト.exists(曲.曲名)

　となると理解しているので、「とある曲リスト」の件数が、母数に比べてどれぐらいの割合か（インデックスがあるなら25％以上、インデックスがないなら数件以上ならBパターンにすべき）考えてプログラムを変えます。
　※ ただ、COBOL世代の人は .find のロジックをそこにベタ書きする人が……（苦笑）

　この使い分けが普段からできてない人は、「SQL以前に手続き型でも全然できてない」ってことになってしまうので、もうちょっと修行すべきですね。

Aパターンのプログラムが欲しい

　Aパターンのプログラムが欲しいと思って仕様書を書こうとするとどうなるでしょう。

　　　　何から取ってくるか書く場所（つまりFROM句）
　　　　どんな条件で取るか書く場所（つまりWHERE句）
　　　　何が欲しいか書く場所（つまりSELECT句）

　結果セットとして欲しいモノは、あくまでもカラオケの曲ですから、何から取るかは「カラオケ」になります。
　何が欲しいかは特に指定してない（曲全体）ですから * で良いでしょう。

　問題はどんな条件かしかありませんが、Aパターンでは、まず、「曲リスト」が必要になります。

　これは外付けでもできますね。

　(SELECT 曲名 FROM 曲リスト) = ('逢いたくて', '愛のメモリー', ……)

	SELECT *
	FROM カラオケ
	WHERE 曲名 IN ('逢いたくて', '愛のメモリー',  ……);

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '逢いたくて' 
		OR 曲名 = '愛のメモリー' 
		OR ……;

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '逢いたくて'
	UNION ALL
	SELECT *
	FROM カラオケ
	WHERE 曲名 = '愛のメモリー' 
	UNION ALL
	……;

つまり、
	SELECT *
	FROM カラオケ
	WHERE 曲名 IN (SELECT 曲名 FROM 曲リスト);

　無茶苦茶、書いていますが、どのパターンで仕様書（SQL）を書いても、実行計画（＝プログラム）は同じになります。（むしろ、実行前に UNION ALL の SQL に展開することが多いはず）

Bパターンのプログラムが欲しい

　FROM句まではAパターンと同じですね。

　抽出条件としては、FROM句のカラオケをループしながら「曲リスト」に存在するかチェックして欲しい。

　これを表現する手段としてサブクエリの中に、FROM句のカラムを入れ込むのが分かり易いと考えて、その表現を決めました。

	SELECT *
	FROM カラオケ
	WHERE
	EXISTS (SELECT * FROM 曲リスト WHERE カラオケ.曲名 = 曲リスト.曲名)

　（INで書いてもFROM句のカラムを指定したら同じですが）

　サブクエリの中にFROM句のカラムが入っていますから、サブクエリ単独では実行することができないのが特徴です。

　一般的な仕様書ならどう表現しても良いので、「Bパターンのプログラムが欲しい」と考えて出来の悪いプログラマ向けに仕様書を書いてみたら良い。その仕様書を、SQLの文法に当て嵌めれば、EXISTSを使ったSQLになるハズです。

　文法から考えるのではなく、まずは、どんなプログラムが欲しいか、じっくり考えてみたらどうでしょう。

逆にSQL（仕様書）からプログラムを書く

　逆にも考えてみましょう。
　あなたが下のSQL（仕様書）を受け取ったとしましょう。

	SELECT *
	FROM カラオケ
	WHERE 曲名 IN (SELECT 曲名 FROM 曲リスト);

　あなたのプログラムはこうなりますか？

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　SQLに限らず、カッコの中から先に解析・実行するのが普通で、カッコの中を先に解析・実行すれば、Bパターンのプログラムは出てこないでしょう。
　逆に、こちらのSQLでは、

	SELECT *
	FROM カラオケ
	WHERE
	EXISTS (SELECT * FROM 曲リスト WHERE カラオケ.曲名 = 曲リスト.曲名)

　カッコの中を解析しようとしても単独では実行できないので、普通にプログラミングすれば、Bパターンのプログラムになるでしょう。

	foreach (Song 曲 in カラオケ){
		if(曲リスト.exists(曲.曲名)){
			結果セット.add(曲);
		}
	}

　私は実行計画の見方すら分からない初心者の頃（インターネットもほとんどない時代ね）から、このSQLならプログラムはこうなるはずで、そうじゃないプログラムを返すDBエンジンは創れない。実行速度から間違いない。と常に考えてコーディングしていました。
　その後、実行計画の見方を覚えて確認したら、思った通りで安心したという経緯を辿りました。

　スタートからSQLは仕様書で自動でプログラムを書いてくれるモノ、と考えて来たからSQLを書くことに何の違和感も感じないだけなのです。
　（後は経験の問題ですけれど……）

　逆に、嫌な言い方をすれば、「IN と EXISTS の違い」が分かってないという人は、SQL文が（直接実行される）プログラミング言語と思っているから中々理解できないし、現実のプログラムと全く繋がってないし、「文法を覚えて何とかしよう」と思ってしまうのでしょう。

　余談ですが、私がHTMLを覚えたときも同じやり方。
　「自分がブラウザを作るとしたら、HTMLはこんな言語仕様になってないとブラウザが作れない。だから、言語仕様はこんな感じだろう」と荒く考えて、細かい仕様はリファレンスを引く訳です。

SQLが仕様書なら手続き型と等価

　ちょっとしたプロジェクトに入ったことがある人なら、何度も経験があるはずですが、プログラミングができない人が書いた仕様書は、何が書いてあるか分からないことが多々ある。逆に考えれば、プログラミングができるなら、「仕様書であるSQLは効率的に簡単に書けるはず」なんです。

　ところが、プログラミングができるのに仕様書であるSQLが書けない人が沢山いるのは、SQLとプログラムを別物と考えているからです。（くどいけどSQLを魔法か呪文と勘違いしている）

　良いSQLを書くには、Aパターンのプログラムが欲しいのか、Bパターンのプログラムが欲しいのかが、まず、あなたの頭の中になければ書けない。これは大前提。
　裏を返せば、SQLがまともにできる人は、同時にプログラムもできているはずです。

　自分が欲しいプログラムが出てきそうな仕様書（SQL）を書き終わったら、それを貰ったプログラマ（DBエンジン）の立場になってプログラミングしてみる。更に、確認として、実行計画が問題ないか見るのです。

　これを繰り返していれば、自然に良いSQLが書ける様になる。DBエンジンが変わっても、ある程度予想できるし、RDBMS毎の文法の違いなんて、プロジェクト毎の仕様書のフォーマットや、お作法の違い程度の差しか感じなくなります。（いちいち、リファレンスを引いても大したことない）

　しかし、文法から入っていると、RDBMS毎の文法の違いが気になって仕方がないでしょうし、ちょっと変わればできなくなる。そういう人は、本質が分かってないのです。


　この規模のSQLになれば、書くのも、読むのも、SQLと手続き型を行ったり来たりしないと無理です。逆に行ったり来たりできるレベルになれば、30分もあればできる様になります。

　また、SQLと手続き型を行ったり来たりできるレベルになれば、「小さなSQLに分割した方がDBサーバの負荷が減る」なんて勘違いは絶対に起きないし、逆に、システム全体を俯瞰して本当に分割すべきポイントも見えてきます。

　しかし、現実的には、多くのプロジェクトで、「小さなSQLに分割した方がDBサーバの負荷が減る」なんて勘違いする連中がクラスタ構成までやっている訳で……、私には悪い冗談としか思えない。彼らは「できる」つもりになっているけれど、彼らのいう「できる」は、「クラスタを構成するツールを使うことができる」であって、「本質を分かって構成することができる」では決してないのですから。

　とにかく、SQLは仕様書ですから、手続き型プログラムと等価でなければおかしい。

　そこが分かってない人は、本質的に何も分かってないのです。

RDBMSを使う以上、SQLを使いこなさなければいけない

SQL概論

　何度も何度も言ってるけれど、「処理を分割した方がDBサーバの負荷が減る」と感じるのは勘違い。
http://d.hatena.ne.jp/Sikushima/20110809/
http://d.hatena.ne.jp/Sikushima/20110810/
http://d.hatena.ne.jp/Sikushima/20110811/
http://d.hatena.ne.jp/Sikushima/20110815/

落ちるのはピークしかない

　http://d.hatena.ne.jp/Sikushima/20110815/ にある通り、SQLでDBサーバでできることをAPサーバで行ってもDBサーバの全体の処理は減らない訳です。
　サーバに掛かる負荷というのはピークではなく面積で見なければいけないけれど、処理が減らない以上、DBサーバの1回の処理の面積は分割した方が大きくなる。

　この状態でアクセスが輻輳すれば下のようになる。

　SQLの処理を避ければ、DBサーバの負荷は高くなるわけです。

　もっと重要なことは、SQLで処理を行ったときのAPサーバの負荷に比べ、SQLを避けて処理したときのAPサーバの負荷は何倍も高くなります。

　ですから、APサーバが複数あったとしても、パンクするのは大抵APサーバの方でしょう。それで、「もし、DBサーバで処理してたら……」って言い訳してる場面を何度も見てきました。

　ところが、本来はDBサーバで処理していたら、APサーバがパンクする時期も随分ズラせてた訳です。（もちろん、Webサーバのパンクはどうにも出来ないけどね）

　すぐパンクするAPサーバをパンクさせないためにも、レスポンスを早くするためにも、DBサーバのパンクを防ぐためにも、SQLを使った方が良いわけですから、何度も言ってるけど、SQLを避けて得られるものは、「下手くそに合わせることができる」の一点しかないのです。

APサーバで行った方がよい処理もある

　とはいえ、もちろん例外は存在する。

　例えばこれとか。元々、メモリー上で処理が完結するようなモノを、わざわざDBサーバを使う必要がないのは当たり前。

　もう一つ、パターンがあって、前回の勉強会で出た問題。

というテーブルから、以下の様に一番多い連勝と連敗の数を出力する。

　何となくできそうな気はするんだけれど、一晩考えて（といっても徹夜するわけではなく夢の中で整理するんですが……）やっぱり答えが出なかった。

　勉強会で出た答えはこれ。

SELECT   勝敗値,
         max(cnt)
FROM     (SELECT   勝敗値,
                   count(*) AS cnt
          FROM     (SELECT 勝敗値,
                           row_number() OVER (ORDER BY 日付) - row_number() OVER (PARTITION BY 勝敗値 ORDER BY 日付) AS cnt
                    FROM   試合結果 AS s) AS a
          GROUP BY 勝敗値, cnt) AS a
GROUP BY 勝敗値
ORDER BY 勝敗値;

　まあ、正しいのですが応用は効かないし、どう考えてもAPサーバでやった方が効率的。

　再帰SQLでやるこちらはすぐに思いついたけれど、内部的にレコード数回SQLを発行するというとんでもないSQLで、こちらも、私としては答えとは認められない。

WITH
m(ID, 日付, 勝敗値)
AS(
	SELECT 
		ROW_NUMBER()OVER(ORDER BY 日付), 日付, 勝敗値
	FROM 試合結果
)
, cte(ID ,開始日付, 日付, 勝敗値, 連勝数, 連敗数)
AS(
	SELECT
		ID 
		, 日付 AS 開始日付
		, 日付
		, 勝敗値
		, CASE WHEN 勝敗値 = 1 THEN 1 ELSE 0 END AS 連勝数
		, CASE WHEN 勝敗値 = 0 THEN 1 ELSE 0 END AS 連敗数
	FROM m 
	WHERE ID = 1
 	UNION ALL
	SELECT 
		m.ID
		, CASE WHEN cte.勝敗値 = m.勝敗値 THEN cte.開始日付 ELSE m.日付 END AS 開始日付
		, m.日付
		, m.勝敗値
		, CASE WHEN m.勝敗値 = 1 THEN cte.連勝数 + 1 ELSE 0 END AS 連勝数
		, CASE WHEN cte.勝敗値 = 0 THEN cte.連敗数 + 1 ELSE 0 END AS 連敗数
	FROM m INNER JOIN cte ON m.ID = cte.ID + 1
)
SELECT * FROM cte; -- 後は好きにサマリーを取る


　要するに RDBMS はシーケンシャルにデータを保たない。という考えから、シーケンシャルな処理を非常に苦手にしている。
　　　　# 分析関数でLAGなどを使えばできるけどSQLServerは未実装なので……。

　このような処理は、必要なデータを転送して素直にAPサーバでやるか、ストアドプロシージャを組んだ方がよろしい。

　何でも例外はあるけれど、それはあくまで例外であって基本は理解するべき。

明日、勉強会の席が空いてます！

　まあ、何が言いたかったかというと宣伝。
　明日、勉強会の席が空いていますので、お時間がある方はいらっしゃってください。

http://sqlworld.org/event/20130827/

SQLWorld★大阪#16 開催情報
【日時】
2013年8月27日（火曜日）　19:00~21:00

【イベント概要】
SQLWorld 3回目の平日夜開催〜。今回も、みんなで SQL を書いてみようというハンズオン企画です！ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0001 大阪府大阪市北区梅田 2-4-9 ブリーゼタワー 12F
http://info.fenrir-inc.com/jp/profile/overview.html
ＪＲ大阪駅、ＪＲ北新地駅、地下鉄梅田駅・東梅田駅・西梅田駅と各駅からアクセス可能です

【参加費】
無料

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)

【参加可能人数】
13 人

【Twitterハッシュタグ】
#sqlworld

CODE VS 2.1 をSQLでやるとどうなる７

CODE VS

　前回、こんな「落ちゲー」の

　ブロックを一つ一つ加算していって、合計が10になる組合せをSQLで探しました。

　今回は消すお邪魔ブロック（最初から10以上（＝ 11）のブロック）を探すSQLを書きます。

繋げるか、一旦テーブルに書き出すか。

　前回でも充分な長さのある SQL になりました。
　更に繋げるのが良いか悪いかは微妙で、当初の設計では、Deletedテーブルに一旦保存するつもりでした。
　しかし、繋げた方がパフォーマンスが出そうなので繋げることにします。
　嫌な人は、前回の結果をテーブルに書き出してから続けましょう。

　前回は4方向に加算していきましたが、今回は消えるブロックの周りにあるお邪魔ブロックを探すことになりますので、1回だけですみます（再帰なし）また、お邪魔ブロックからは逆向きに探してこないため、4方向ではなく8方向を調査することになります。

　4方向用のDirection4テーブルと、8方向用のDirection8テーブルの2つ作っているのは、単純にサブクエリーが増えるのを避けたかったからだけで、サブクエリーでも良いと思う人は、Direction8に区分を追加してサブクエリーでやってください。

　ちと長い……。

WITH
	cb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 4方向に合算された結果
	AS(
	SELECT
		GameID
		, SimulationID
		, Turn
		, Row
		, Col
		, Row AS ERow
		, Col AS ECol
		, Num
		, Num AS amount
		, 1 AS cnt
		, 0 --調べている位置
		, CAST(N'調査' AS nvarchar(8)) AS Memo
	FROM Board
	WHERE GameID =-1 AND Turn = 0	AND Num < 10 
	UNION ALL
	SELECT 
		cb.GameID
		, cb.SimulationID
		, cb.Turn
		, cb.Row			-- 調査元の行
		, cb.Col		-- 調査元の列
		, bd.Row		-- 調査先の行
		, bd.Col		-- 調査先の列
		, bd.Num
		, cb.Amount + bd.Num
		, Cnt + 1 
		, dr.Angle
		, dr.Memo
	FROM 
		Board AS bd
		INNER JOIN (cb
		CROSS JOIN Direction4 AS dr)
			ON bd.Row = cb.Row + (dr.RowOffset * Cnt)
			AND bd.Col = cb.Col + (dr.ColOffset * Cnt)
			AND bd.GameID = cb.GameID
			AND bd.SimulationID = cb.SimulationID
			AND bd.turn = cb.turn
	WHERE
		cb.amount + bd.num <= 10
	)
-- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる
	, eb (GameID, SimulationID, Turn, Row, Col
		, ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 消すブロック
	AS(
	SELECT * 
	FROM cb AS bd
		WHERE EXISTS (
			SELECT * FROM cb 
			WHERE bd.row = cb.row 
				AND bd.col = cb.col
				AND (bd.angle = 0 OR bd.angle = cb.angle)
				AND cb.amount = 10
			)
	) -- WITHの終わり
-- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる
-- SELECT * FROM eb;  -- として、以下をコメントアウトすれば消すべきブロックを確認できる
	, ob (Row, Col, ERow, ECol, Num, Angle, Memo) -- お邪魔ブロック
	AS(
	SELECT 
		eb.ERow			-- 調査元の行
		, eb.ECol		-- 調査元の列
		, bd.Row		-- 調査先の行
		, bd.Col		-- 調査先の列
		, bd.Num
		, dr.Angle
		, dr.Memo
	FROM
		eb -- 上のSQLで作成した消すべきブロックの一覧
		INNER JOIN (Board AS bd
		CROSS JOIN Direction8 AS dr)
			ON eb.ERow + dr.RowOffset = bd.Row
			AND eb.ECol + dr.ColOffset = bd.Col
	WHERE
		bd.GameID = -1 AND bd.Turn = 0
		AND bd.Num > 10
	)
-- SELECT * FROM cb;  --として、以下をコメントアウトすれば4方向に合計していったときの結果が確認できる
-- SELECT * FROM eb;  --として、以下をコメントアウトすれば消すべきブロックを確認できる
-- SELECT * FROM ob;  --として、以下をコメントアウトすれば消すべきお邪魔ブロックが確認できる
	, curEb (ERow, ECol) -- このチェインで消すブロック（重複あり）
	AS (
		SELECT ERow, ECol FROM eb -- 通常のブロックは重複あり
		UNION ALL 
		SELECT ERow, ECol FROM ob GROUP BY ERow, ECol -- お邪魔ブロックは重複なし
	)
-- SELECT * FROM cb;  --として、以下をコメントアウトすれば4方向に合計していったときの結果が確認できる
-- SELECT * FROM eb;  --として、以下をコメントアウトすれば消すべきブロックが確認できる
-- SELECT * FROM ob;  --として、以下をコメントアウトすれば消すべきお邪魔ブロックが確認できる
SELECT * FROM curEb; --今回のチェインで消すべきブロックの数が確認出来る。

つづく