イチから学ぶRDB再入門　ゲーム開発を進化させるために知っておくべき基礎知識

効率的なゲーム開発のためのRDB再入門

生島勘富氏（以下、生島）：改めまして、ジーワンシステムの生島と申します。よろしくお願いいたします。

まずは自己紹介です。一部で「SQLオジサン」と揶揄されていますが、私自身はSIer系からWeb系、IoTなどを幅広くやっていて、開発やコンサルなどをやらせていただいております。

実はゲーム系のお仕事は一切やっていません。ですので、今日はゲーム系のお話も入れさせていただいていますが、「そこは違うよ」というところは突っ込んでいただければと思います。今回は、基本的にみなさんが勘違いしているところを簡単に、広く浅くご説明させていただきたいと思っております。よろしくお願いいたします。

DBサーバの構造を知ろう

では、DBサーバの構造について。この章は、RDBがどういう構造でデータを読み込んで、どういう構造で書き込んでいるか。それから、安定運用に欠かせないリカバリーについて説明したいと思います。よろしくお願いします。

まず、いきなり低レイヤーな話からスタートしますが、「RDBのデータの内部構造はどうなっているか？」についてです。RDBはなんとなく「レコード単位で保存している」というようなイメージを持たれる方もいらっしゃると思いますが、実はレコード単位ではデータを保存していません。ページ単位です。

ページのサイズは、こうしたMySQLのInnoDBの場合ではデフォルトで16Kになっています。そして、データの上にヘッダーがあって、レコードがずっと並んで、最後に空きがチョロッと残るかたちで保存しております。

1件のレコードを読むにも、必ず16K取ってしまいます。（16Kのページ全体を）読み込んで、そこから切り出して、自分のほしいデータを読む、というかたちになっています。これが基本です。

ただ、最近はインメモリDBというものが出てきていますが、インメモリDBは実はレコード単位で保存しています。インメモリDBは、ハードディスクを意識する必要がないので、レコード単位で保存しています。インメモリDBの場合、書き込みが速いのは当たり前ですが、読み込みも速くなります。

いまのRDBは、日常的に使われているデータのほとんどバッファに載った状態で運用されていると思います。特に、我々の業界ではバッファヒット率が99パーセントという状態で運用しているものもいっぱいありますが、そういう状態でもインメモリDBのほうが速くなります。というのは、このページの構造か、レコード単位で保存しているか、という違いが大きいので、インメモリDBのほうが必ず速くなります。

ですので、これについて誤った説明をされている人もいるので、ここは覚えておいていただければと思います。

インデックスについて

続いてインデックスについてです。

インデックスも……これはルートなんですが、ルートのデータも16Kで作られています。（各インデックスを指しながら）16K、16K、16K、16K、というかたちで読んでいきます。インデックスのもとのキーとアドレスが入っている状態のものが分かれていくわけですね。分かれていって、最後の実データを読みます。

InnoDBのプライマリキーになっている場合は、（インデックスのリーフを指して）ここが実データになっています。最後が実データになっているので一段階少ないです。この、リーフの最後がインデックスになっているものをクラスタードインデックスといいますが、クラスタードインデックスになっていないDBの場合は、実データがバラバラに入っています。

だいたい入力時点のシーケンシャルが順番に入っているという状態になるんですが、InnoDBのクラスタードインデックスはこのような構造になって、（インデックスのリーフを指して）ここが実データになっていますから、プライマリーキー順に並んだ状態でセットされているという、ちょっとした違いがあります。

クラスタードインデックスになっているものとして、MySQLとSQL ServerというDBが有名で、それ以外はクラスタードインデックスになっていませんから、Postgreなどとは違いがあります。

RDBの更新処理

では、RDBの更新処理はどうなっているかについて説明していきます。

更新処理が入ると、基本的にはハードディスクからメモリへ突っ込んでいます。そのメモリを更新処理で書き換えたと同時に、ログファイルに書き込みます。

Commit完了したら、ログファイルに書き込むんですね。そしてログファイル自身がシーケンシャルからファイルに追記するだけですから、追記は非常に速いです。

