Re: Best practices for effective_io_concurrency

On Mon, 19 Oct 2009, Sergey Konoplev wrote:

> I feel it rater significant for PG performance and would like to ask
> gurus to provide some more description here.

It's probably not as significant as you are hoping. Currently the code
only kicks in when you're doing a Bitmap Heap Scan, which is really
helpful for them, but of no help for any other type of query. And I've
only seen it actually work at all on Linux. It might work on BSD and Mac
OS X systems, certainly doesn't do anything on Solaris and Windows.

> Also I've found some info in EnterpriseDB documentation
> (http://www.enterprisedb.com/docs/en/8.3R2/perf/Performance_Guide-16.htm).
> Is it all actual for PG8.4?

That's is almost all correct for 8.4. The settings mentioned there that
start with the name "edb" are not available to tweak
(edb_prefetch_indexscans and edb_enable_icach) in standard PostgreSQL, the
rest of it is good background and advice. The basic idea is that you
start with setting the value to the number of working drives in the disk
array the database is on and see if I/O performance goes up and/or query
speed drops afterwards. If it does you might try further increases beyond
that even.

As for why there isn't a better tuning guide than just those simple
guidelines, it's not that easy to show a situation where the type of
bitmap scan this parameter impacts is used on a generated data set, even
though it's not that uncommon in real-world data. It's hard both to make
generic suggestions here and to even demonstrate the feature at work.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD