Re: [HACKERS] effective_io_concurrency in 9.6beta
On Tue, May 24, 2016 at 11:34 AM, Alvaro Herrera wrote: > Tom Lane wrote: >> Jeff Janes writes: >> > commit 1aba62ec made zero be an illegal value for effective_io_concurrency. >> > i think this was an accident. If not, then the sample postgresql.conf >> > (at least) needs to be updated. >> >> It looks like the problem is that the new range check >> >> + /* This range check shouldn't fail, but let's be paranoid */ >> + return (new_prefetch_pages > 0.0 && new_prefetch_pages < (double) >> INT_MAX); >> >> should be testing for >= 0.0 not > 0.0. > > Hmm, yeah, it looks like that's it. Will fix. > Thanks, works as expected now. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency in 9.6beta
Tom Lane wrote: > Jeff Janes writes: > > commit 1aba62ec made zero be an illegal value for effective_io_concurrency. > > i think this was an accident. If not, then the sample postgresql.conf > > (at least) needs to be updated. > > It looks like the problem is that the new range check > > + /* This range check shouldn't fail, but let's be paranoid */ > + return (new_prefetch_pages > 0.0 && new_prefetch_pages < (double) > INT_MAX); > > should be testing for >= 0.0 not > 0.0. Hmm, yeah, it looks like that's it. Will fix. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency in 9.6beta
Jeff Janes writes: > commit 1aba62ec made zero be an illegal value for effective_io_concurrency. > i think this was an accident. If not, then the sample postgresql.conf > (at least) needs to be updated. It looks like the problem is that the new range check + /* This range check shouldn't fail, but let's be paranoid */ + return (new_prefetch_pages > 0.0 && new_prefetch_pages < (double) INT_MAX); should be testing for >= 0.0 not > 0.0. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] effective_io_concurrency in 9.6beta
commit 1aba62ec made zero be an illegal value for effective_io_concurrency. i think this was an accident. If not, then the sample postgresql.conf (at least) needs to be updated. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency documentation
On Mon, Sep 29, 2014 at 10:41 AM, Peter Eisentraut wrote: > I think this is a mistake. For example, we allow setting ssl to false > even if SSL support is not compiled in. +1 -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] effective_io_concurrency documentation
The documentation of effective_io_concurrency says
Asynchronous I/O depends on an effective posix_fadvise
function, which some operating systems lack. If the function is not
present then setting this parameter to anything but zero will result
in an error.
This is not correct, because on unsupported systems, it is an error to
set this parameter to anything, including zero, because it is set to
PGC_INTERNAL.
I think this is a mistake. For example, we allow setting ssl to false
even if SSL support is not compiled in.
I propose the attached patch to correct this.
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 9494439..0229f54 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1892,6 +1892,10 @@ Asynchronous Behavior
in an error. On some operating systems (e.g., Solaris), the function
is present but does not actually do anything.
+
+
+ The default is 1 on supported systems, otherwise 0.
+
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d208314..d438128 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2269,11 +2269,7 @@ static struct config_int ConfigureNamesInt[] =
{
{"effective_io_concurrency",
-#ifdef USE_PREFETCH
PGC_USERSET,
-#else
- PGC_INTERNAL,
-#endif
RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
gettext_noop("For RAID arrays, this should be approximately the number of drive spindles in the array.")
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index c8ff2cb..e6c9e48 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1288,6 +1288,12 @@ setup_config(void)
conflines = replace_token(conflines, "#dynamic_shared_memory_type = posix",
repltok);
+#if !USE_PREFETCH
+ conflines = replace_token(conflines,
+ "#effective_io_concurrency = 1",
+ "#effective_io_concurrency = 0");
+#endif
+
snprintf(path, sizeof(path), "%s/postgresql.conf", pg_data);
writefile(path, conflines);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency
On Thu, Aug 30, 2012 at 1:25 PM, Peter Geoghegan wrote: > Just how helpful is effective_io_concurrency? Did someone produce a > benchmark at some point? Attached is a benchmark I put together a while ago. I don't know how close to "real world" it might be. I haven't seen it in the wild, but I'm anticipating I will see something like it soon. The benefit is of course reduced if you were to apply high levels of -c $clients, but cases where -c 1 are frequent in data mining and such. Obviously it would be better to cluster the giant table on "abalance", but that might be hard to do and then hard to maintain. Size of generated database is about 70GB. Server had 16GB of RAM. It is a virtual server, and I don't know the real hardware, but am told it has 8 spindles. (if the range used in the query is increased from +100 to +1000, even more speed up is available) effective_io_concurrencytps 0 2.06273064 1 2.1693092 2 4.11726948 3 5.90785352 4 6.65748384 5 7.58297556 6 8.36130404 7 8.86561116 8 9.2673546 9 9.57076168 10 9.8558758 11 10.11641752 12 10.316673 13 10.46953468 14 10.65962516 15 10.76328636 16 10.86442376 17 10.96362168 18 11.04371008 19 11.19470171 20 11.30110867 21 11.39553967 22 11.45420263 23 11.54764725 24 11.61949146 25 11.65659225 26 11.68992392 27 11.75944667 28 11.7456135 29 11.80111779 30 11.72897188 31 11.7210945 32 11.73292504 33 11.734458 34 11.75195196 35 11.79079175 36 11.73687979 37 11.79583758 38 11.75879063 39 11.77868596 40 11.74685896 41 11.76294508 42 11.7213265 43 11.68458158 44 11.71036729 45 11.72728229 46 11.72063796 47 11.80322429 48 11.83563058 49 11.81916996 50 11.73395892 Cheers, Jeff bitmap.sql Description: Binary data bench.sh Description: Bourne shell script setup.sql Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency
On 30 August 2012 20:28, Robert Haas wrote: > On Sat, Jul 28, 2012 at 4:09 PM, Jeff Janes wrote: >> But it might be better yet to make ordinary index scans benefit from >> effective_io_concurrency, but even if/when that gets done it would >> probably still be worthwhile to make the planner understand the >> benefit. > > That sounds good too, but separate. Indeed. The original effective_io_concurrency commit message said: """ ***SNIP*** (The best way to handle this for plain index scans is still under debate, so that part is not applied yet --- tgl) """ ...seems like a pity that this debate never reached a useful conclusion. Just how helpful is effective_io_concurrency? Did someone produce a benchmark at some point? -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] effective_io_concurrency
On Sat, Jul 28, 2012 at 4:09 PM, Jeff Janes wrote: > From my attempted reading of the thread "posix_fadvise v22", it seems > like modification of the planner was never discussed, rather than > being discussed and rejected. So, is there a reason not to make the > planner take account of effective_io_concurrency? Not that I can see. > But it might be better yet to make ordinary index scans benefit from > effective_io_concurrency, but even if/when that gets done it would > probably still be worthwhile to make the planner understand the > benefit. That sounds good too, but separate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] effective_io_concurrency
The bitmap heap scan can benefit quite a bit from effective_io_concurrency on RAID system (and to some extent even on single spindle systems) However, the planner isn't aware of this. So you have to just be lucky to have it choose the bitmap heap scan instead of something else that can't benefit from effective_io_concurrency. As far as I can tell, the only thing that drives the bitmap heap scan down in cost is the estimation that you will end up getting multiple tuples from the same block. And because of the fuzzy in compare_path_costs_fuzzily, the estimate has to be 1% of redundant blocks before the bitmap scan will be considered, and I think the benefits of effective_io_concurrency can kick in well before that on very large data sets. Also, if there some correlation in the table, then the situation is worse because the index scan lowers its block-read estimates based on the correlation, while the bitmap scan does not lower its estimate. I haven't witnessed such a case, but it seems like there must be correlation levels small enough that most reading is still scattered, but large enough to make a difference in the cost estimates between the two competing access methods that favor the one that is not actually faster. >From my attempted reading of the thread "posix_fadvise v22", it seems like modification of the planner was never discussed, rather than being discussed and rejected. So, is there a reason not to make the planner take account of effective_io_concurrency? But it might be better yet to make ordinary index scans benefit from effective_io_concurrency, but even if/when that gets done it would probably still be worthwhile to make the planner understand the benefit. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
