Message-ID: <55E78D51.9050004@2ndquadrant.com>
Date: Thu, 03 Sep 2015 01:59:13 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: Andres Freund <andres@anarazel.de>, Josh Berkus <josh@agliodbs.com>
CC: pgsql-hackers@postgresql.org
Subject: Re: Allow a per-tablespace effective_io_concurrency setting
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> <20150902222329.GD8555@awork2.anarazel.de>
In-Reply-To: <20150902222329.GD8555@awork2.anarazel.de>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


On 09/03/2015 12:23 AM, Andres Freund wrote:
> 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.

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'm not entirely surprised that values beyond 30 make a difference, but 
that you seldomly saw significant improvements below this value.

No doubt there are workloads like that, but I'd expect them to be quite 
rare and not prevalent as you're claiming.

> 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!

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?"

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.

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).

I guess the best thing we could do at this level is simply keep the 
on-device queues fully saturated, no?

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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