Re: [PERFORM] intel s3500 -- hot stuff

On 18/07/2015 12:03, Julien Rouhaud wrote:
> On 10/12/2014 17:52, Jeff Janes wrote:
>> On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us
>> <mailto:bruce@momjian.us>> wrote:
>>
>>     On Mon, Dec  8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
>>     > >> Did not see consistent measurable gains > 256
>>     > >> effective_io_concurrency.  Interesting that at setting of '2' (the
>>     > >> lowest possible setting with the feature actually working) is
>>     > >> pessimal.
>>     > >
>>     > > Very interesting.  When we added a per-tablespace random_page_cost,
>>     > > there was a suggestion that we might want to add per-tablespace
>>     > > effective_io_concurrency someday:
>>     >
>>     > What I'd really like to see is to have effective_io_concurrency work
>>     > on other types of scans.  It's clearly a barn burner on fast storage
>>     > and perhaps the default should be something other than '1'.  Spinning
>>     > storage is clearly dead and ssd seem to really benefit from the posix
>>     > readhead api.
>>
>>
>> I haven't played much with SSD, but effective_io_concurrency can be a
>> big win even on spinning disk.
>>
>>
>>
>>     Well, the real question is knowing which blocks to request before
>>     actually needing them.  With a bitmap scan, that is easy --- I am
>>     unclear how to do it for other scans.  We already have kernel read-ahead
>>     for sequential scans, and any index scan that hits multiple rows will
>>     probably already be using a bitmap heap scan.
>>
>>
>> If the index scan is used to provide ordering as well as selectivity
>> than it will resist being converted to an bitmap scan. Also it won't
>> convert to a bitmap scan solely to get credit for the use of
>> effective_io_concurrency, as that setting doesn't enter into planning
>> decisions.
>>
>> For a regular index scan, it should be easy to prefetch table blocks for
>> all the tuples that will need to be retrieved based on the current index
>> leaf page, for example.  Looking ahead across leaf page boundaries would
>> be harder.
>>
>
> I also think that having effective_io_concurrency for other nodes that
> bitmap scan would be really great, but for now
> having a per-tablespace effective_io_concurrency is simpler to implement
> and will already help, so here's a patch to implement it.  I'm also
> adding it to the next commitfest.
>

I didn't know that the thread must exists on -hackers to be able to add
a commitfest entry, so I transfer the thread here.

Sorry the double post.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org