（ハードディスクを指して）ここは先ほど言いましたようにページ単位で保存されているので、ハードディスクのいろいろなところにデータがバラバラに入っていることがあります。そのため、書き込みには非常に時間がかかるけれども、ログファイルを書くだけなら速いので、ある程度のスピードで書き込みが終わります。

つまり、（ハードディスクを指して）このデータも最新じゃない、（ログファイルを指して）これももちろん最新じゃない、（メモリを指して）これも全部持っているわけじゃないということで、実は基本的に最新のデータをカチッと一貫して持っているところは運用中はほぼない、という状態で運用されています。基本的には不整合が起こっている状態で運用されている、ということになっています。

（ハードディスクを指して）ここは遅延書き込みをするんですが、遅延書き込みの前にプロセスが飛んでしまったら、メモリのデータは当然飛んでしまいますよね。しかし、次の起動時に自動的にログファイルから書き込んでいない分を（ハードディスクに）書き込んで起動する、という機能があります。

そういう機能があるので、Commitされたデータは基本的に保障されています。そのため「RDBはクラッシュセーフなんです」という言い方がされています。

DBが飛んでしまったら

バックアップについては、DB自体がクラッシュしてしまった。そして、1週間前までのバックアップしかない、という運用をしていたとしても、1週間分のログファイルがあれば、最後にCommitしたところまでデータは戻せることになります。

かつて業務システムをやっている人たちで、最大2年ぐらいバックアップを取っていなくて、2年前に取ったバックアップと（2年分の）ログファイルだけ、ということをやっていたところもありました。昔は1年ぐらいバックアップを取っていないことは普通にありましたね。

最近は、バックアップをまめに取ってもファイルサーバのハードディスクなどに取ることが多いので問題ありませんが、昔はテープで取っていたので、（バックアップが）けっこう大変でした。そのため、「1年に1回しか取らない」みたいなところもけっこうありましたが、それでも運用できていました。

基本的に、バックアップを戻すのがリストアで、リカバリというのは「コールドバックアップ＋ログファイルで障害の直前まで戻してね」ということです。

ですので、ちゃんとログファイルを別のところに逃がしておかないとダメなわけです。

ログファイルは、デフォルトではおそらく3個のファイルをローテーションするようなかたちになりますから、しばらくすると消えてしまいます。だから、上書きされる前に逃がしてあげる設定をしておくことが必要です。そういう設計をすれば、安全に運用でき、いざというときに戻すことが可能です。

すこし説明が漏れましたが、ログファイルはレプリケーションにもよく使われます。バックアップとログファイルを持っていたら、横にコピーが作れます。いまの戻し方と同じことをやればいいということで、レプリケーションにも利用されます。

パフォーマンスに効く設定

では、「こういう構造だからこそ、パフォーマンスに効く設定がありますよ」ということで、考え方を説明します。設定はたくさんありますので、本当に簡単なものだけを説明しますが、興味を持たれた方はご自身でも調べてみてください。

まずはページサイズです。

でも、RDSでは変更不可です。「コンパイルからやり直せ」というようなことになるのかな。MySQLはコンパイルからやり直さないと、たぶん変えられません。変えてからデータを突っ込むかたちになるので、最初の設計段階で決めておかないと大変です。RDSを使っていたらあきらめてください、ということですね。

個人的に、ゲームであれば1レコードにアクセスするのに16K読むなんて、「どう考えてもちょっとでかすぎるよな」というのがたぶんあると思うので、ゲームだったら4Kからスタートして、4K、8Kというように試していくのが良いのではないかと思います。

それもゲームの特性によって違いますから、「絶対にこれぐらいがいい」とは言えませんが、「4Kからいったほうがいいんじゃないの」というのが、私の意見です。これは単なる意見で、「現場を知らない人間が言うな」と言われるとそれまでなんですが（笑）、私だったら「もし設計するとしたら4Kから攻めるな」ということです。

これは当たり前ですが、バッファサイズ、データをキャッシュするメモリサイズは物理メモリの8割ぐらいを割り当てていくことが多いので、できるだけ大きく取ってあげたほうがいいです。

我々は最近はほぼ99パーセントのキャッシュヒット率になるように割り当てることが多いですが、できれば90～95パーセントぐらいあったら、非常に速いのではないかと思います。できるだけ高いヒット率を目指してください。

