Yves Weißig wrote:
> I was wondering if there is more information about this switch in the
> configuration. Does it really work? Where in the source code can I
> follow how it works? "sgmgr.c" seems to be an entry point, but where
> exactly is it used?
>
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. This style of heap scan already knows in advance exactly what
blocks it needs from the database, and normally it just asks for them
one at a time. That can turn into a fair amount of random I/O, and it's
done serially: the next block isn't requested until the last one
arrives. What effective_io_concurrency does is advise the operating
system of the next few blocks the database is going to ask for, before
the actual read requests, in hopes that it might grab them if it happens
to be nearby that area of the disk.e
I've only seen this feature work at all on Linux. It might work on BSD
and Mac OS X systems, it certainly doesn't do anything on Solaris and
Windows.
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.
Moving up the source code chain from smgr,
src/backend/storage/buffer/bufmgr.c has PrefetchBuffer, and the one
place the executor calls that is BitmapHeapNext inside
src/backend/executor/nodeBitmapHeapscan.c
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books