pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
To: hzzhangjiazhi <hzzhangjiazhi@corp.netease.com>
Cc: Gary Doades <gpd@gpdnet.co.uk>
Cc: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: effective_io_concurrency on EBS/gp2
Date: Fri, 2 Feb 2018 13:46:22 +0200
Message-ID: <c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com> (raw )
In-Reply-To: <CAGTBQpZjj1ekDLyZ=RdyPhKAbLnow1ma66TDuBj=eradrgOskQ@mail.gmail.com >
References: <6c7a45df-f6ab-f2ce-6f84-9555864f6c86@gmail.com >
<CAMAYy4+SPqTLuwjWLrnrAnZ2EVyz97oQ2Hey46fffP8tihaRXQ@mail.gmail.com >
<768e3a56-46ed-3dd1-8722-953a47857a35@gmail.com >
<014801d39a99$d4c32930$7e497b90$@gpdnet.co.uk >
<8fb758a1-d7fa-4dcc-fb5b-07a992ae6a32@gmail.com >
<CAGTBQpYCWStnzsjTrN+vBsEwZHH2PKwO7ybxp_JQv9CsEJj4xw@mail.gmail.com >
<534b557c-3658-6d0f-2330-34a78da57e2f@gmail.com >
<9122FC8B-2129-4E10-807F-F9F34117A156@corp.netease.com >
<CAGTBQpZjj1ekDLyZ=RdyPhKAbLnow1ma66TDuBj=eradrgOskQ@mail.gmail.com >
I did some more tests. I've made an SQL dump of the table. Then used
head/tail commands to cut the data part. Then used shuf command to
shuffle rows, and then joined the pieces back and restored the table
back into DB.
Before:
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
effective_io_concurrency=0 Execution time: 1455.336 ms
effective_io_concurrency=1 Execution time: 8365.070 ms
effective_io_concurrency=2 Execution time: 4791.961 ms
effective_io_concurrency=4 Execution time: 4113.713 ms
effective_io_concurrency=8 Execution time: 1584.862 ms
effective_io_concurrency=16 Execution time: 1533.096 ms
effective_io_concurrency=8 Execution time: 1494.494 ms
effective_io_concurrency=4 Execution time: 3235.892 ms
effective_io_concurrency=2 Execution time: 4624.334 ms
effective_io_concurrency=1 Execution time: 7831.310 ms
effective_io_concurrency=0 Execution time: 1422.203 ms
After:
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
{6861090,18316007,2361004,11880097,5079470,9859942,13776329,12687163,3793362,18312052,15912971,9928864,10179242,9307499,2737986,13911147,5337329,12582498,3019085,4631617}
effective_io_concurrency=0 Execution time: 71321.723 ms
effective_io_concurrency=1 Execution time: 180230.742 ms
effective_io_concurrency=2 Execution time: 98635.566 ms
effective_io_concurrency=4 Execution time: 91464.375 ms
effective_io_concurrency=8 Execution time: 91048.939 ms
effective_io_concurrency=16 Execution time: 97682.475 ms
effective_io_concurrency=8 Execution time: 91262.404 ms
effective_io_concurrency=4 Execution time: 90945.560 ms
effective_io_concurrency=2 Execution time: 97019.504 ms
effective_io_concurrency=1 Execution time: 180331.474 ms
effective_io_concurrency=0 Execution time: 71469.484 ms
The numbers are not directly comparable with the previous tests, because
this time I used scale factor 200.
Regards,
Vitaliy
On 2018-02-01 20:39, Claudio Freire wrote:
> On Wed, Jan 31, 2018 at 11:21 PM, hzzhangjiazhi
> <hzzhangjiazhi@corp.netease.com> wrote:
>> HI
>>
>> I think this parameter will be usefull when the storage using RAID
>> stripe , otherwise turn up this parameter is meaningless when only has one
>> device。
> Not at all. Especially on EBS, where keeping a relatively full queue
> is necessary to get max thoughput out of the drive.
>
> Problem is, if you're scanning a highly correlated index, the
> mechanism is counterproductive. I had worked on some POC patches for
> correcting that, I guess I could work something out, but it's
> low-priority for me. Especially since it's actually a kernel "bug" (or
> shortcoming), that could be fixed in the kernel rather than worked
> around by postgres.
>
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1454.570..1454.570 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=447.877..447.877 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.782 ms
Execution time: 1455.336 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=8364.272..8364.272 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=448.043..448.043 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.036 ms
Execution time: 8365.070 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4791.196..4791.196 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=433.928..433.928 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.847 ms
Execution time: 4791.961 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4112.928..4112.928 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.873..470.873 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.694 ms
Execution time: 4113.713 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1584.067..1584.067 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=451.587..451.587 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.421 ms
Execution time: 1584.862 ms
(11 rows)
effective_io_concurrency=16
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1532.343..1532.343 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=429.148..429.148 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.802 ms
Execution time: 1533.096 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1493.721..1493.721 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=427.864..427.864 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.565 ms
Execution time: 1494.494 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=3235.104..3235.104 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.009..435.009 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.289 ms
Execution time: 3235.892 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4623.565..4623.565 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=425.661..425.661 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.529 ms
Execution time: 4624.334 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=7830.530..7830.530 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.078..435.078 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.361 ms
Execution time: 7831.310 ms
(11 rows)
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1421.417..1421.417 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.542..470.542 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.907 ms
Execution time: 1422.203 ms
(11 rows)
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71320.201..71320.201 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.222..620.222 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 25.095 ms
Execution time: 71321.723 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180229.423..180229.423 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=626.718..626.718 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.123 ms
Execution time: 180230.742 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=98634.125..98634.125 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.905..620.905 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 22.936 ms
Execution time: 98635.566 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91463.095..91463.095 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=643.029..643.029 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.676 ms
Execution time: 91464.375 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91047.653..91047.653 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=614.786..614.786 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.338 ms
Execution time: 91048.939 ms
(12 rows)
effective_io_concurrency=16
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97680.861..97680.861 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=616.763..616.763 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.479 ms
Execution time: 97682.475 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91260.958..91260.958 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=596.291..596.291 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.734 ms
Execution time: 91262.404 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=90944.102..90944.102 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=597.324..597.324 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.300 ms
Execution time: 90945.560 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97018.175..97018.175 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=645.782..645.782 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.976 ms
Execution time: 97019.504 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180330.061..180330.061 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=610.894..610.894 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.558 ms
Execution time: 180331.474 ms
(12 rows)
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71467.974..71467.974 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=778.875..778.875 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 42.094 ms
Execution time: 71469.484 ms
(12 rows)
Attachments:
[text/plain] test-before.txt (10.0K, ../c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com/2-test-before.txt)
download | inline:
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1454.570..1454.570 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=447.877..447.877 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.782 ms
Execution time: 1455.336 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=8364.272..8364.272 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=448.043..448.043 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.036 ms
Execution time: 8365.070 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4791.196..4791.196 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=433.928..433.928 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.847 ms
Execution time: 4791.961 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4112.928..4112.928 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.873..470.873 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.694 ms
Execution time: 4113.713 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1584.067..1584.067 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=451.587..451.587 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.421 ms
Execution time: 1584.862 ms
(11 rows)
effective_io_concurrency=16
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1532.343..1532.343 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=429.148..429.148 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.802 ms
Execution time: 1533.096 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1493.721..1493.721 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=427.864..427.864 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.565 ms
Execution time: 1494.494 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=3235.104..3235.104 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.009..435.009 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.289 ms
Execution time: 3235.892 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4623.565..4623.565 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=425.661..425.661 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.529 ms
Execution time: 4624.334 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=7830.530..7830.530 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.078..435.078 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.361 ms
Execution time: 7831.310 ms
(11 rows)
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1421.417..1421.417 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.542..470.542 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.907 ms
Execution time: 1422.203 ms
(11 rows)
[text/plain] test-after.txt (10.7K, ../c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com/3-test-after.txt)
download | inline:
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71320.201..71320.201 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.222..620.222 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 25.095 ms
Execution time: 71321.723 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180229.423..180229.423 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=626.718..626.718 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.123 ms
Execution time: 180230.742 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=98634.125..98634.125 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.905..620.905 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 22.936 ms
Execution time: 98635.566 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91463.095..91463.095 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=643.029..643.029 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.676 ms
Execution time: 91464.375 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91047.653..91047.653 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=614.786..614.786 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.338 ms
Execution time: 91048.939 ms
(12 rows)
effective_io_concurrency=16
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97680.861..97680.861 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=616.763..616.763 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.479 ms
Execution time: 97682.475 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91260.958..91260.958 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=596.291..596.291 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.734 ms
Execution time: 91262.404 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=90944.102..90944.102 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=597.324..597.324 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.300 ms
Execution time: 90945.560 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97018.175..97018.175 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=645.782..645.782 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.976 ms
Execution time: 97019.504 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180330.061..180330.061 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=610.894..610.894 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.558 ms
Execution time: 180331.474 ms
(12 rows)
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71467.974..71467.974 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=778.875..778.875 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 42.094 ms
Execution time: 71469.484 ms
(12 rows)
view thread (21+ messages) latest in thread
Message-ID: <c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com>
Permalink: ../c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com/
Also on: postgresql.org/message-id/c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vgarnashevich@gmail.com, klaussfreire@gmail.com, hzzhangjiazhi@corp.netease.com, gpd@gpdnet.co.uk, rottenwindfish@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: effective_io_concurrency on EBS/gp2
In-Reply-To: <c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox