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: Sun, 4 Feb 2018 01:05:05 +0200
Message-ID: <85a21d95-a776-4c1c-3223-5568864df3f3@gmail.com> (raw)
In-Reply-To: <c8b0b311-c436-622a-c5fa-96718ead36a0@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>
> Problem is, if you're scanning a highly correlated index, the
> mechanism is counterproductive.
> I would not expect this to make much of a difference on a table which
> is perfectly correlated with the index. You would have to create an
> accounts table which is randomly ordered to have a meaningful
> benchmark of the eic parameter.
If I read the postgres source code correctly, then the pages are sorted
in tbm_begin_iterate() before being iterated, so I don't think
correlation of index should matter. The tests on shuffled records show
the same trend in execution time for different eic values.
I did some more tests, this time on DigitalOcean/SSD. I also tried
different kernel versions (3.13 and 4.4). I've run each test several times.
Ubuntu 16.04.3 LTS
Linux ubuntu-s-2vcpu-4gb-ams3-01 4.4.0-112-generic #135-Ubuntu SMP Fri
Jan 19 11:48:36 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
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
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
------------------------------------------------------
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
(1 row)
effective_io_concurrency=0 Execution time: 3910.770 ms
effective_io_concurrency=1 Execution time: 10754.483 ms
effective_io_concurrency=2 Execution time: 5347.845 ms
effective_io_concurrency=4 Execution time: 5737.166 ms
effective_io_concurrency=8 Execution time: 4904.962 ms
effective_io_concurrency=16 Execution time: 4947.941 ms
effective_io_concurrency=8 Execution time: 4737.117 ms
effective_io_concurrency=4 Execution time: 4749.065 ms
effective_io_concurrency=2 Execution time: 5031.390 ms
effective_io_concurrency=1 Execution time: 10117.927 ms
effective_io_concurrency=0 Execution time: 3769.260 ms
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{14845391,12121312,18579380,9075771,7602183,762831,8485877,1035607,4451695,4686093,1925254,3462677,9634221,14144638,17894662,8247722,17996891,14842493,13832379,2052647}
(1 row)
effective_io_concurrency=0 Execution time: 6801.229 ms
effective_io_concurrency=1 Execution time: 14217.719 ms
effective_io_concurrency=2 Execution time: 9126.216 ms
effective_io_concurrency=4 Execution time: 8797.717 ms
effective_io_concurrency=8 Execution time: 8759.317 ms
effective_io_concurrency=16 Execution time: 8431.835 ms
effective_io_concurrency=8 Execution time: 9387.119 ms
effective_io_concurrency=4 Execution time: 9064.808 ms
effective_io_concurrency=2 Execution time: 9359.062 ms
effective_io_concurrency=1 Execution time: 16639.386 ms
effective_io_concurrency=0 Execution time: 6560.935 ms
Ubuntu 14.04.5 LTS
Linux ubuntu-s-2vcpu-4gb-ams3-02 3.13.0-139-generic #188-Ubuntu SMP Tue
Jan 9 14:43:09 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
------------------------------------------------------
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
(1 row)
effective_io_concurrency=0 Execution time: 3760.865 ms
effective_io_concurrency=1 Execution time: 11092.846 ms
effective_io_concurrency=2 Execution time: 4933.662 ms
effective_io_concurrency=4 Execution time: 4733.713 ms
effective_io_concurrency=8 Execution time: 4860.886 ms
effective_io_concurrency=16 Execution time: 5063.696 ms
effective_io_concurrency=8 Execution time: 4670.155 ms
effective_io_concurrency=4 Execution time: 5049.901 ms
effective_io_concurrency=2 Execution time: 4785.219 ms
effective_io_concurrency=1 Execution time: 11106.143 ms
effective_io_concurrency=0 Execution time: 3779.058 ms
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{8089611,788082,3477731,10034640,9256860,15432349,2412452,10087114,10386959,7199759,17253672,7798185,160908,1960920,13287370,14970792,18578221,13892448,3532901,3560583}
(1 row)
effective_io_concurrency=0 Execution time: 6243.600 ms
effective_io_concurrency=1 Execution time: 14613.348 ms
effective_io_concurrency=2 Execution time: 8250.552 ms
effective_io_concurrency=4 Execution time: 8286.333 ms
effective_io_concurrency=8 Execution time: 8167.817 ms
effective_io_concurrency=16 Execution time: 8193.186 ms
effective_io_concurrency=8 Execution time: 8206.614 ms
effective_io_concurrency=4 Execution time: 8375.153 ms
effective_io_concurrency=2 Execution time: 8354.106 ms
effective_io_concurrency=1 Execution time: 14139.712 ms
effective_io_concurrency=0 Execution time: 6409.229 ms
Looks like this behavior is not caused by, and does not depend on:
- variable performance in the cloud
- order of rows in the table
- whether the disk is EBS (backed by SSD or HDD), or ordinary SSD
- kernel version
Does this mean that the default setting for eic on Linux is just
inadequate for how the modern kernels behave? Or am I missing something
else in the tests?
Regards,
Vitaliy
Attachments:
[application/octet-stream] test-digitalocean-ubuntu14.zip (8.4K, ../85a21d95-a776-4c1c-3223-5568864df3f3@gmail.com/2-test-digitalocean-ubuntu14.zip)
download
[application/octet-stream] test-digitalocean-ubuntu16.zip (8.1K, ../85a21d95-a776-4c1c-3223-5568864df3f3@gmail.com/3-test-digitalocean-ubuntu16.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: <85a21d95-a776-4c1c-3223-5568864df3f3@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