kagamihogeの日記

kagamihogeの日記です。

集計関数がメモリで処理しきれなくなったとき起きたこと

一般的にOracleなりRDBMSのパフォーマンスは、メモリでさばける量を超えると突然悪化すると言われる。その理由は、メモリで処理しきれていたものが、ディスクIOを必要とするようになるから、ということになる。よって、実際のところどの程度悪化するものなのか、を見てみる。また、コレが原因で痛い目を見たケースについても見ていく。

準備

テスト用のテーブルを用意する。

drop table hoge purge;
create table hoge 
(
  hoge_id integer not null,
  hoge_value varchar2(16),
  constraint hoge_pk primary key (hoge_id)
);

100万件ずつテストデータを挿入していくときに下記を使用する。下記で+ 1000000となっている箇所を、100万ずつプラスしながら使用する。

insert into hoge
select 
  rownum + 1000000,
  dbms_random.string('X', 16)
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 1000);
commit;

速度計測対象のクエリ。あまり意味の無いクエリなんだけど、重要なのは全件検索するクエリだ、ということ。この意味については後述。

select count(*) from hoge where hoge_value <> '1';

速度計測

100万件ずつテストデータを増やして、その都度速度計測対象のクエリを実行する。なお、バッファキャッシュをきかせた状態でテストするので、最初に1,2回実行して、それから後での実行時間を計測する。それを3回やって、平均を取ったのが下記。

百万件 1 2 3 AVG
1000 1.235 1.250 1.234 1.240
1100 1.391 1.359 1.328 1.359
1200 1.516 1.453 1.453 1.474
1300 1.609 1.594 1.578 1.594
1400 1.703 1.718 1.734 1.718
1500 8.625 8.515 8.610 8.583
1600 9.204 9.266 9.313 9.261


グラフにすると一目瞭然で1500から1600で急激に実行時間が跳ね上げる。それ以前までも、データ量の増大に比例して僅かずつながら実行時間が増えている。しかし、データ量の増え方は同じでありながら、実行時間の劣化は7〜8倍となっている。もちろん、この数値そのものにそれほどの意味は無い。この環境ではこうなった、というだけの話なので、個別の数値については参考値にしかならない。

重要なのは、全件検索をする集計関数がバッファキャッシュにおさまりきらないデータ量となったとき、突如パフォーマンスの劣化となりうる、という点にある。DBのデータ量が増大するとそれまで安定していたものがいきなり遅くなる、という事例はちょくちょく耳にする。そうなるケースは色々考えられるのだが、その1ケースをごくごく単純して示すのがこのエントリでやりたかったことである。

↑のような状況を経験した失敗事例

じゃあ具体的にどういうSQLを含むアプリケーションのコードだとそうなるのか? っていう話になる。これはまぁいくらでもパターンは考えられるので、俺が実際に遭遇したケースについて書くことにする。

いわゆるマスタ更新画面的なものを考える。検索条件を入力して、合致したものを画面表示して、更新入力をしてもらう、的なイメージ。検索で合致したものを出すとき、まずcountで件数を出す。それで、50件とかテキトーなマックス値以上になりそうなら、再検索を促す、的になっているものとする。なお、この実装の是非については一先ずスルーする。

擬似コードだとこんな感じ。

if select count() 〜 where 検索条件 > 50
   再検索してください
   return

select col1, col2 〜 where 検索条件
for
   画面表示
end

で、俺が遭遇したのはこの機能がある日突然スローダウンするっていうもの。「検索条件」の列がインデックスを付けるほどには分布がビミョーな列だったので、countは全件検索になっていた。よって、このエントリの前半部分で見たように、ある瞬間から突然性能劣化することになった。それと、検索条件を変える(=他の列で検索する)場合には問題無く動くので、見た目上は問題発生したりしなかったりしたのが印象に残っている。

対策

レコードの存在チェックは「rownum <= 1」を使用する

指針:レコードの存在チェックは「rownum <= 1」をWHERE句に使用して実行してください。
理由:条件に一致する行を1行見つけた時点でSQLを終了するため、高速に実行することができます。
(中略)表の件数を確認する場合にNULL値の考慮を行うルルールなどについても、SQLコーディングルールに記載しておく必要があります。

SELECT /* TARGET_FUNC00N_000M */
       COUNT(1)
  FROM (SELECT empno
          FROM emp
         WHERE salary > :b1
           AND rownum <= 1);

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニングSQLチューニング (DB SELECTION) 7章定型的なSQLチューニング レコードの存在チェックは「rownum <= 1」を使用する p.43-44 より抜粋

とまぁ、rownumで切ってしまえばよい。

存在チェック無くして必要なだけフェッチする

単なるマスタ更新画面とかの場合、別に存在チェック不要だったりする。もちろん、仕様次第というか、どういう使い方・画面操作をしたいかにもよるのだけども。ページングめんどかったり、実用上ソレで問題なかったりする場合は50件とかテキトーな件数だけどーんとフェッチして表示しちゃう実装でも良いんじゃないっすかね。50件で目的のデータが出てこない場合は、検索条件見直してもっかい絞込み検索していただく感じ。要するに、SQL Developerの問い合わせ結果表示のやり方と同じって話。

参考文献

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニングSQLチューニング (DB SELECTION)

パフォーマンス改善と事前対策に役立つ Oracle SQLチューニングSQLチューニング (DB SELECTION)