Re: intel s3500 -- hot stuff

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.

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