Hi,
I've tried to run a benchmark, similar to this one:
https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ@xxxxxxxxxxxxxx
CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';
pgbench -i -s 1000 --tablespace=test pgbench
echo "" >test.txt
for i in 0 1 2 4 8 16 32 64 128 256 ; do
sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
echo "effective_io_concurrency=$i" >>test.txt
psql pgbench -c "set effective_io_concurrency=$i; set
enable_indexscan=off; explain (analyze, buffers) select * from
pgbench_accounts where aid between 1000 and 10000000 and abalance != 0;"
>>test.txt
done
I get the following results:
effective_io_concurrency=0
Execution time: 40262.781 ms
effective_io_concurrency=1
Execution time: 98125.987 ms
effective_io_concurrency=2
Execution time: 55343.776 ms
effective_io_concurrency=4
Execution time: 52505.638 ms
effective_io_concurrency=8
Execution time: 54954.024 ms
effective_io_concurrency=16
Execution time: 54346.455 ms
effective_io_concurrency=32
Execution time: 55196.626 ms
effective_io_concurrency=64
Execution time: 55057.956 ms
effective_io_concurrency=128
Execution time: 54963.510 ms
effective_io_concurrency=256
Execution time: 54339.258 ms
The test was using 100 GB gp2 SSD EBS. More detailed query plans are
attached.
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
The results look really confusing to me in two ways. The first one is
that I've seen recommendations to set effective_io_concurrency=256 (or
more) on EBS. The other one is that effective_io_concurrency=1 (the
worst case) is actually the default for PostgreSQL on Linux.
Thoughts?
Regards,
Vitaliy
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=40261.322..40261.322 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3366.623..3366.623 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 17.285 ms
Execution time: 40262.781 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=98124.607..98124.607 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3373.380..3373.380 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 18.110 ms
Execution time: 98125.987 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=55340.663..55340.663 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3306.896..3306.896 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 30.986 ms
Execution time: 55343.776 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=52504.298..52504.298 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3332.724..3332.724 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 17.100 ms
Execution time: 52505.638 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=54952.558..54952.558 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3390.833..3390.833 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 18.043 ms
Execution time: 54954.024 ms
(12 rows)
effective_io_concurrency=16
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=54345.036..54345.036 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3401.819..3401.819 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 17.164 ms
Execution time: 54346.455 ms
(12 rows)
effective_io_concurrency=32
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=55195.211..55195.211 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3373.154..3373.154 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 18.490 ms
Execution time: 55196.626 ms
(12 rows)
effective_io_concurrency=64
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=55056.559..55056.559 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3351.440..3351.440 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 17.142 ms
Execution time: 55057.956 ms
(12 rows)
effective_io_concurrency=128
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=54962.081..54962.081 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3410.678..3410.678 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 17.441 ms
Execution time: 54963.510 ms
(12 rows)
effective_io_concurrency=256
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=137192.84..1960989.89 rows=1 width=97) (actual time=54337.825..54337.825 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 10000000))
Rows Removed by Index Recheck: 23
Filter: (abalance <> 0)
Rows Removed by Filter: 9999001
Heap Blocks: exact=97869 lossy=66050
Buffers: shared hit=3 read=191240
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..137192.84 rows=10540117 width=0) (actual time=3380.008..3380.008 rows=9999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 10000000))
Buffers: shared hit=3 read=27321
Planning time: 16.987 ms
Execution time: 54339.258 ms
(12 rows)
