PostgreSQLの「effective_io_concurrency」という設定をご存知でしょうか？
PostgreSQLのドキュメントには、
「PostgreSQLが同時実行可能であると想定する同時ディスクI/O操作の数を設定します」
とあります。
とりあえず有効にしておけば同時実行でなんか速そう！という印象をうけるのですが、そんなことはありません！
PostgreSQL9.6以下の場合はオフにした方がパフォーマンスが良いケースが多いと思います。
今回はなぜオフにした方が速いのか、みていきたいと思います。
※PostgreSQL10以降で実装されたパラレルビットマップヒープスキャンにはあてはまりません
※常に「オフ」が最適設定ということではありません。システム構成・データ傾向次第です
※検証環境はCentOS7、PostgreSQL9.6です
effective_io_concurrencyとは？
ビットマップヒープスキャン時に、ストレージからのデータ読み込みIOを、データの処理と並行させることでパフォーマンス向上を狙うもので、ビットマップヒープスキャン専用の設定です。
effective_io_concurrencyを1以上に設定することで有効となり、最大は1000です。設定値を上げるほどより多くのデータを非同期で読み込むようになります。
0に設定することで非同期IOを無効化することができます。
どうやって非同期でデータを読み込むのか？
ずばり、posix_fadvise関数です。ソースコードには将来的に別の実装も検討したいとコメントがありますが、現時点ではありません。
そのため、posix_fadvise関数が利用できないシステムでは動作しません。
posix_fadvise関数とは、アプリケーションがカーネルに、次に行う予定のストレージアクセスを知らせるためのものです。
これにより、アプリケーションが実際にストレージアクセスを行うまでの間にカーネルが先行して準備を進めることが可能となります。
たとえば、posix_fadvice(fd, 0, 8192, POSIX_FADV_WILLNEED)とすると、
カーネルがファイルディスクリプタfdのファイルの先頭から8KBをページキャッシュに読み込みます。
このあとにread()すると、すでにページキャッシュに読み込まれているため、ディスクアクセスが発生することなく即座にデータを取得可能となります。
（read()までにカーネルの処理が間に合っていれば）
PostgreSQLはまさにこれを使っています。データ処理を行う際に次に読み込む予定のページをposix_fadviseでカーネルに知らせています。
で、何が問題なの？
とても効率的に見えるのですが、逆にとても非効率になってしまうケースがあるんです。
それは「ビットマップヒープスキャンでアクセスするページの多くが隣接している場合」です。
隣接している、つまり、ストレージアクセスがシーケンシャルリードになる場合です。
PostgreSQLは常にページサイズである8KB単位でread/writeをします。たとえば読み取る予定のページが16個隣接していた場合、128KBのread()一回で済みますが、PostgreSQLでは8KBのread()を16回実行します。
とても非効率に見えますが、実際はOSの機能でカバーされるため、ほとんど問題になりません。
Linuxには「read_ahead_kb」という設定があります。シーケンシャルリードが続く場合、この設定にもとづいてカーネルはread()で指定されたアドレス以降のデータを一括で読み込みます。
そのため、PostgreSQLがread()を16回実行したとしても、実際にストレージに16回IOが発行されるということはありません。おそらくせいぜい２～３回です。
が、ここで問題が発生します。posix_fadviceによる先読みです。
PostgreSQLは先に書いたようにposix_fadviceで先読みを行いますが、これも常に8KB単位です。連続した領域に対してposix_fadviceを実行しても、OSはread()のときのように先読みすることはなく、そのまま8KBでIOを発行します。
結果、本来であればOSの機能により抑えられるIO発行回数が数倍、数十倍にもふくれあがり、パフォーマンスが大きく悪化します。
実際に試してみる
実際に再現させてみます。テストとして以下のテーブルを用います。
|
1 |
test=# CREATE TABLE foobar(id serial primary key, created_at timestamp, data text); |
テストデータとして500万件投入します。投入するデータはcreated_atが1秒ずつ進み、dataはすべて同じ900Byteの'A'が並んだデータを用意します。
これで1ページにちょうど8行入ることになります。
テストデータ
|
1 2 3 4 5 |
"2019-01-01 00:00:01","AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" "2019-01-01 00:00:02","AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" ・ ・ ・ |
コピーで投入します。
|
1 2 |
test=# \copy foobar (created_at,data) FROM '5000000.csv' CSV; COPY 5000000 |
テーブルファイルを確認します。1ページに8行入るデータを500万件投入したので、計625000ページで、ファイルサイズは625000 * 8192 = 5120000000Byteとなります。
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# SELECT * FROM (SELECT datid FROM pg_stat_database WHERE datname = 'test') db CROSS JOIN (SELECT relfilenode FROM pg_class WHERE relname = 'foobar') tbl; datid | relfilenode -------+------------- 16384 | 16407 [postgres@testdb]$ ll data/base/16384/16407{,.*} -rw------- 1 postgres postgres 1073741824 9月 5 10:49 data/base/16384/16407 -rw------- 1 postgres postgres 1073741824 9月 5 10:49 data/base/16384/16407.1 -rw------- 1 postgres postgres 1073741824 9月 5 10:49 data/base/16384/16407.2 -rw------- 1 postgres postgres 1073741824 9月 5 10:50 data/base/16384/16407.3 -rw------- 1 postgres postgres 825032704 9月 5 10:50 data/base/16384/16407.4 |
ビットマップヒープスキャンを発生させるため、created_atにbrinインデックスをはります。
|
1 2 |
test=# CREATE INDEX foobar_created_at_brin ON foobar USING brin(created_at); CREATE INDEX |
いよいよ実験です！
まずはeffective_io_concurrencyを1で試してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
test=# set effective_io_concurrency=1; SET test=# EXPLAIN ANALYZE SELECT COUNT(*) FROM foobar WHERE created_at > '2019-02-01' AND id % 2 = 0; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=682865.26..682865.27 rows=1 width=8) (actual time=20898.775..20898.775 rows=1 loops=1) -> Bitmap Heap Scan on foobar (cost=17414.54..682836.39 rows=11549 width=0) (actual time=84.223..20722.441 rows=1160800 loops=1) Recheck Cond: (created_at > '2019-02-01 00:00:00'::timestamp without time zone) Rows Removed by Index Recheck: 640 Filter: ((id % 2) = 0) Rows Removed by Filter: 1160800 Heap Blocks: lossy=290280 -> Bitmap Index Scan on foobar_created_at_brin (cost=0.00..17411.65 rows=2309820 width=0) (actual time=60.019..60.019 rows=2903040 loops=1) Index Cond: (created_at > '2019-02-01 00:00:00'::timestamp without time zone) Planning time: 0.095 ms Execution time: 20898.836 ms (11 rows) |
約20秒かかりました。このとき、ストレージへのIO発行状況は以下です。
(blktrace/blkparseで取得）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
253,1 1 32703 4.847592450 14489 Q R 66304224 + 16 [postgres] 253,1 1 32704 4.847642966 0 C R 66304224 + 16 [0] 253,1 1 32705 4.847658615 14489 Q R 66304240 + 16 [postgres] 253,1 1 32706 4.847708921 0 C R 66304240 + 16 [0] 253,1 1 32707 4.847727665 14489 Q R 66304256 + 16 [postgres] 253,1 1 32708 4.847778924 0 C R 66304256 + 16 [0] 253,1 1 32709 4.847794801 14489 Q R 66304272 + 16 [postgres] 253,1 1 32710 4.847846454 0 C R 66304272 + 16 [0] 253,1 1 32711 4.847862889 14489 Q R 66304288 + 16 [postgres] 253,1 1 32712 4.847914285 0 C R 66304288 + 16 [0] 253,1 1 32713 4.847930219 14489 Q R 66304304 + 16 [postgres] 253,1 1 32714 4.847981493 0 C R 66304304 + 16 [0] 253,1 1 32715 4.847997950 14489 Q R 66304320 + 16 [postgres] 253,1 1 32716 4.848048647 0 C R 66304320 + 16 [0] |
+16となっているのは512Byte計算で、8KBのことです。8KBの読み込みIOが続いているのがわかります。
straceでみてみると、posix_fadviceに対応するシステムコールfadvise64が8KBで連続した領域に実行されているのがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 |
fadvise64(32, 871890944, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871899136, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871907328, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871915520, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871923712, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871931904, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871940096, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871948288, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871956480, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871964672, 8192, POSIX_FADV_WILLNEED) = 0 fadvise64(32, 871972864, 8192, POSIX_FADV_WILLNEED) = 0 |
では次にeffective_io_concurrencyを0で試してみます。（キャッシュ等はすべてクリアしたうえで）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
test=# set effective_io_concurrency=0; SET test=# EXPLAIN ANALYZE SELECT COUNT(*) FROM foobar WHERE created_at > '2019-02-01' AND id % 2 = 0; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=682865.26..682865.27 rows=1 width=8) (actual time=3625.882..3625.882 rows=1 loops=1) -> Bitmap Heap Scan on foobar (cost=17414.54..682836.39 rows=11549 width=0) (actual time=28.424..3529.730 rows=1160800 loops=1) Recheck Cond: (created_at > '2019-02-01 00:00:00'::timestamp without time zone) Rows Removed by Index Recheck: 640 Filter: ((id % 2) = 0) Rows Removed by Filter: 1160800 Heap Blocks: lossy=290280 -> Bitmap Index Scan on foobar_created_at_brin (cost=0.00..17411.65 rows=2309820 width=0) (actual time=22.098..22.098 rows=2903040 loops=1) Index Cond: (created_at > '2019-02-01 00:00:00'::timestamp without time zone) Planning time: 0.097 ms Execution time: 3625.937 ms (11 rows) |
劇的改善です！3秒強になりました！
このときのストレージへのIO発行状況は以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
253,1 1 3306 2.360343529 16694 Q R 63859640 + 1024 [postgres] 253,1 1 3307 2.360385438 16694 Q R 63860664 + 1024 [postgres] 253,1 1 3308 2.360436292 16694 Q R 63861688 + 1024 [postgres] 253,1 1 3309 2.360477008 16694 Q R 63862712 + 1024 [postgres] 253,1 1 3310 2.360517538 16694 Q R 63863736 + 1024 [postgres] 253,1 1 3311 2.360558272 16694 Q R 63864760 + 1024 [postgres] 253,1 1 3312 2.360603382 16694 Q R 63865784 + 1024 [postgres] 253,1 1 3313 2.360642645 16694 Q R 63866808 + 1024 [postgres] 253,1 1 3314 2.360682197 16694 Q R 63867832 + 1024 [postgres] 253,1 1 3315 2.360727269 16694 Q R 63868856 + 1024 [postgres] 253,1 1 3316 2.360767066 16694 Q R 63869880 + 1024 [postgres] 253,1 1 3317 2.360806320 16694 Q R 63870904 + 1024 [postgres] |
先ほどの8KBのリードが並んでいたのとは違い、512KBのリードが並んでいます。
かなり発行IO数を減らせていそうです。
最後に
今回の実験では確実にデータが隣接しているビットマップヒープスキャンとなるよう意図的にデータを用意したため、顕著に違いがでました。
実際に運用しているシステムではここまでデータが隣接することはないと思いますが、ビットマップヒープスキャンはスキャン開始前にページ位置順にソートするため、極端にランダムアクセスになることも少ないと思います。
そのため、effective_io_concurrency無効化は十分に試す価値があると思います。特定のクエリだけ実行前にSETでオフにするというのもアリですね。
PostgreSQL10以降で試したところ、パラレルビットマップヒープスキャンの場合はeffective_io_concurrencyを1にしてもread_aheadが機能していました。
パラレルでない場合は9系までと同じ挙動となるので注意してください。