Writeに効く設定

続いて、Writeに効く設定です。

Writeはやっぱりログファイルが効くんですね。ログファイルはデフォルトで1ギガなんですが、「1ギガぐらいすぐいっちゃうよ」というゲームもたぶんあるんじゃないかなと思います。

バージョンによって違いますが、ログファイルの75パーセントまで書き込まれたら、自動的にハードディスクに書き込んでいない分を書き込もうとします。

これをデータフラッシュといいますが、それが起こるときは、だいたいピークのときですよね。ユーザーアクセスが集まった時に、ログファイルは育ちやすくなると思います。急に育ったら、「あ、これじゃあかん。書き込もう」という感じで、ピークのときにハードディスクの書き込みが起こってしまうと、それがボトルネックになってしまい、それが障害の原因になりやすいです。なので、ログファイルは大きめにしてください。

いまのバージョンでは、確か最大で24ギガか32ギガだったと思います。それはちょっと大きすぎると思いますが、大きめにしたほうがいいですね。

大きすぎるとDBの再起動が遅くなるというのは事実ですが、ある程度の大きさを取っていただいたほうが、きっと安定すると思います。

遅延書き込みのタイミングを知っておきたいという人は、バージョンによって違うのでマニュアルを読んでください。もちろん遅延書き込みは、スクリプトを書いて手動でやることもできます。「いまからフラッシュして」というコマンドももちろんありますので、それも調べていただければと思います。

ログファイルへの書き込みを1秒おきにする

我々業務屋さんは絶対にやらない設定として、通常はCommitごとにログファイルに書き込みますが、1秒おきにログファイルに書き込むという設定が、MySQLにはあります。

これを課金サーバでやったら大変なことになりますよ！　だから、課金サーバは絶対に別のDBで立てておいていただかないといけないのですが、課金サーバ以外の処理はこれにしてしまう。

つまり、こんなゲームの場合ですね。

APサーバとの間でバトル中は回っていたり、キャッシュサーバをはさんで回っていたり、むしろクライアントとサーバ間で回さずに、クライアントだけで処理していたとします。

最終的にバトルが終わったらDBに書き込むよ、というような処理をしていると、DBが途中でクラッシュしても、まだクライアントは生きているんですよね。ということは、1秒のクラッシュセーフは意味がなくて、クライアントとDBの同期がぜんぜん取れていないじゃないですか、ということなんですよ。

「1秒どころじゃない不一致が起きているのだから、1秒ぐらいログファイルへの書き込みが消えたところでそんなに問題ないよね」というふうに考えれば、これを1秒ごとにするだけで書き込みの回数がグンと減りますから、それも安定稼働につながります。

したがって、「いや、そんなことは許せん」という方もいるかもしれませんが、ぜひ検討していただければと思っています。このパラメータ（“innodb_flush_log_at_trx_commit”）を2にして再起動していただければ、ファイルへの追記が1秒ごとになります。

Commitごとだと、1人がバトルが終わったら書き込み、書き込み、書き込みになって、1万人ぐらいがずっと同時にアクセスしていたら、おそらくしょっちゅうぶつかることになりますから、これはぜひ検討していただけたらと思います。

これ（“innodb_flush_log_at_trx_commit”）が2のときの処理は、ログバッファというところに書き込んで、それが1秒おきにログファイルにいって消えるという作りがここに入る、という感じですね。

遅延書き込みは相変わらず行います。ピーク時に遅延書き込みが入ると、とても痛いことが起きてしまうということにもなりますので、できるだけログファイルは大きくしてください。

DBの設定についての説明はこんな感じです。

実行計画を見てみよう

ここからは「実行計画を見てみよう！」ということで、SQLの実行計画をご説明させていただいて、「実行計画からSQLがどのように実行されるか？」という、具体的な説明をさせていただきます

このなかで、SQLの実行計画を見られたことのある方はいらっしゃいますか？

（会場挙手）

1人。RDBを使っていても、見たことがないという人が多いです。多いですが、実行計画を見ると、SQLとはどんなものなのかがよくわかります。

