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
相關文章
- java同步非阻塞IOJava
- Java 非阻塞 IO 和非同步 IOJava非同步
- IO模式和IO多路複用（阻塞IO、非阻塞IO、同步IO、非同步IO等概念）模式非同步
- IO - 同步 非同步 阻塞 非阻塞的區別非同步
- 網路IO之阻塞、非阻塞、同步、非同步總結非同步
- 談談對不同I/O模型的理解 (阻塞/非阻塞IO，同步/非同步IO)模型非同步
- IO通訊模型（二）同步非阻塞模式NIO（NonBlocking IO）模型模式BloC
- 如何解讀 Java IO、NIO 中的同步阻塞與同步非阻塞？Java
- 11、協程和io教程01 -- 併發 並行 同步 非同步 阻塞 非阻塞 以及 IO多路複用並行非同步
- 阻塞IO與非阻塞IO
- 【死磕NIO】— 阻塞IO，非阻塞IO，IO複用，訊號驅動IO，非同步IO，這你真的分的清楚嗎？非同步
- Java網路程式設計和NIO詳解5：Java 非阻塞 IO 和非同步 IOJava程式設計非同步
- 如何給女朋友解釋什麼是IO中的阻塞、非阻塞、同步、非同步？非同步
- 非同步IO,同步IO,Direct IO,FILESYSTEMIO_OPTIONS, DISK_ASYNCH_IO [final]非同步
- 非同步IO非同步
- asyncio（非同步io）非同步
- ORACLE 非同步IOOracle非同步
- 檔案讀寫網路IO簡單瞭解，同步IO和非同步IO非同步
- 同步非同步，阻塞非阻塞非同步
- 非同步、同步、阻塞、非阻塞非同步
- 同步、非同步、阻塞、非阻塞非同步
- PostgreSQL IO最佳化技巧SQL
- 同步非同步 與 阻塞非阻塞非同步
- 理解阻塞、非阻塞、同步、非同步非同步
- 同步、非同步，阻塞、非阻塞理解非同步
- 同步、非同步、阻塞與非阻塞非同步
- 同步、非同步、阻塞和非阻塞非同步
- python中非同步非阻塞如何實現Python非同步
- [轉]阻塞／非阻塞與同步／非同步非同步
- 同步與非同步 阻塞與非阻塞非同步
- Oracle 之 AIO (非同步io)OracleAI非同步
- Flink - 旁路快取和非同步IO的實現快取非同步
- 使用PostgreSQL_Notify實現多例項快取同步SQL快取
- 非同步和非阻塞非同步
- 同步、非同步、阻塞、非阻塞的區別非同步
- SOA 非功能測試最佳實踐
- 使用C++20協程和io_uring優雅地實現非同步IOC++非同步
- Windows核心程式設計：第10章 同步裝置IO與非同步裝置IOWindows程式設計非同步