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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