申し訳ないですが今回、実行計画はOracleで取っています。Oracleのほうがわかりやすいというか細かいので、実行計画はぜんぜん違いますが、内容的には同じと思ってください。

業務屋なので（スライドを指して）こういうかたちになっています。“売上”と“売上日”で、1年ぐらいの日付で取ったものです。

ですが、あえてほとんどヒットしないデータを作っています。

ここで言ってる「予想」を誰がするかというと、もちろんDBエンジンが予想しています。SQLをDBエンジンが受け取ったらまず何をするかというと、「どのインデックスが使えるか？」とDBエンジンは考えます。そして、使えるインデックスをピックアップして、そのインデックスに対して範囲を指定していき、どれぐらいヒットするかを予想するわけです。

予想して「ヒットするデータが少ない」と感じたら、「インデックスを使ったほうがいいよね」ということで、インデックスを使うプログラムを作ります。できあがるプログラムはこんな感じです。

“売上日”というインデックスがあります。それをRangeScan（範囲スキャン）します。するとFrom/Toが入っていたのでFrom/Toを入れて、（“rowIDs”を指して）アドレスの一団が取れました。

そのアドレスの一団をグルッと回って（“add(売上.getRow(rowID))”を指して）実データを取るというので、（“retRows”を指して）元の位置に戻して（“return retRows”を指して）返すという、こうしたプログラムが自動で作られ、実行されます。

こういうデータではあまり起こりませんが、実データよりインデックスの容量の方が大きい場合もあります。

そういうことが起こりえるので、インデックスをあまりたくさん読んでしまうと、非常にたくさんのデータを読むことになります。「それなら実データだけを読んで、読み飛ばしたほうがいいよね」と判断するわけです。つまり、「ヒットするデータが多い」と判断したとき、「インデックスはあるけど、使いませんよ」という判断をして、“TABLE ACCESS(FULL)”と書き換えます。

プログラム的にはどうなるかというと、まずは売上データを回ります。先ほどはインデックスによる絞込が行われていたのですが、ループの内側にif文を置いて絞り込みが処理されます。答えは一緒です。

したがって、ヒットする・ヒットしないにかかわらず、どちらのプログラムを流しても同じ答えが出てきます。ただ、パフォーマンスがぜんぜん違うので、ヒットするときとヒットしないときとで、どちらが良いプログラムかということが変わります。

ヒットするデータが多い、「インデックス使うべき」と判断するかしないかという閾値付近では、ちょっとおかしな挙動になることがままありますが、予想からプログラムを書き換えるという処理が行われている、ということになります。

RDBMSごと、バージョンごとに性格が違う

これがSQLの基本で、出てくるプログラムはRDBMSとバージョンごとにぜんぜん違います。ですが、Oracleはとくに違うんです。Oracleを使う人は1個のSQLでもA4に2ページ、3ページというSQLを書いたりします。

そんなものを、ものすごくたくさんあるバージョンについてチューニングをかけたとしても、Oracleさんが新しく出したパッチを当てたら、突然、遅くなるということもぜんぜん起こりえるんです。

そういったことが起こったときに、バージョンを8.0に落としたりすると、11だと“HASH JOIN”となる実行計画が。

8だと“NESTED LOOPS”となります。性格が違うんですね。

MySQLとPostgreもぜんぜん性格が違いますし、もちろんMySQLとOracleもぜんぜん性格が違う。ある程度実行計画を見ながらテストをやっていると、「この子はこういう性格だな」というのがわかってきます。MySQLはとくにまだ成長期なので、特にバージョンごとに違う実行計画を返してきてくれます。

そのような機能を担当しているのが、RDBMSのなかのオプティマイザーという機能になります。

これはどんなことやっているかというと、まず、シンタックスチェックをやります。シンタックスチェックをやって、DBオブジェクト、テーブルがあるかどうかをチェックします。

そのテーブルには権限が付いていますよね。SELECT権限や、テーブルごとにUPDATE権限がありますから、それがあるかどうかをチェックします。そしてカラムや、ファンクションを使っていたら引数のチェックも当然やります。

その後、パース処理という文法上の整理が入ります。これら全部を合わせたものを「パース」と呼んだりもしますが、ここで本来的なパース処理が入ります。

