942
PostgreSQL 異步IO實測
標簽
PostgreSQL , effective_io_concurrency , 異步IO
背景
異步IO的目的是充分發揮塊設備的吞吐能力，讓塊設備處於更繁忙的工作狀態（一次連續攝取更多的塊），而不是等用戶進程需要數據時再讀取。
如果數據庫並發連接（或者活躍會話）足夠時，並且塊設備處於繁忙狀態，那麼沒有必要開啟異步IO，因為開了也沒什麼用，塊設備已經足夠的忙了。
目前PostgreSQL的bitmap heap scan支持異步IO，因為bitmap heap scan是按順序讀取堆表的數據塊的，對於機械硬盤，bitmap heap scan異步IO效率可以得到充分的發揮。（實際上全表掃描也適合異步IO。）
異步IO的參數effective_io_concurrency，應該如何設置呢？
如果是磁盤陣列，根據表空間所在的塊設備進行設置，例如RAID0， RAID10，設置為磁盤個數，而RAID5或者其他RAID，設置為實際的數據盤個數（如，raid5(10)設置為9）。
如果設置為0，表示不使用異步IO。
僅僅當操作係統支持posix時，才能使用異步IO。
effective_io_concurrency (integer)
Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.
Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel.
The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.
For magnetic drives, a good starting point for this setting is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database.
(For RAID 5 the parity drive should not be counted.)
However, if the database is often busy with multiple queries issued in concurrent sessions, lower values may be sufficient to keep the disk array busy.
A value higher than needed to keep the disks busy will only result in extra CPU overhead.
SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds.
Asynchronous I/O depends on an effective posix_fadvise function, which some operating systems lack.
If the function is not present then setting this parameter to anything but zero will result in an error.
On some operating systems (e.g., Solaris), the function is present but does not actually do anything.
The default is 1 on supported systems, otherwise 0.
This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see ALTER TABLESPACE).
那麼生產中應該如何設置呢？
我們實際測試一下，如果是SSD，從測試結果看，建議關閉異步IO。（雖然手冊中提到如果是內存盤或SSD盤，可以設置更大的值，但是實測效果不理想。）
測試用例
src/test/regress/expected/select_parallel.out
-- test parallel bitmap heap scan.
set enable_seqscan to off;
set enable_indexscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;
set enable_material to off;
-- test prefetching, if the platform allows it
DO $$
BEGIN
SET effective_io_concurrency = 50;
EXCEPTION WHEN invalid_parameter_value THEN
END $$;
set work_mem='64kB'; --set small work mem to force lossy pages
explain (costs off)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
QUERY PLAN
------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk2
Filter: (thousand = 0)
-> Gather
Workers Planned: 4
-> Parallel Bitmap Heap Scan on tenk1
Recheck Cond: (hundred > 1)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred > 1)
(10 rows)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
count
-------
98000
(1 row)
create table bmscantest (a int, t text);
insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
create index i_bmtest ON bmscantest(a);
select count(*) from bmscantest where a>1;
count
-------
99999
(1 row)
reset enable_seqscan;
reset enable_indexscan;
reset enable_hashjoin;
reset enable_mergejoin;
reset enable_material;
reset effective_io_concurrency;
reset work_mem;
drop table bmscantest;
測試
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
42328
(1 row)
postgres=# set enable_seqscan=off;
SET
postgres=# set enable_indexscan=off;
SET
postgres=# set work_mem ='64kB';
SET
postgres=# set max_parallel_workers_per_gather =0;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=338227.11..338227.12 rows=1 width=8) (actual time=2634.805..2634.806 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=110649
-> Bitmap Heap Scan on public.ptest (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=418.257..1735.802 rows=9999000 loops=1)
Output: id, info
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 40
Heap Blocks: exact=408 lossy=82918
Buffers: shared hit=110649
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=418.158..418.158 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.123 ms
Execution time: 2634.858 ms
(14 rows)
postgres=# set effective_io_concurrency =0;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=338227.11..338227.12 rows=1 width=8) (actual time=2619.868..2619.869 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=110649
-> Bitmap Heap Scan on public.ptest (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=418.974..1721.578 rows=9999000 loops=1)
Output: id, info
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 40
Heap Blocks: exact=408 lossy=82918
Buffers: shared hit=110649
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=418.873..418.873 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.155 ms
Execution time: 2619.930 ms
(14 rows)
postgres=# set effective_io_concurrency =1000;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=338227.11..338227.12 rows=1 width=8) (actual time=2661.584..2661.585 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=110649
-> Bitmap Heap Scan on public.ptest (cost=104910.47..313230.01 rows=9998843 width=0) (actual time=417.591..1761.522 rows=9999000 loops=1)
Output: id, info
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 40
Heap Blocks: exact=408 lossy=82918
Buffers: shared hit=110649
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=417.473..417.473 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.113 ms
Execution time: 2661.632 ms
(14 rows)
postgres=# set max_parallel_workers_per_gather =4;
SET
postgres=# set effective_io_concurrency =1000;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=226740.55..226740.56 rows=1 width=8) (actual time=1017.452..1017.452 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=111309
-> Gather (cost=226740.13..226740.54 rows=4 width=8) (actual time=1017.350..1017.447 rows=5 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=111309
-> Partial Aggregate (cost=225740.13..225740.14 rows=1 width=8) (actual time=1014.227..1014.227 rows=1 loops=5)
Output: PARTIAL count(*)
Buffers: shared hit=110649
Worker 0: actual time=1013.492..1013.492 rows=1 loops=1
Buffers: shared hit=16355
Worker 1: actual time=1013.497..1013.497 rows=1 loops=1
Buffers: shared hit=16269
Worker 2: actual time=1013.510..1013.510 rows=1 loops=1
Buffers: shared hit=16333
Worker 3: actual time=1013.501..1013.501 rows=1 loops=1
Buffers: shared hit=16268
-> Parallel Bitmap Heap Scan on public.ptest (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=418.784..840.764 rows=1999800 loops=5)
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 8
Heap Blocks: exact=92 lossy=18009
Buffers: shared hit=110649
Worker 0: actual time=417.217..844.848 rows=1962600 loops=1
Buffers: shared hit=16355
Worker 1: actual time=422.223..844.926 rows=1952280 loops=1
Buffers: shared hit=16269
Worker 2: actual time=417.216..844.028 rows=1959960 loops=1
Buffers: shared hit=16333
Worker 3: actual time=417.216..840.404 rows=1952080 loops=1
Buffers: shared hit=16268
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=419.867..419.867 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.121 ms
Execution time: 1027.083 ms
(37 rows)
postgres=# set effective_io_concurrency =1;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=226740.55..226740.56 rows=1 width=8) (actual time=1006.182..1006.182 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=111309
-> Gather (cost=226740.13..226740.54 rows=4 width=8) (actual time=1006.073..1006.177 rows=5 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=111309
-> Partial Aggregate (cost=225740.13..225740.14 rows=1 width=8) (actual time=1003.124..1003.124 rows=1 loops=5)
Output: PARTIAL count(*)
Buffers: shared hit=110649
Worker 0: actual time=1002.447..1002.447 rows=1 loops=1
Buffers: shared hit=16408
Worker 1: actual time=1002.441..1002.441 rows=1 loops=1
Buffers: shared hit=16312
Worker 2: actual time=1002.446..1002.446 rows=1 loops=1
Buffers: shared hit=16093
Worker 3: actual time=1002.436..1002.436 rows=1 loops=1
Buffers: shared hit=16287
-> Parallel Bitmap Heap Scan on public.ptest (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=413.650..828.524 rows=1999800 loops=5)
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 8
Heap Blocks: exact=85 lossy=18141
Buffers: shared hit=110649
Worker 0: actual time=412.974..833.142 rows=1968960 loops=1
Buffers: shared hit=16408
Worker 1: actual time=412.994..831.530 rows=1957440 loops=1
Buffers: shared hit=16312
Worker 2: actual time=412.980..831.353 rows=1931160 loops=1
Buffers: shared hit=16093
Worker 3: actual time=412.977..828.009 rows=1954440 loops=1
Buffers: shared hit=16287
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=416.164..416.164 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.124 ms
Execution time: 1015.976 ms
(37 rows)
postgres=# set effective_io_concurrency =0;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from ptest where id>1000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=226740.55..226740.56 rows=1 width=8) (actual time=943.288..943.288 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=111309
-> Gather (cost=226740.13..226740.54 rows=4 width=8) (actual time=943.170..943.282 rows=5 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=111309
-> Partial Aggregate (cost=225740.13..225740.14 rows=1 width=8) (actual time=940.060..940.060 rows=1 loops=5)
Output: PARTIAL count(*)
Buffers: shared hit=110649
Worker 0: actual time=939.276..939.276 rows=1 loops=1
Buffers: shared hit=16286
Worker 1: actual time=939.387..939.388 rows=1 loops=1
Buffers: shared hit=16303
Worker 2: actual time=939.389..939.390 rows=1 loops=1
Buffers: shared hit=16291
Worker 3: actual time=939.388..939.389 rows=1 loops=1
Buffers: shared hit=15943
-> Parallel Bitmap Heap Scan on public.ptest (cost=104910.47..219490.85 rows=2499711 width=0) (actual time=419.160..766.586 rows=1999800 loops=5)
Recheck Cond: (ptest.id > 1000)
Rows Removed by Index Recheck: 8
Heap Blocks: exact=92 lossy=18411
Buffers: shared hit=110649
Worker 0: actual time=418.404..771.100 rows=1954320 loops=1
Buffers: shared hit=16286
Worker 1: actual time=418.487..770.127 rows=1956360 loops=1
Buffers: shared hit=16303
Worker 2: actual time=418.491..770.200 rows=1954920 loops=1
Buffers: shared hit=16291
Worker 3: actual time=418.513..768.309 rows=1913160 loops=1
Buffers: shared hit=15943
-> Bitmap Index Scan on idx_ptest1 (cost=0.00..102410.76 rows=9998843 width=0) (actual time=421.750..421.750 rows=9999000 loops=1)
Index Cond: (ptest.id > 1000)
Buffers: shared hit=27323
Planning time: 0.169 ms
Execution time: 952.028 ms
(37 rows)
分別使用perf record -ag -p $pid 進行了跟蹤。因為這個版本編譯時沒有打開幾個需要的開關(-g -ggdb -fno-omit-frame-pointer)，所以看不出什麼問題。用戶可以自己測試並分析一下。
參考
《PostgreSQL 源碼性能診斷(perf profiling)指南》
https://www.postgresql.org/docs/devel/static/runtime-config-resource.html
src/backend/executor/nodeBitmapHeapscan.c
最後更新：2017-05-13 08:43:09