はじめに
トビウオさんの「ギブミーパフォーマンス！〜DBスキーマを変更して高速化した話〜」だが、見れば見るほどRDBMSの性能ネタの宝庫である。 ちょうど夏休み期間でもあるし、自由研究的に、数回にわたって性能改善してみようと思う。
ちゃぶ台返し
のっけからちゃぶ台を返すようだが、これ、実はRDBMSには向いていないと思うのだ。というのも:
- 各デバイスが「1 ms毎のデータ」を吐くと86,400,000件/日、そのデバイスが1,000台で86.4億件/日と膨大
- RDBMSが得意とする結合などはなく、SQLの言語処理系くらいにしか使っていない
- おそらくは、厳密なトランザクション管理など必要ない
…なんてことをいうと面白くないので、ここはあくまでRDBMSでどこまでいけるかを試してみよう。
環境
トビウオさんが何を使ったかは知らないが、私はPostgreSQL 11.5を使うことにした。 私が使い慣れており、ネタにしたい機能(BRINなど)もいくつかある。そしてチューニングに便利なpg_hint_planを持っているからだ。
マシンは以下の通り:
- OS: Windows 10 (1903)
- CPU: Core i7-7700K
- メモリー: 32 GiB
- ドライブ: ハードディスク(SSDに非ず; 容量が全然足りないので)
準備
実行環境の準備
pg_hint_planを利用する関係上、PostgreSQLはソースから自前コンパイルした。
とはいえ、CygwinのGCC (MinGW)を入れて、configure + makeしただけなのだが。
特別なのは以下の2点だけである:
./cofigure --without-zlibした(pg_dump/pg_restoreするつもりはないので)- pg_hint_planには、下記パッチ(PostgreSQL 11.3以降で必要)を当てた:
--- core.c 2019-01-17 18:32:19.000000000 +0900
+++ core.c 2019-08-13 13:03:02.328066500 +0900
@@ -942,15 +942,6 @@
/*
- * is_dummy_rel --- has relation been proven empty?
- */
-static bool
-is_dummy_rel(RelOptInfo *rel)
-{
- return IS_DUMMY_REL(rel);
-}
-
-/*
* Mark a relation as proven empty.
*
* During GEQO planning, this can get invoked more than once on the same
データの準備
テーブル定義は、スライド4ページ目にあるような構造に従った。ただし:
- カラム名は多少いじった:
DATAなどのキーワードを使うと、クエリーを書くのが面倒だから - 時刻は
BIGINTとした: SQL標準のNUMERICが好ましいが、10進演算はなくてもよいから - 観測値は
REALとした: 同上。DOUBLE PRECISIONでないのは、データ量を減らしたいのと、センサー精度的に6-7桁で十分だろうから
データは、ディスク容量の都合で1,080,000,100件とした:
- デバイス数は100台(
'DEV00000'-'DEV00099') - 各デバイスが持つデータは、3時間分(2019-08-01 00:00:00から03:00:00で1 ms間隔)
- 観測値は乱数で生成
なお、要件的に(おおむね)観測時刻順にINSERTされると見て間違いないだろう。
あわせて、スライド6ページ目にある「インデックスを張っていたname列」に相当する索引も作っておこう。
すべて含めてこんな感じでデータが作れる:
/*+ NestLoop(t d) Leading(t d) */
CREATE TABLE tbuw_normal (
time/*BIGINT*/,
device_id/*CHAR(8)*/,
measure/*REAL*/
) AS
SELECT t.t, d.d, CAST(RANDOM()*1000 AS REAL)
FROM (
SELECT generate_series AS t
FROM generate_series(
CAST(DATE '2019-08-01' - DATE 'EPOCH' AS BIGINT)*24*60*60*1000,
CAST(DATE '2019-08-01' - DATE 'EPOCH' AS BIGINT)*24*60*60*1000 + 3*60*60*1000,
1)) AS t
CROSS JOIN (
SELECT 'DEV'||TO_CHAR(generate_series, 'FM00000') AS d
FROM generate_series(0, 99, 1)) AS d
-- ORDER BY t.t
-- ↑時系列データなので、概ねORDER BY t.t的な順序で挿入されるはず。
-- 今回は実行計画がt→dのnested loopsなので、ORDER BYは省略可能。
;
CREATE INDEX tbuwn_device ON tbuw_normal USING btree (device_id);
私の環境では、52 GiBの表(46分)と、32 GiBの索引(2時間37分)が作られた:
habe=# 上記CTAS文
時間: 2749399.525 ミリ秒(45:49.400)
habe=# 上記CREATE INDEX文
時間: 9460848.753 ミリ秒 (02:37:40.849)
habe=# \dt+ tbuw_normal
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------+-------+-------+---------+-------------
public | tbuw_normal | table | habe | 52 GB |
(1 row)
habe=# \di+ tbuwn_device
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------------+-------+-------+-------------+---------+-------------
public | tbuwn_device | index | habe | tbuw_normal | 32 GB |
(1 row)
トビウオさん問題の再現
この状態で、トビウオさんの「パフォーマンスが劇的に落ちる」問題が発生するようになったはずだ。 そこで、同じように特定デバイスの600秒分のデータ(=60万行)を取得してみよう:
habe=# EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT COUNT(*), SUM(measure) FROM tbuw_normal WHERE time BETWEEN 1564619100000 AND 1564619700000 AND device_id='DEV00046';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14756409.31..14756409.32 rows=1 width=12) (actual time=360297.874..360297.874 rows=1 loops=1)
Output: count(*), sum(measure)
Buffers: shared hit=96 read=6878886
-> Gather (cost=14756409.09..14756409.30 rows=2 width=12) (actual time=360297.396..360303.479 rows=3 loops=1)
Output: (PARTIAL count(*)), (PARTIAL sum(measure))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=96 read=6878886
-> Partial Aggregate (cost=14755409.09..14755409.10 rows=1 width=12) (actual time=360279.186..360279.186 rows=1 loops=3)
Output: PARTIAL count(*), PARTIAL sum(measure)
Buffers: shared hit=96 read=6878886
Worker 0: actual time=360268.267..360268.267 rows=1 loops=1
Buffers: shared hit=33 read=2294912
Worker 1: actual time=360272.651..360272.651 rows=1 loops=1
Buffers: shared hit=37 read=2297944
-> Parallel Seq Scan on public.tbuw_normal (cost=0.00..14753982.93 rows=285232 width=4) (actual time=48581.185..360222.114 rows=200000 loops=3)
Output: "time", device_id, measure
Filter: ((tbuw_normal."time" >= '1564619100000'::bigint) AND (tbuw_normal."time" <= '1564619700000'::bigint) AND (tbuw_normal.device_id = 'DEV00046'::text))
Rows Removed by Filter: 359800033
Buffers: shared hit=96 read=6878886
Worker 0: actual time=48570.102..360210.804 rows=200545 loops=1
Buffers: shared hit=33 read=2294912
Worker 1: actual time=48574.643..360216.196 rows=199597 loops=1
Buffers: shared hit=37 read=2297944
Planning Time: 0.067 ms
Execution Time: 360303.519 ms
(26 rows)
約6分(360,303.519ミリ秒)。
遅いのは想定通りとして、そもそも索引が効いていない(Parallel Seq Scan on public.tbuw_normal)のが気になる。
100デバイスのうち1デバイスに絞り込んでいる(WHERE device_id='DEV00046')ので、索引だけで処理対象レコードを1%に絞り込めるはずなのに。
これには理由があるのだが、分析は後にして、強引に索引が効くようにしてみよう:
habe=# /*+ IndexScan(tbuw_normal tbuwn_device) */ EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT COUNT(*), SUM(measure) FROM tbuw_normal WHERE time BETWEEN 1564619100000 AND 1564619700000 AND device_id='DEV00046';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=47406782.48..47406782.49 rows=1 width=12) (actual time=432050.170..432050.170 rows=1 loops=1)
Output: count(*), sum(measure)
Buffers: shared read=6920365
-> Index Scan using tbuwn_device on public.tbuw_normal (cost=0.58..47403359.69 rows=684558 width=4) (actual time=60009.993..431926.155 rows=600001 loops=1)
Output: "time", device_id, measure
Index Cond: (tbuw_normal.device_id = 'DEV00046'::text)
Filter: ((tbuw_normal."time" >= '1564619100000'::bigint) AND (tbuw_normal."time" <= '1564619700000'::bigint))
Rows Removed by Filter: 10200000
Buffers: shared read=6920365
Planning Time: 2.924 ms
Execution Time: 432053.498 ms
(11 rows)
実行時間は更に伸びて7分超(432,053.498ミリ秒)。ちゃんと索引スキャンになっている(Index Scan using tbuwn_device)し、索引だけで1%に絞り込めている証拠が残っている:
-> Index Scan using tbuwn_device (略) (actual 略 rows=600001 loops=1)
# ↑索引スキャンの結果は60万行であり、
Index Cond: (tbuw_normal.device_id = 'DEV00046'::text)
Filter: ((tbuw_normal."time" >= '1564619100000'::bigint) AND (tbuw_normal."time" <= '1564619700000'::bigint))
# ↑それは、索引でdevice_id列を絞り込んだ上で、time列を表の値でフィルターした結果だが、
Rows Removed by Filter: 10200000
# ↑フィルターで消したのは1020万行なので、索引だけで1080万行(表全体の1%)に絞り込めていた計算になる
なにかがおかしい。ぽすとぐれすきゅーえるの、ばぐだろうか。 (もちろんそんな訳はない)
索引で高速化しない原因
原因究明のため、実行結果をちゃんと読んでみよう。 注目すべきは最初の数行だ:
# 全表スキャンだった時
Finalize Aggregate (cost=14756409.31..14756409.32 rows=1 width=12) (actual time=360297.874..360297.874 rows=1 loops=1)
Buffers: shared hit=96 read=6878886
# 索引スキャンだった時
Aggregate (cost=47406782.48..47406782.49 rows=1 width=12) (actual time=432050.170..432050.170 rows=1 loops=1)
Buffers: shared read=6920365
すると、おかしな点が2つ見つかる。
索引を使っても、ファイルアクセスが減っていない! (Buffers: shared hit=XXXX read=YYYY)
PostgreSQLは、どんなに大きな表や索引も「ページ」という細切れの単位で管理している。
ページのサイズはPostgreSQLのコンパイル時に決まっており、普通は8 KiBである。
実行結果中のBuffers:は、ファイルから(≠ディスクから)読み込んだページ数(read=YYYY)と、たまたまPostgreSQLのバッファー上にあったのでファイルアクセスを省略したページ数(hit=XXXX)を示している。
そこでBuffers:の値を見直してみると、どちらの実行結果もread=YYYYの値は700万足らずである。
これは概ね55 GiB程度、つまりtbuw_normal表全体に匹敵する量である。
全表スキャンなら当然の結果だが、索引スキャンがこうなるのは直感と合わない。
これは、レコードの並び方が原因だ。
今回のテーブルは約700万ページで10億件程度であり、レコードも固定長なので、各ページには概ね140レコード程度が収まっているはずだ。
デバイスは100台(DEV00000-DEV00099)としたので、各デバイス(例えばDEV00046)は、平均で各ページに1.4回ずつ格納されている計算になる。
格納順がランダムならば、DEV00046が1回も格納されていないページや、たまたま30回格納されているページもあるだろう。
索引を使えば、前者のようなページは読まずに済むことが判るし、後者は1ページ読むだけで30レコードも抽出できるお得なページだと判る。
だが、今回は「レコードは時系列で挿入される」と仮定してORDER BY 観測時刻順で挿入してしまった。
このため、各デバイスのレコードが「どのページにも、少なくとも1回、多くとも2回格納されている」という状態になってしまったのだ(TODO: 図を入れた方がいいカモ)。
すると、索引でアクセス対象レコード数が99%削減できたとしても、そのレコードたちを得るためにアクセスすべきページ数は全く減らない。
むしろ、索引にもアクセスしなければならない分、ページ数は増えてしまっている。
この理屈だと、以下のようになりそうだ:
- デバイスの種類が(140台よりもずっと)多いと、各デバイスのレコードが「2ページに1回」「10ページに1回」のようになり、アクセスページ数も減らせる
ORDER BY デバイスID順でレコードを挿入すると、各デバイスのレコードが特定少数ページに集中するようになり、アクセスページ数が激減する
実際に後者を試すと以下のようになった:
habe=# /*+ IndexScan(tbuw_reversed tbuwr_device) */ EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT COUNT(*), SUM(measure) FROM tbuw_reversed WHERE time BETWEEN 1564619100000 AND 1564619700000 AND device_id='DEV00046';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=420564.79..420564.80 rows=1 width=12) (actual time=19979.644..19979.644 rows=1 loops=1)
Output: count(*), sum(measure)
Buffers: shared hit=40478 read=110175
-> Gather (cost=420564.77..420564.78 rows=2 width=12) (actual time=19979.327..19988.835 rows=3 loops=1)
Output: (PARTIAL count(*)), (PARTIAL sum(measure))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=40478 read=110175
-> Partial Aggregate (cost=419564.77..419564.78 rows=1 width=12) (actual time=19700.688..19700.688 rows=1 loops=3)
Output: PARTIAL count(*), PARTIAL sum(measure)
Buffers: shared hit=40478 read=110175
Worker 0: actual time=19561.725..19561.725 rows=1 loops=1
Buffers: shared hit=13491 read=36268
Worker 1: actual time=19561.778..19561.778 rows=1 loops=1
Buffers: shared hit=13574 read=36454
-> Parallel Index Scan using tbuwr_device on public.tbuw_reversed (cost=0.58..418157.61 rows=281432 width=4) (actual time=2624.196..19679.108 rows=200000 loops=3)
Output: "time", device_id, measure
Index Cond: (tbuw_reversed.device_id = 'DEV00046'::text)
Filter: ((tbuw_reversed."time" >= '1564619100000'::bigint) AND (tbuw_reversed."time" <= '1564619700000'::bigint))
Rows Removed by Filter: 3400000
Buffers: shared hit=40478 read=110175
Worker 0: actual time=2485.058..19540.126 rows=200558 loops=1
Buffers: shared hit=13491 read=36268
Worker 1: actual time=2485.492..19540.019 rows=198621 loops=1
Buffers: shared hit=13574 read=36454
Planning Time: 0.215 ms
Execution Time: 19988.879 ms
(27 rows)
ファイルアクセスしたページ数が、1.5%程度(700万→11万)に減っている。 処理速度は18倍速(6分→20秒)とイマイチだが、まあ良いとしよう。
索引スキャンの方が高コストと見積もられている! (cost=XXX..YYY)
同じSQL文を実行する場合でも、複数ある索引たちのうちどれを使う(あるいはどれも使わない)か、複数ある表たちをどのような順で結合するか、などなど、いろいろな戦略(=実行計画)が考えられる。 戦略によって実行結果は変わらないが、メモリー使用量や実行時間などは大きく変わってくる。なので、いかに良い戦略を選ぶかが、RDBMSの性能の鍵となる。
戦略を選ぶ際、各戦略の「悪さ」に当たるのが推定コスト値であり、EXPLAINで実行計画を表示した際にcost=XXX..YYYで表示される値だ。
コスト値は2つあるが、1つ目(XXX)は先頭行を取得するまでのコストで、2つ目(YYY)が最終行を取得するまでのコストである。とりあえずは後者を見れば良い。
さて、さきほど述べたようにファイルアクセス量はほぼ同じである。 通常、RDBMSではファイルアクセスが最も高コストなので、ファイルアクセス量が同じくらいならば、同じくらいのコストになりそうなものである。 だが実際には、3倍近いコスト差が出てしまっている(全表=14,756,409.32 vs. 索引=47,406,782.49)。
これは、全表スキャンと索引スキャンとでは、ファイルアクセス方法に違いがあるからだ。
全表スキャンの場合、表のデータファイルを開き、前から順に全ページにアクセスすればよい。 通常はシーケンシャルアクセスとなるので、普通のハードディスクであっても100 MiB/s以上の速度が出る。 つまり、毎秒1万ページ以上でアクセスできる可能性が高い。
これに対して索引スキャンの場合、表のデータファイルを開き、索引内に出現する順番で各ページにアクセスする必要がある。 B+木索引だと、索引内の出現順はキーの大小だけで決まっており、データファイル内のページの順序とは全く関係ない。 このため、表のデータファイルはランダムアクセスに近い状態となり、シーケンシャルアクセスと比べると性能が大きく落ちてしまう。
PostgreSQLには、デフォルトで「ランダムアクセスのコストは4倍」という設定が入っており、ランダムアクセスを避けるようになっている。 試しにこれを「ランダムアクセスもシーケンシャルアクセスもコストは同じ」と騙してやると、索引スキャンの方が低コスト(索引=12,062,032.91 vs. 全表=14,756,409.12)となり、ヒント句なしでも索引スキャンとなってくれる
habe=# SET SESSION random_page_cost=1;
habe=# /*+ IndexScan(tbuw_normal tbuwn_device) */ EXPLAIN (VERBOSE) SELECT COUNT(*), SUM(measure) FROM tbuw_normal WHERE time BETWEEN 1564619100000 AND 1564619700000 AND device_id='DEV00046';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=12062032.90..12062032.91 rows=1 width=12)
Output: count(*), sum(measure)
-> Index Scan using tbuwn_device on public.tbuw_normal (cost=0.58..12058610.11 rows=684558 width=4)
Output: "time", device_id, measure
Index Cond: (tbuw_normal.device_id = 'DEV00046'::text)
Filter: ((tbuw_normal."time" >= '1564619100000'::bigint) AND (tbuw_normal."time" <= '1564619700000'::bigint))
(6 rows)
habe=# /*+ SeqScan(tbuw_normal) */ EXPLAIN (VERBOSE) SELECT COUNT(*), SUM(measure) FROM tbuw_normal WHERE time BETWEEN 1564619100000 AND 1564619700000 AND device_id='DEV00046';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14756409.11..14756409.12 rows=1 width=12)
(略)
habe=# SET SESSION random_page_cost=4;
ちなみに、索引を使いつつ、シーケンシャルに近いアクセスをする方法がないわけではない。 機会があれば書こうと思う。
まとめ
デバイスIDに対する索引は、常識的なシステムならパフォーマンス向上に繫がったのだろうが、巨大な時系列データに対してはむしろ邪魔でしかないことがわかった:
- レコード数を減らせる索引も、ページ数が減らないのならむしろない方がマシ: アクセスページ数の削減が重要
- 大量のランダムアクセスを発生させる索引なら、むしろない方がマシ: ページ数をさほど減らせないなら、シーケンシャルアクセス化を優先すべき
というわけで、次回はこれらの問題を解決する2つの案を試してみよう。
- 閲覧数 2052
コメントを追加