Date: Tue, 6 Feb 2018 23:42:27 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Vitaliy Garnashevich <vgarnashevich@gmail.com>,
	Gary Doades <gpd@gpdnet.co.uk>,
	Rick Otten <rottenwindfish@gmail.com>,
	pgsql-performance@lists.postgresql.org,
	hzzhangjiazhi <hzzhangjiazhi@corp.netease.com>
Subject: Re: effective_io_concurrency on EBS/gp2
Message-ID: <20180207054227.GE17521@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAGTBQpZyXSxPuCFLBR3eg95=Lk3MexzaQdCGkuvH+vdKnN6gHg@mail.gmail.com>
 <CAGTBQpZjj1ekDLyZ=RdyPhKAbLnow1ma66TDuBj=eradrgOskQ@mail.gmail.com>
 <CAGTBQpYCWStnzsjTrN+vBsEwZHH2PKwO7ybxp_JQv9CsEJj4xw@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Wed, Jan 31, 2018 at 04:34:18PM -0300, Claudio Freire wrote:
> In my experience playing with prefetch, e_i_c>0 interferes with kernel
> read-ahead. What you've got there would make sense if what postgres
> thinks will be random I/O ends up being sequential. With e_i_c=0, the
> kernel will optimize the hell out of it, because it's a predictable
> pattern. But with e_i_c=1, the kernel's optimization gets disabled but
> postgres isn't reading much ahead, so you get the worst possible case.

On Thu, Feb 01, 2018 at 03:39:07PM -0300, Claudio Freire wrote:
> 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.

On Sun, Feb 04, 2018 at 11:27:25PM -0300, Claudio Freire wrote:
> ... Dense scans have large portions of contiguous fetches, a pattern that is
> quite adversely affected by the current prefetch mechanism in linux.
> 
> ... 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.

Thinking out loud.. if prefetch were a separate process, I imagine this
wouldn't be an issue ; is it possible the parallel worker code could take on
responsibility of prefetching (?)

Justin