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(at)momjian(dot)us
>> <mailto:bruce(at)momjian(dot)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