Re: effective_io_concurrency on EBS/gp2

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com>
Cc: hzzhangjiazhi <hzzhangjiazhi(at)corp(dot)netease(dot)com>, Gary Doades <gpd(at)gpdnet(dot)co(dot)uk>, Rick Otten <rottenwindfish(at)gmail(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: effective_io_concurrency on EBS/gp2
Date: 2018-02-05 02:27:25
Message-ID: CAGTBQpZyXSxPuCFLBR3eg95=Lk3MexzaQdCGkuvH+vdKnN6gHg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Sat, Feb 3, 2018 at 8:05 PM, Vitaliy Garnashevich
<vgarnashevich(at)gmail(dot)com> wrote:
> 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

I have analyzed this issue quite extensively in the past, and I can
say with high confidence that you're analysis on point 2 is most
likely wrong.

Now, I don't have all the information to make that a categorical
assertion, you might have a point, but I believe you're
misinterpreting the data.

I mean, that the issue is indeed affected by the order of rows in the
table. Random heap access patterns result in sparse bitmap heap scans,
whereas less random heap access patterns result in denser bitmap heap
scans. Dense scans have large portions of contiguous fetches, a
pattern that is quite adversely affected by the current prefetch
mechanism in linux.

This analysis does point to the fact that I should probably revisit
this issue. There's a rather simple workaround for this, pg should
just avoid issuing prefetch orders for sequential block patterns,
since those are already much better handled by the kernel itself.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Vitaliy Garnashevich 2018-02-05 11:26:43 Re: effective_io_concurrency on EBS/gp2
Previous Message Rick Otten 2018-02-04 16:04:56 Re: postgresql 10.1 wrong plan in when using partitions bug