PostgreSQL非同步IO實測

德哥發表於2017-05-12

標籤

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 on Linux 最佳部署手冊》

《Linux 效能診斷 perf使用指南》

《PostgreSQL 原始碼效能診斷(perf profiling)指南》

https://www.postgresql.org/docs/devel/static/runtime-config-resource.html

src/backend/executor/nodeBitmapHeapscan.c


相關文章