From: Andres Freund <andres@anarazel.de>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
Date: Thu, 3 Sep 2015 00:23:29 +0200
Message-ID: <20150902222329.GD8555@awork2.anarazel.de> (raw)
In-Reply-To: <55E76AB7.4030306@agliodbs.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
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: andres@anarazel.de, josh@agliodbs.com, tomas.vondra@2ndquadrant.com, pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
In-Reply-To: <20150902222329.GD8555@awork2.anarazel.de>
* 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