graphics.hatenablog.com

技術系テクニカルアーティストのあれこれ

テクニカルアーティストのためのデータベース入門 (9) パフォーマンス最適化

最初のリリースまではデバッグ大変だからちっちゃいデータセットで試しながら進めてて、とあるタイミングで本番規模のデータを流し込んだら最初のロードに数秒とかかかっちゃったりして。そういうときに、可能な限り悩まずに状況を改善したい。

graphics.hatenablog.com

共通

サーバを札束でひっぱたく

だってほら、どうせインハウス用のサーバなんてガチ業務用のいいやつなんて滅多に使わない*1じゃないですか。僕らの人件費と比べれば、仮想ホストのコア割り当てを増やすとか、メモリ買ってきて追加で挿すとか、ちょっと奮発してHDDをSDDに入れ替えるとか、そっちのほうが断然安いケースって結構あるんですね。

それで済むならプログラムを書き換える必要はないし、テーブル構成を変える必要もないし、ソフトウェア側のメンテナンスコストが一切増えない。*2

大人の力ってすばらしい。

select

認知とか心理の話。

大抵のユーザって、「データの保存」には多少時間かかっても待ってくれるんですね。ふつうにUIつくれば「あ、いまデータ更新してるんだよね?」って、ユーザ側はわかるし、データを保存するってことは作業が一段落してるってことだから、別に待ち時間が原因で集中力が途切れて云々とか*3*4なりにくい。何より、そのデータを保存が必要な状態にした(=編集した)のは自分だから、多少の待ち時間にはむしろ納得感さえある。*5

一方で、「データの取得」は厳密にはツールやワークフロー、チームなんかの都合であって、作業者個人からみたら「保存」ほどの納得感は案外なかったりするかもしれない。それに、データの取得は必ずしも作業の節目で行われるわけじゃないから、作業者の集中を妨害しやすい。

そういうわけで、まず select を最適化すべき、というのが自分の基本的な考え方。update や delete は可能な限りビルドマシンに逃がすっていうのも、とてもアリだと思う。

クエリの並列化

DBをやるなら並列化をおぼえよう。

たとえばこのコードは、

using (var db = new Connection()) {
  var item1 = db.ExecuteQuery("select * from `table1` where `name` = 'filter1'");
  var item2 = db.ExecuteQuery("select * from `table2` where `name` = 'filter2'");
  var item3 = db.ExecuteQuery("select * from `table3` where `name` = 'filter3'");
}

こうするとだいたい2倍ぐらいに速くなる。

using (var db = new Connection()) {
  Task.WaitAll(
    Task.Factory.StartNew(() => item1 = db.ExecuteQuery("select * from `table1` where `name` = 'filter1'")),
    Task.Factory.StartNew(() => item2 = db.ExecuteQuery("select * from `table2` where `name` = 'filter2'")),
    Task.Factory.StartNew(() => item3 = db.ExecuteQuery("select * from `table3` where `name` = 'filter3'")),
  );
}

たとえばこのコードは、

