From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: 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: Thu, 8 Feb 2018 18:40:12 +0200
Message-ID: <364c0960-fd84-da6d-ed57-51ef268f628a@gmail.com> (raw)
In-Reply-To: <4948d2de-33c1-1c98-df9e-8b5541cd7eee@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>
<c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com>
<85a21d95-a776-4c1c-3223-5568864df3f3@gmail.com>
<CAGTBQpZyXSxPuCFLBR3eg95=Lk3MexzaQdCGkuvH+vdKnN6gHg@mail.gmail.com>
<7895b3f0-0871-2b04-d3a3-3ade175e7212@gmail.com>
<CAGTBQpaOsC4=hwtO-ZkE9XVuUS2DqfiLw3-Zf_tjj1CfT49fjw@mail.gmail.com>
<4948d2de-33c1-1c98-df9e-8b5541cd7eee@gmail.com>
Anyway, there are still some strange things happening when
effective_io_concurrency is non-zero.
I've found that the real reason for the poor Bitmap Scan performance was
related not only with sparsity of the rows/pages to be rechecked, but
also with the value of starting ID from which the scan begins:
create table test as select generate_series(1, 100000) id, repeat('x',
90) val;
alter table test add constraint test_pkey primary key (id);
select count(*) tup_per_page from test group by (ctid::text::point)[0]
order by count(*) desc limit 5;
tup_per_page
--------------
65
65
65
65
65
(5 rows)
select * from test where id between X and 100000 and val != ''
effective_io_concurrency=0; id between 0 and 100000; Execution time:
524.671 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time:
420.000 ms
effective_io_concurrency=0; id between 0 and 100000; Execution time:
441.813 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time:
498.591 ms
effective_io_concurrency=0; id between 0 and 100000; Execution time:
662.838 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time:
431.503 ms
effective_io_concurrency=0; id between 10 and 100000; Execution time:
1210.436 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time:
1056.646 ms
effective_io_concurrency=0; id between 10 and 100000; Execution time:
578.102 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time:
396.996 ms
effective_io_concurrency=0; id between 10 and 100000; Execution time:
598.842 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time:
555.258 ms
effective_io_concurrency=0; id between 50 and 100000; Execution time:
4017.999 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time:
383.694 ms
effective_io_concurrency=0; id between 50 and 100000; Execution time:
535.686 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time:
570.221 ms
effective_io_concurrency=0; id between 50 and 100000; Execution time:
852.960 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time:
656.097 ms
effective_io_concurrency=0; id between 64 and 100000; Execution time:
385.628 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time:
712.261 ms
effective_io_concurrency=0; id between 64 and 100000; Execution time:
1610.618 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time:
438.211 ms
effective_io_concurrency=0; id between 64 and 100000; Execution time:
393.341 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time:
744.768 ms
effective_io_concurrency=0; id between 65 and 100000; Execution time:
846.759 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time:
514.668 ms
effective_io_concurrency=0; id between 65 and 100000; Execution time:
536.640 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time:
461.966 ms
effective_io_concurrency=0; id between 65 and 100000; Execution time:
1810.677 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time:
545.359 ms
effective_io_concurrency=0; id between 66 and 100000; Execution time:
663.920 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time:
5571.118 ms
effective_io_concurrency=0; id between 66 and 100000; Execution time:
683.056 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time:
5883.359 ms
effective_io_concurrency=0; id between 66 and 100000; Execution time:
472.809 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time:
5461.794 ms
effective_io_concurrency=0; id between 100 and 100000; Execution time:
647.292 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time:
7810.344 ms
effective_io_concurrency=0; id between 100 and 100000; Execution time:
773.750 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time:
5637.014 ms
effective_io_concurrency=0; id between 100 and 100000; Execution time:
726.111 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time:
7740.607 ms
effective_io_concurrency=0; id between 200 and 100000; Execution time:
549.281 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time:
5032.522 ms
effective_io_concurrency=0; id between 200 and 100000; Execution time:
692.631 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time:
5138.669 ms
effective_io_concurrency=0; id between 200 and 100000; Execution time:
793.342 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time:
5375.822 ms
effective_io_concurrency=0; id between 1000 and 100000; Execution time:
596.754 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time:
5278.683 ms
effective_io_concurrency=0; id between 1000 and 100000; Execution time:
638.706 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time:
5404.002 ms
effective_io_concurrency=0; id between 1000 and 100000; Execution time:
730.667 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time:
5761.312 ms
effective_io_concurrency=0; id between 2000 and 100000; Execution time:
656.086 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time:
6156.003 ms
effective_io_concurrency=0; id between 2000 and 100000; Execution time:
768.288 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time:
4917.423 ms
effective_io_concurrency=0; id between 2000 and 100000; Execution time:
500.931 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time:
5659.255 ms
effective_io_concurrency=0; id between 5000 and 100000; Execution time:
755.440 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time:
5141.671 ms
effective_io_concurrency=0; id between 5000 and 100000; Execution time:
542.174 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time:
6074.953 ms
effective_io_concurrency=0; id between 5000 and 100000; Execution time:
570.615 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time:
6922.402 ms
effective_io_concurrency=0; id between 10000 and 100000; Execution time:
469.544 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time:
6083.361 ms
effective_io_concurrency=0; id between 10000 and 100000; Execution time:
706.078 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time:
4069.171 ms
effective_io_concurrency=0; id between 10000 and 100000; Execution time:
526.792 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time:
5289.984 ms
effective_io_concurrency=0; id between 20000 and 100000; Execution time:
435.503 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time:
5460.730 ms
effective_io_concurrency=0; id between 20000 and 100000; Execution time:
454.323 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time:
4163.030 ms
effective_io_concurrency=0; id between 20000 and 100000; Execution time:
674.382 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time:
3703.045 ms
effective_io_concurrency=0; id between 50000 and 100000; Execution time:
226.094 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time:
2584.720 ms
effective_io_concurrency=0; id between 50000 and 100000; Execution time:
1431.037 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time:
2651.834 ms
effective_io_concurrency=0; id between 50000 and 100000; Execution time:
345.194 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time:
2328.844 ms
effective_io_concurrency=0; id between 75000 and 100000; Execution time:
120.121 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time:
2125.927 ms
effective_io_concurrency=0; id between 75000 and 100000; Execution time:
115.865 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time:
1616.534 ms
effective_io_concurrency=0; id between 75000 and 100000; Execution time:
138.005 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time:
1651.880 ms
effective_io_concurrency=0; id between 90000 and 100000; Execution time:
66.322 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time:
443.317 ms
effective_io_concurrency=0; id between 90000 and 100000; Execution time:
53.138 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time:
566.945 ms
effective_io_concurrency=0; id between 90000 and 100000; Execution time:
57.441 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time:
525.749 ms
For some reason, with dense bitmap scans, when Bitmap Heap Scan /
Recheck starts not from the first page of the table, the
effective_io_concurrency=0 consistently and significantly outperforms
effective_io_concurrency=1.
Regards,
Vitaliy
Attachments:
[application/octet-stream] starting-id-test.zip (14.2K, ../364c0960-fd84-da6d-ed57-51ef268f628a@gmail.com/2-starting-id-test.zip)
download
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: <364c0960-fd84-da6d-ed57-51ef268f628a@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