From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
Date: Thu, 3 Sep 2015 02:24:48 +0200
Message-ID: <20150903002448.GD17210@awork2.anarazel.de> (raw)
In-Reply-To: <55E78D51.9050004@2ndquadrant.com>
References: <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>
<20150902222329.GD8555@awork2.anarazel.de>
<55E78D51.9050004@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 2015-09-03 01:59:13 +0200, Tomas Vondra wrote:
> That's a bit surprising, especially considering that e_i_c=30 means ~100
> pages to prefetch if I'm doing the math right.
>
> AFAIK queue depth for SATA drives generally is 32 (so prefetching 100 pages
> should not make a difference), 256 for SAS drives and ~1000 for most current
> RAID controllers.
I think the point is that after an initial buildup - we'll again only
prefetch pages in smaller increments because we already prefetched most
pages. The actual number of concurrent reads will then be determined by
how fast pages are processed. A prefetch_target of a 100 does *not*
mean 100 requests are continously in flight. And even if it would, the
OS won't issue many more requests than the queue depth, so they'll just
sit in the OS queue.
> So instead of "How many blocks I need to prefetch to saturate the devices?"
> you're asking "How many blocks I need to prefetch to never actually wait for
> the I/O?"
Yes, pretty much.
> I do like this view, but I'm not really sure how could we determine the
> right value? It seems to be very dependent on hardware and workload.
Right.
> For spinning drives the speedup comes from optimizing random seeks to a more
> optimal path (thanks to NCQ/TCQ), and on SSDs thanks to using the parallel
> channels (and possibly faster access to the same block).
+ OS reordering & coalescing.
Don't forget that the OS processes the OS IO queues while the userland
process isn't scheduled - in a concurrent workload with more processes
than hardware threads that means that pending OS requests are being
issued while the query isn't actively being processed.
> I guess the best thing we could do at this level is simply keep the
> on-device queues fully saturated, no?
Well, being too aggressive can hurt throughput and latency of concurrent
processes, without being beneficial.
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, tomas.vondra@2ndquadrant.com, josh@agliodbs.com, pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
In-Reply-To: <20150903002448.GD17210@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