そこから、先ほど言ったテーブル件数の統計情報や、インデックスの統計情報、データの分散具合を見て、「どのインデックス使おう？」と考えてプログラムを組み、できあがったプログラムをマシン語に直して実行する、ということをやっています。

これは、誰が見ても明らかに重いですよね。更に、実行時には排他制御も自動的にやってくれますから、そこでもまたオーバーヘッドが載っているわけです。そういうかたちでRDBは実行されています。

JOINは重いのか？

続いて、「JOINは重いか？」という話をさせていただきます。JOINはオブジェクト指向と親和性が低いんですよ。JOINをいっぱい入れたら、なんだかわからないオブジェクト指向とはまったく違う異物が入っているものに見えてしまいます。だから、避けようとする方がよくいらっしゃいます。

では、避けたらどうなるのか。JOINしたSQLと、JOINしていないSQL。

（JOINしたSQLを指して）これとこれが“売上”と“顧客”がJOINされているよ。

（JOINしてないSQLを指して）これは“売上”だけを取って、（“顧客”を指して）これがループのなかで呼ばれるよ、というかたちですね。

これはN＋1のかたちになるんですが、実行計画を比べると、主要な操作をしているところは、すべて分けたほうに入ります。

プログラムにすると、JOINしたときのプログラムは、（スライドを指して）こうしたプログラムが作られるわけです。

これはイメージですが、こういったプログラムが作られます。これは余計ですが、このif文が消えるだけで、LEFT JOINと同じになります。

JOINなしの場合

JOINなしのソースのイメージはこうなります。

これがAPサーバから呼び出されるわけですね。そして、ここが繰り返されます。

それとこれを比べます。

この2つを比べて、なにか減っているところがあるかというと、実は減らないんですよ。なに1つ減らない。これをAPサーバに持っていったら、ループのブレイク条件は減ります。でも、ここしか減らない。

ですから、DBサーバの負荷は、これだけを見たら分割しても分割しなくても、負荷はぜんぜん変わりません。

が、これがN回繰り返されるわけです。これが分割した回数だけ繰り返されます。すごい量です。

もっと考えると、分割してAPサーバで処理するということはどういうことかというと……システムのデバイスのなかで一番遅いのは、だいたいハードディスクです。

ですが、ネットワークも十分遅いですよね。メモリとCPUのスピードと比べたら、めちゃくちゃ遅いです。

そして、（右側のサーバを指して）個々のメモリー上に、この筐体の8割のメモリを当ててほしいというぐらい、大量のメモリにバッファしているデータがあります。そのデータを一番遅いネットワークを通じて、（左側のサーバを指して）ここへコピーするわけですね。配列などを作りますが、配列を作るということは、APサーバもメモリにコピーされましたよ、ということです。

「（右側のサーバを指して）ここにすでにデータはあるんだから、ここに存在しているデータで処理しようよ」「バッファに載ったものをインプロセスで処理しようよ」というのが、SQL文です。

だから一括で処理して、処理済みのデータ・必要最小限のデータだけ返します。「ネットワークは一番遅いところですから、ここをループさせたり、ここを通信するものは最小限にしましょう」というのが、基本的な考え方です。

RDBで処理するということ

なぜこうなるか？　RDBが最初に考えられたのは50年ぐらい前の話です。50年前にどんなことをやっていたかというと、ホストコンピュータや汎用機といわれるものの時代です。クライアントの処理能力はゼロでした。

だから、（左側のサーバを指して）ここで処理するなんて考えられなかったので、（右側のサーバに）SQL文を渡します。当時はコンパイル言語が普通でしたが、コンパイルなしで渡すことができます。

これをやらなければ、毎回こうしたプログラムを作ってコンパイルし直さなければインプロセスが動かせないよね、ということになってしまいます。そんなことはやっていられないので、SQL文という規格ができたわけです。

そのSQL文の規格として「繰り返さない」「集合的理論でやったら便利だよ」といったことが残っているので、それがみなさんと合わなくなる最大の原因かもしれませんが、「こういうことをして、せっかくバッファを載せているんだから、このバッファを利用して処理し、必要最小限をこちらへ返そう」と考え直せば、RDBで処理するという印象も変わってくるかもしれません。