using (var db = new Connection()) {
  foreach (var user in users) {
    var item = db.ExecuteQuery(#"select * from `table1` where `user` = '{user}'");
    ... // item を使ったややこしい処理
  }
}

こうするとすごく速くなることが多い。

using (var db = new Connection()) {
  Parallel.ForEach(users, user => {
    var item = db.ExecuteQuery(#"select * from `table1` where `user` = '{user}'");
    ... // item を使ったややこしい処理
  });
}

「データの取得」に関してだけいえば、ソースコードの中身をほとんど変えずに Task とか Parallel とか書くだけでものすごく速くことが結構ある。この上なく手軽な上にアタマもあまり使わない。

基本的にすべてのDBアクセスはネットワーク処理を介して行われる。つまり、ExecuteQuery() の処理時間の大半が通信待ちになる。その間に何もしないのはとてももったいない。データ競合の可能性がない select 処理は、大抵の場合並列化と相性がいい。

クエリの数を減らす

さっきのこのコードは、

using (var db = new Connection()) {
  foreach (var user in users) {
    var item = db.ExecuteQuery(#"select * from `table1` where `user` = '{user}'");
    ... // item を使ったややこしい処理
  }
}

こうしてもいい。

using (var db = new Connection()) {
  items = db.ExecuteQuery(#"select * from `table1` where `user` in ({string.Join(',', users)})");
}
Parallel.ForEach(users, user => {
  var itemIndex = Array.IndexOf(users, user);// usersがソート済み配列ならもっと速い検索が使えるかも
  var item = items[itemIndex];
  ... // item を使ったややこしい処理
});

それか、こんなのでもいい。

using (var db = new Connection()) {
  items = db.ExecuteQuery("select * from `table1`")
    .ToDictionary(item => (string)item["name"], item => item);
}
Parallel.ForEach(users, user => {
  var item = items[user];
  ... // item を使ったややこしい処理
});

ToDictionary() にも多少のコストはかかるから、itemsの個数によってはForEachの中で items.FirstOrDefault(x => (string)x["name"] == user)] したほうがいいかもしれない。

CPU にかかる負荷が増えてるじゃないかって話はあるんだけど、そのあたりはクエリの結果をバラすためにローカル CPU 側で消費する計算コストと、ばらばらにクエリを打つときに DB サーバの CPU 側で消費する計算コスト、DB サーバとの通信コストとの兼ね合いになる。規模や複雑さにもよるけど、通信するよりは CPU を余計にまわすほうが速いことも多い。*6

あとクエリの数を減らすのもそうだけど、「DBとの接続期間」はできるだけ短くしておくほうがなにかと都合がよかったりする。そういう意味でも、可能な限りクエリをまとめて実行できるようにしておく。

キャッシュに載せる

memcached でも Redis でも、なんでもいいから適当な KVS に載せる。アプリ側でオンメモリにキャッシュする。などなど。

キャッシュの自動クリア*7期間に気を遣う必要はあるけど、DBサーバにかかる負荷*8をゼロにできる。キーの設定なんかも、最初は適当で構わない。あとでまた悩めばいい。

var query = "select * from `models` where `user` = 'user1'";
var item = Cache.Get(key: query);
if (item == null) {
  using (var db = new Connection()) {
    item = db.ExecuteQuery(query);
    Cache.SetAsync(key: query, value: item);
  }
}
var filter = "`user` = 'user1'";
var query = "select * from `models` where " + filter;
var item = Cache.Get(key: filter);
if (item == null) {
  using (var db = new Connection()) {
    item = db.ExecuteQuery(query);
    Cache.SetAsync(key: filter, value: item);
  }
}

さて、キャッシュサーバを使うときはそことの通信に多少のコストがかかる。キャッシュの取得は結果を確実に受け取る必要があるから通信結果をちゃんと待たなきゃいけないけど、キャッシュの設定は別に待つ必要はない。通信コストってやっぱりいろんなとこでネックになりやすいので、それなりには気を遣っておく。

joinしない

たとえばこんな2つのテーブルがあるとする。

models

id name file_id
1 model1 13
2 model2 14
2 model3 13

files

id path
13 model13.fbx
14 model14.fbx

ここからデータを取り出すクエリは、だいたいこうなる。

select `m`.`name`, `f`.`path`
from `models` as `m`
inner join `files` as `f` on `f`.`id` = `m`.`file_id`


でもこれ、どう考えてもこうしたほうが速い。
models

id name filepath
1 model1 model13.fbx
2 model2 model14.fbx
2 model3 model13.fbx
select `name`, `filepath` from `models`

あからさまにデータが重複してるし、嫌がる人は結構いると思う。でも速い。

速さをとるか、綺麗さをとるか。個人的には、すべての選択肢を検討できるようにしておきたい。

インデックスを貼る

ものすごくざっくりいうと、よく使うデータにポストイットを貼っとくようなイメージ。インデクスを貼っておくと検索が速くなる。特に join するときのキーに使うやつ(↑の例だと `models`.`file_id`)には、これがよく効く。*9

f:id:hal1932:20161022010808j:plain

それ以外

最初に書いたとおりユーザに悪影響を与えるのはだいたい select の遅さだし、更新系はめんどくさい話も多いからあまり書くことがない。。

トランザクション

同じデータを複数人が同時に編集すると競合してデータが壊れるから、それを防ぐために、DB のシステムは編集中のデータにロックを書ける。つまり、誰かが編集中のデータを他人が同時に編集することはできない。この「編集を始めてから終わるまで」の期間に行われる一連の編集処理のことをトランザクションと呼ぶ。

つまり、DB 上のデータを編集しようとすると、こんなことが起こる。

  1. トランザクションを開始する(他人が編集できないようにロックをかける)
  2. データを編集する
  3. トランザクションの内容を実データに反映する(他人が編集できるようにロックを外す)

最後の反映処理が、地味に重い。*10

データの反映処理が遅いなら、反映する回数を少なくすれば速くなる。なので大抵の DB システムでは、明示的にトランザクションの期間を指定できる仕組みがある。そうやって指定した期間にはデータの反映が行われない。

というわけで、このコードは、

foreach (var model in models) {
  db.ExecuteNonQuery(#"insert into `models` values(`name`, `operator`) ('{model.Name}', '{model.Operator}')");
}

こうする。

db.ExecuteNonQuery("begin transaction");
foreach (var model in models) {
  db.ExecuteNonQuery(#"insert into `models` values(`name`, `operator`) ('{model.Name}', '{model.Operator}')");
}
db.ExecuteNonQuery("commit");

ただしこれには落とし穴があって、トランザクションを使うってことは、その指定した期間にはロックがかかるので、他人が編集することはできない。つまり、トランザクションの実行中は他人が編集待ちをしてしまう。あちこちで編集待ちが起きると結局速度は遅くなる。

しかもトランザクションは大量にメモリを食うから、やりすぎるとサーバのメモリが尽きてめんどくさいことになる。だから、トランザクションの期間は基本的に短いほうがいい。大量の編集クエリを実行したいときは、適当な単位でトランザクションを分割してあげる。

バルクインサート

こういうコードを書きたくなったときは、

foreach (var model in models) {
  db.ExecuteNonQuery(#"insert into `models` values(`name`, `operator`) ('{model.Name}', '{model.Operator}')");
}

こうすると、DB サーバとの通信が 1 回で済んで速い。

var data = new List<string>();
foreach (var model in models) {
  data.Add(#"('{model.Name}', '{model.Operator}')");
}
db.ExecuteNonQuery(#"insert into `models` values(`name`, `operator`) #{string.Join(',', data)}");

*1:「使わせてもらえない」とも言う。

*2:ハードウェアの管理が煩雑になったり、上司やシステム部門にお願いするのが面倒だったり、そういうのはあるかもだけど。

*3:人間の集中力なんてものは、その人の意識を3秒も奪えば十分に途切れさせることができる。

*4:ちなみに現代人の(最大瞬間風速的な意味での)集中持続時間は8秒くらいが限界らしい。

*5:もちろん状況にもよる。まぁそれでもせいぜい3秒くらいの待ち時間にしたいところ。

*6:ただそれはそれとして、通信待ちの間はローカル CPU を他の処理のために使えるので、そういう意図があるときはあえて通信処理を増やすのもアリ。でもそれ系の制御は大抵面倒なのであまりおすすめしない。

*7:詳しくは「database cache expire」とかでぐぐる

*8:特にユーザ数が多いときは、サーバへの負荷がダイレクトに速さに効いてくる。

*9:めんどくさい話はいろいろあるんだけど、とりあえず最初のうちは、「joinのキーにはインデックスを貼る」と丸暗記してしまってもたいした弊害はない、と思う。

*10:DBって結局のところ「データをファイルに保存したり、保存したデータを読み込んだりする」のを効率化するための仕組みなので、最悪のケースでは、データが更新されるたびにファイル書き込みが行われることになる。ファイル書き込みはだいたいいつも遅い。だからこそ、DBサーバにSSDを積むと速くなる。