データベースを遅くするための８つの方法
はじめに
Twitterのタイムラインを見ていたらバッチ系のプログラムで逐次コミットをやめて一括コミットにしたら爆速になったというのを見ました。当たり前でしょ、と思ったけど確かに知らなければ分からないよね、と思って主に初心者向けにRDBを扱うときの注意点をまとめてみました。
プログラミングテクニック的なところからテーブル設計くらいの範疇でDBチューニングとかは入ってないです。
自分の経験的にOracleをベースに書いていますが、他のRDBでも特に変わらないレベルの粒度だと思います。
大量の逐次コミットをする
バッチアプリケーションでDBにデータをインサートすると言うのはかなり一般的な処理です。しかしデータ量が少ない時はともかく大量のインサートを逐次コミットで処理するとめちゃくちゃ遅くなります。数倍から十数倍遅くなることもあるので、10分程度のバッチが１時間越えに化けることもザラにあるので原則するべきではありません。通常は1000件とか１万件あたりを目安にコミットで良いと思いますが、この辺はマシンやDB、データ状態に強く依存するので適切に測りましょう。
何故遅くなるのか？
これはいろんな理由があると思いますが端的にいえばRDBは書き込みの度に最適化処理をしているので、それを逐次コミットで都度都度やると非効率、と言う話です。
これは書き込み自体の最適化と読み込みのための最適化があります。
書き込みのタイミングでWALとかREDOログとか呼ばれるトランザクションログを書き込みます。この作業を一括してやるか逐次やるかるかでまず大きく性能に影響がでます。一般論として逐次処理よりバッチ処理の方が高速なのはイメージしやすいと思います。
ETLとかでデータを大量にロードするときはOracleなんかだとダイレクト・パス・インサートを使ってログを出さずに書き込みをして高速化をするテクニックもあるので検討しましょう。ETLツールや各DBのロードコマンドもそういった動きをするケースがあります。
また、同じく影響が大きいのがINDEXの再構築コストです。
INDEXは読み込みを高速に行うための仕組みです。逐次インサートではこれを毎回構築し直す必要があるのでその分余計なコストがかかってしまいます。これも逐次コミットではなくバルクインサートなどにすることで負荷を緩和することができます。
あと同じくログ関連でいうとDELETEも大量にするとREDOログが大量に発生して死ぬので大量削除にはTruncateを検討しましょう。ただし、ロールバック出来ないしテーブル構造にも依存するので業務設計的にNGなことも多いけど。。。
バッチを分割して疑似的な並列インサートをする
取り扱うデータ量が日々増えていきある日バッチが突き抜けてしまう。こういったことは良くありますよね。その解決策として思いつくことの一つにデータを分割して同じ処理をするバッチの数を増やすことです。
こでは特に参照系の処理やファイル書き込みでは上手くいきますがRDBにインサートや更新をするケースだと効果が薄すかったりDB全体を遅くしてしまう事があります。
そのため、データを例えばファイルに一度書き込んで最終的に パラレルDMLなどでDBの機能を使って並列インサートをすることで高速化されることが良くあります。
何故遅くなるのか？
まず大前提として現在のトラディショナルなRDBMSは書き込みよりも参照性能を重視しています。
これは作られた当時の時代背景もありますし、現代的にも多くのシステムは書き込みよりも参照が重視される傾向にはありますよね。そのためインデックスをはじめとしたREADのための最適化等をWRITEのタイミングでしてるのですが、これが基本的に並列処理に弱く外部から大量に書き込みがあるとリソースが競合しまくって性能が出ないばかりかリソースを枯渇させる危険性があります。CPU1個の時代に原理が作られてるからまあ仕方ない。
主な解決策は以下の2つです。
- シャーディングやパーティションを使ってリソースの管理単位を小さくする
- パラレルDMLなどRDB側の機能を使ってRDBに最適化をさせる
シャーディングやパーティションを使う事で例えばディスクやメモリの書き込みブロックやインデックスなどをキー毎に散らす事で競合をさけます。運用とのコストバランスがありますがパーティションは比較的リスクが少ないですし、シャーディングもCloud Spannerではデフォルトですし、OracleのOracle Shardingなど使い勝手の良いものも登場してきています。SNSや会員系のシステムであればユーザをキーにしておけば基本的には問題も発生しづらいと思うので妥当な選択肢です。
あと、元データをPKとかでソートしておかないと同じタイミングで同じようなデータを書き込むリスクが増えるのでその点も注意しましょう。
もう一つがパラレルDML等を使ってＤＢ側に最適化をさせることです。RDBMSは高度に抽象化されたシステムなので外部から細かいことがわかりませんが、当然DB自身は詳細にしっています。そのためマルチコアのマシンなどであればパラレルDMLで高速に読み込めるケースは多いかともいます。リアルタイムトランザクションには使えない解決策ですし、シャーディングと比べると限度もありますがアプリ側だけで対応できる方法なのでまずは検討するのが良いと思います。
全てのカラムにINDEXを付ける
RDBではWHERE句によるDBアクセスを高速化するためにインデックスを使用します。なので最初は無邪気にINDEXを大量につけたくなるのですが、これは原則避けるべきです。必要最小限のINDEXを付けましょう。
特にWHERE句で複数のキーを指定する場合は２つ目以降のインデックスが本当に必要かは考えた方がいいと思います。個人的な感覚だと１つ目のキーで1,000件以下に絞られていればよほど低レイテンシーを意識する時以外は十分な気がします。この辺はアプリ特性によるのでバランスを見て考えると良いかと。
何故遅くなるのか？
これは単純にインサート時にINDEXの再構築がたくさん走り、書き込み性能の劣化を招くからです。INDEXがある方が一般的に読み込みは速いですが、無い方が書き込みは速いと言うトレードオフの関係にあるので、そこを意識してテーブル設計をしましょう。
アプリケーション的に普段はインサート性能がそこまで重要じゃなくてもデータマイグレーションを将来する時とかに死ぬ可能性が大いにあるので、いらないなら付けない越したことがないです。
シーケンスナンバーをPKにする
より正確には「シーケンスナンバーにインデックスを適用する」なのですが、最も一般的なシーケンスナンバーの使い所はPKに使う場合でしょう。
MySQLだとAUTO_INCREMENTもあるし、入門書などでも良くシーケンスをキーにしてるので推奨すらされてる感じですが、これは現代ではあまりお勧めできません。UUIDなどユニークでランダムなキーかサロゲートキーではなくナチュラルキーを使うと良いでしょう。まあ、個人的にはサロゲートキーのが好きだけどそこはシステムによるし。
何故遅くなるのか？
いくつか理由があります。
- シーケンス生成のボトルネック
- 並列処理の場合に同期処理が必要になる
- Right Growing Index
まず、シーケンスの生成にはコストがかかります。そのためバッファ/キャッシュを上回る速度で書き込んだ場合にはこれがボトルネックになる可能性があります。
解決方法としてはキャッシュサイズを増やすのが鉄板なのでこれで解決できるケースが多いです。
ただし、Oracle RACのようにDBインスタンスが複数ある場合はそれぞれのインスタンスでキャッシュを持つことになるので 「シーケンス値が書き込み順」になるわけではありません。
アプリケーション設計でシーケンス番号が書き込み順になってる事を期待するロジックは稀に存在します。これは現在では完全に非推奨ですが昔はまあ許容範囲の設計だったと思うので出会う事はあります。その場合はキャッシュを0にしたりしてシーケンスを複数のインスタンスで同期させる必要があります。これだと並列インスタンスのメリットを全然活かせず性能が劣化してしまいます。
Oracle RAC以外でもAWS AuroraやCloud Spannerのように分散DBでマルチ書き込みが出来るものは同じ事象が発生するかと思います。
もう一つ重要なボトルネックとしてRight Growing Index、つまりインデックスリーフブロックの競合です。
シーケンス番号のような単調な値をインデックスにすると連続する書き込みで同じB-Treeの同じリーフに更新が集中してしまいロックがかかります。これが性能のボトルネックになるわけです。
引用:実はシンプル！RACチューニングの考え方
こちらはOracleの話ですが原理的には他のデータベースでも発生するはずです。
このような書き込みの競合を減らすためには「シーケンスのキャッシュを増やして値を散らす」「ハッシュパーティショニングでインデックスを分散させる」「シャーディングでインデックスを分散させる」「そもそもシーケンスやタイムスタンプ等単調増加する値をインデックスに使わない」あたりがあります。
個人的にはシーケンス番号をPKにしてPKが書き込み順を保証する値だと誤った設計をしないためにも最初からUUIDなどをPKに使うのがベストだと考えています。
カーディナリティが低いカラムにインデックスを指定する
カーディナリティとはカラムに含まれるデータの分散具合です。性別なら「男・女」の２個(#1)ですし、誕生日なら365個です。
*1 最近はLGBTを考慮してもう少しバリエーションがあるケースもありますが、今回は話をシンプルにするために男女とします。
数が多い方を「カーディナリティが高い」、少ない方を「カーディナリティが低い」と呼びます。基本的にインデックスはカーディナリティが高いカラムに指定しないと遅くなると言うか効果がありません。インデックス作成のデメリットだけを得ることになるのでカーディナリイティが低いカラムにインデックスを指定するべきではありません。
何故遅くなるのか？
これは単純でカーディナリティが低いとデータが絞れないからです。
例えば、10,000人のユーザデータを考えるとします。この場合、男女比が均一だと仮定すると 「性別カラム」では5,000人までしか数を絞れません。一方で、誕生日もこれまた均一だと仮定すると、約27人まで絞れるわけです。これはどちらがインデックスの効果が大きいかは一目瞭然ですよね？
なので、カーディナリティが高いカラムのみにインデックスは指定するべきですし、Where句への指定もカーディナリティが高い順から書いた方が効果の大きいDBが多いです。
RDBの性能テストで実際の本番のデータと同質のデータをそろえないと適切にテストが出来ないのも同様の理由うです。
Update文を多用する
例えば売上げの集計をするサマリテーブルを考えてみます。ものすごく素朴に作ると売上テーブルに随時インサートして、同時にサマリテーブルに売上金額を加算することで実現できます。
しかし、この設計は原則してはいけません。サマリーテーブルは作らずに必要に応じてSQLで売上テーブルを集計するか、性能が重要な場合は必要に応じてMaterialized view などを作ります。
何故遅くなるのか？
上記の設計はそもそも正規化の観点でダメだろって話もあるのですが、あくまで性能の観点から考えます。
その場合の問題点はUpdate文と言うのは 「原理的にロック競合が起こりやすい」 ことです。売り上げのように複数のトランザクションが同時に発生するケースではインサートであればある程度並列性が保てます。しかし、Update文は同じレコードを更新するという性質上、どうしてもロックをとり同期処理をする必要があるので並列度を上げて性能を上げるというチューニングが取れなくなるためアンチパターンになります。
Materialized viewでは非同期になるし、SQLの集計ではリアルタイムに計算するにはデータ量が多すぎてパフォーマンスが持たないという場合には、例えば「毎分サマリを作成し、直近の１分間だけリアルタイムに計算してその合計値を返す」と行った処理で改善できるケースもあります。
何にしてもUpdate文はマスターテーブルやユーザ属性などどうしても必要でかつ更新頻度の低いものに限定するのが良いでしょう。
大量にカラムがあるテーブルを作る
正規化されていない大量のカラムをもった神テーブルが存在していることがあります。
これはJOINコストを避けるための設計だったりして当時の苦肉の策であることも多いのですがやはり問題です。
定期的に作成/更新される参照専用のテーブルならまだ良いのですがこういった巨大なテーブルに書き込みをするような設計だと大きなボトルネックになります。
何故遅くなるのか？
問題点としてI/Oコストとロック競合があります。
まず、MySQLやOracleといったOLTP向けのRDBは通常は行指向のストレージになっています。そのためデータの取得は行単位です。
この場合、たとえ実際に必要なのは特定のカラムだけでSQLでカラムを指定したとしてもDB側では大量のカラムを全て読み込む必要があります。OLAP向けに作られているDHWなどであれば列指向のストレージを採用してるケースが多いですが、そうではない通常のトランザクション向けDBではこれがペナルティになります。
I/Oリソースを多く消費すると言うことは単純に対象のクエリだけではなくシステム全体のリーソース枯渇に繋がるので性能問題が出やすいです。
もう一つはロックの競合です。
このような大量のカラムをもったテーブルはInsertではなくUpdateされることが多いでしょう。そのため前述したUpdate文による競合が起きやすいのです。
例えばこのような作りになる典型がユーザテーブルです。ユーザのさまざまな属性を正規化せずに１つのテーブルに書いている場合、複数のバッチやリアルタイムトランザクションが同時に走った場合に思わぬ競合が発生しボトルネックになりがちです。
このような状況を避けるためには基本的には正規化を適切に行うのが性能観点でもやはり重要と言うことになります。
クライアントサイドJOIN, クライアントサイドでフィルタ
RDBを使う場合の大きなアンチパターンとしてJOINやフィルタをクライアントサイドで行う、と言うのがあります。
限定した状況で効果がある時が無いわけではないのですが、かなりのレアケースなので原則これらの処理はRDBにてサーバサイドで実施するようにしてください。
何故遅くなるのか？
これはなんといってもI/Oコストです。
一時期「高価なDBに仕事を極力させるべきではない」という風潮が一瞬だけあった気がするのですが、「I/Oリソースを大量に消費する」のでRDBへの負荷もむしろ大きくなります。
ほとんどのケースでRDBとアプリケーションは以下のような関係になっているでしょう。
App -> NW -> DB
アプリケーションで処理をしようとするとNWを経由するのでメモリやディスクに比べてI/Oが大幅に落ちます。IBとか使ってると単純なレイテンシや帯域は理論的には良いですが、実際はシステムを跨ぐのでシリアライズをしていたりアプリ/ミドルウェアの部分で多くのオーバーヘッドが入ります。
結果として何が起こるかというと 「I/Oリソースをがめてる時間が長くなる」 と言う現象が発生します。I/Oは貴重なリソースですからこれを長く占有されると対象のクエリ以外にも待機がかかって遅延してしまいます。集計関数や場合によってはストアドをDBで実行した方がDBの負荷が下がるのも同様の理由です。CPUは相対的に余ってるリソースだしメモリやキャッシュは広帯域なので効率的に処理できるため占有時間が短いためです。
なので1GBのデータをDBで1KBにして転送したほうがNWコストが小さくなるので、JOIN/WHERE句をDBでした方がパフォーマンスが良くなるのはイメージがしやすいですね？
ただし、マスターテーブルとの結合などWHERE句に関係なかったり更新頻度が十分に少ないものは、一度アプリ側でマスターテーブルを読み込んで、RDBから取得した値と最終的に結合した方が全体のコストが小さくなるケースも確かにあります。最初から考えることでは無いですが、他の手段で性能問題が解決できない場合には一考の価値はあると思います。
ソートや表記の加工のような処理はロジック的に問題が無いのであればアプリ側オフロードしてもかまいません。I/O消費に影響がないので。ただし、ソートをアプリ側でやった場合はページングなどと組み合わせてると意図した挙動にならないと思うので注意してください。
ちなみにこういったデータのローカリティに注目して作られてるのがRDBのストアドプロシージャ、Oracleのスマートスキャン、そしてHadoopのような分散処理基盤です。それぞれ解決したいことは違いますがデータに近いところで前処理をすることで効率を上げるという発想は同様です。
一部のKVSではクライアントサイドJOINが必要になるケースもあるのですが、RDBでは原則的にはRDBに仕事をさせたほうがRDBの負荷自体が減ると言うことは覚えておいてもいいでしょう。
まとめ
RDBを利用する上での性能観点での典型的な罠をまとめてみました。
RDBと言ってもチューニングポイントはプロダクト毎に違いますし、もっというとKVSやNewSQLでは考え方が根本的に異なる部分もあります。
なので、上記観点がなんにでも使えるわけではないのですが 「遅くなる理由」 をきちんと押さえておけば、ほかにも応用が出来る気がします。
そもそも RDBは書き込み性能、特に並列時の書き込み性能が極めて弱い ですがそれは多くのKVSやNewSQLのようにシャーディングが前提になってないからです。逆にいえばこれらはRDBの弱点を克服するためにそのような作りなのです。こういったことを知っていれば適切なアーキテクチャを選ぶこともできるようになります。
RDB/SQLの性能チューニングは奥深いのでここに書いてること以外も星の数ほどあると思いますが、とりあえずこの辺を知ってると即死は免れる気がします。
それではHappy Hacking!
Discussion