Thread: Best practices for effective_io_concurrency
Hi, All I read documentation (http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html) and googled effective_io_concurrency but have not found any expanded explanation of what it actually is. I feel it rater significant for PG performance and would like to ask gurus to provide some more description here. It would be great if someone provide his experience. 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? Thank you. -- Regards, Sergey Konoplev
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@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, Oct 19, 2009 at 7:12 PM, Greg Smith <gsmith@gregsmith.com> wrote: > 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. Thanx for the details. > >> 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. Well wouldn't using loosy indexes be the reason to increase this parameter? And would it somehow affect (I mean negative) another queries which doesn't lead to Bitmap Heap Scans? -- Regards, Sergey Konoplev -- PostgreSQL articles in english & russian http://gray-hemp.blogspot.com/search/label/postgresql/