だから私は、「SQLを書け」「書け」「書け」と、いろいろなところで指導しているんですが、ここを最大限利用したい、もったいないというのが、私の言っている意味です。

ですから、SQLを分割してAPサーバで肩代わりできる処理は何かというと、ループのブレイク処理の判断、SELECT文で行っている四則演算、あとはソート処理ですね。

このソート処理は、実はSQLでやる必要はないんです。たいていのところはSQLで「“ORDER BY”を必ずつけなさい」というルールになっている（シングルポイントのDBサーバの不可を気にする意味では、逆の指導になっている。）ことが多いのですが、これだけは外出しできます。ループ処理と四則演算の2つはほとんど誤差なので、ぜんぜん関係ありません。「パフォーマンスとは一切関係ない」と言い切っていいと思います。

そのおかげでAPサーバの処理がめちゃめちゃ増えますし、コピーされるためメモリも大量に消費することになるわけです。そして、DBサーバのパワーは減らずに、SQLの解析文が増えます。ネットワークといっても、これはデータセンターのネットワークなのであまり関係ありませんが、SQL文と未処理のデータがバンバン飛ぶため、ネットワークも大量に消費します。

DBサーバのCPU負荷

負担を考えると、こういうかたちになります。

DBサーバのCPUの負荷のことを考えると、CPUの負荷はピュンと上がります。それをバラしてやったらこうなります。APサーバの処理を待つ時間があるので、ピョンピョンピョンと櫛型になるわけです。

「負荷のピークが高いからSQLでやるのは怖い」という人もいますが、それは間違いです。みなさんのゲームのピークというのは、1個の処理のピークを考えても意味がないですよね。1個の処理のピークなど、どうでもいいんですよ。ピークというのは、ユーザーのアクセスが重なったときがピークなので、横方向に大きいほうが重なりやすいんです。

ですから、ピークが高いというと「ピーク」という言葉に反応して「こんなのイヤだ」という人がけっこう出るんですが、「いやいや、面積で考えたら、どう考えても分割したほうが大きいですよ。一括で処理したほうが絶対小さくなるんですよ」ということです。できるかぎり1回で処理したほうが小さくなるということですね。

もっとひどいのは、DBサーバのメモリの負荷です。

何が負荷かというと、DBサーバとクライアントが接続したときに、セッションを作りますよね。そのセッションができた瞬間にワークメモリを確保するんです。確保して、「セッション確立」というふうに返します。

でも、使った瞬間からとにかくセッションごとに同じ量のワークメモリを取っちゃうんです。

（大きすぎるときはあとから修正すればいいのですが）デフォルトでは128Kぐらいバンと取ってしまいます。セッションが切れるまではそれを維持しますから、セッションの処理が遅ければ遅いほどメモリの負荷が高くなります。

いずれにしても、みなさんは本当にCPUのピークばかり気にしているので、ピークだけではなく面積で考えないと意味がないぞ、ということです。

N＋1は論外

ネットワークはCPUと同じなので省略します。

つまり、N＋1は論外なんですよ、これは絶対やってはいけません。

「JOINは重いからループして処理するんです。ORMを使うんです」みたいなことを言っている人もいるんですが、それはまったくの間違いです。APサーバもDBサーバも負荷が高くなって意味がありません。

そして、「JOINは重い」の対策は、非正規化です。あるいはここに書いていますが、APサーバにキャッシュしていたら、キャッシュした分をわざわざもう1回DBサーバから取ってくる必要はありません。APサーバにキャッシュしているものを使えばいいんです。APサーバは複数立つので、その整合性を取るという面倒なことができるのであれば、別にキャッシュしているものを使うのもありです。いずれかしかありません。

非正規化することになった場合、この時点で私から見れば、「それ、COBOLの設計やな」というふうになるんですけどね（笑）。実はCOBOLの設計なんですね。COBOLは昔は「ここが遅いから」ということで、どんどん非正規化してやっていました。

いずれにしても、「JOINは遅い！」「JOINは重い！」と言われたら、「ループしてやります」「いや、それは意味がないよ」ということで、この言葉に反応しないようにしてほしいですね。