Date: Thu, 3 Sep 2015 00:23:29 +0200
From: Andres Freund <andres@anarazel.de>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>, pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
Message-ID: <20150902222329.GD8555@awork2.anarazel.de>
References: 
 <CAHyXU0wgpE2E3B+rmZ959tJT_adPFfPvHNqeA9K9mkJRAT9HXw@mail.gmail.com>
 <20141209204337.GC24488@momjian.us>
 <CAMkU=1w0nXDcwhyQurh+XCdQ2AyeSLBcMGqrNAYsckLbTqq3mw@mail.gmail.com>
 <55AA2469.20306@dalibo.com> <55AA27C3.7060009@dalibo.com>
 <20150902135351.GA5286@alap3.anarazel.de>
 <CAM-w4HNFSi9HAvRXrvPhYf7awhzZq1ojOtcbK064VxC5U5t1jA@mail.gmail.com>
 <55E76950.2070403@2ndquadrant.com>
 <WM!3a593c803674048e94b1aebbd2d16ded211f5b8802ccc594ea824f30dbaeb070b2098ffa4bc26e2c2531662ed7cd7efc!@asav-1.01.com>
 <55E76AB7.4030306@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <55E76AB7.4030306@agliodbs.com>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 2015-09-02 14:31:35 -0700, Josh Berkus wrote:
> On 09/02/2015 02:25 PM, Tomas Vondra wrote:
> > 
> > As I explained, spindles have very little to do with it - you need
> > multiple I/O requests per device, to get the benefit. Sure, the DBAs
> > should know how many spindles they have and should be able to determine
> > optimal IO depth. But we actually say this in the docs:
> 
> My experience with performance tuning is that values above 3 have no
> real effect on how queries are executed.

I saw pretty much the opposite - the benefits seldomly were significant
below 30 or so. Even on single disks. Which actually isn't that
surprising - to be actually beneficial (that is, turn an IO into a CPU
bound workload) the prefetched buffer needs to actually have been read
in by the time its needed. In many queries processing a single heap page
takes far shorter than prefetching the data from storage, even if it's
on good SSDs.

Therefore what you actually need is a queue of prefetches for the next
XX buffers so that between starting a prefetch and actually needing the
buffer ienough time has passed that the data is completely read in.  And
the point is that that's the case even for a single rotating disk!

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers