Thread: [PATCH] Don't bail with legitimate -N/-B options
Greetings,
Starting PostgreSQL 8.3.0 with the default options used by Gentoo Linux
(-N 40 -B 80) causes it to bail with an error message.
"the number of buffers (-B) must be at least twice the number of allowed
connections (-N) and at least 16"
The problem is that NBuffers is actually "max autovacuum connections" +
NBuffers.
My attached patch fixes this by adding "max autovacuum connections" * 2
to NBuffers before the check.
Best regards,
Andreas Kling
ACG Nyström AB
Index: src/backend/postmaster/postmaster.c
===================================================================
--- src/backend/postmaster/postmaster.c (revision 30129)
+++ src/backend/postmaster/postmaster.c (working copy)
@@ -685,6 +685,9 @@ PostmasterMain(int argc, char *argv[])
/* And switch working directory into it */
ChangeToDataDir();
+ /* Add buffers to accomodate backends "reserved" for autovacuum */
+ NBuffers += autovacuum_max_workers * 2;
+
/*
* Check for invalid combinations of GUC settings.
*/
Andreas Kling wrote: > Greetings, > > Starting PostgreSQL 8.3.0 with the default options used by Gentoo Linux > (-N 40 -B 80) causes it to bail with an error message. > > "the number of buffers (-B) must be at least twice the number of allowed > connections (-N) and at least 16" > > The problem is that NBuffers is actually "max autovacuum connections" + > NBuffers. > > My attached patch fixes this by adding "max autovacuum connections" * 2 > to NBuffers before the check. Anybody know *why* Gentoo does such a thing? Having shared buffers at the very lowest possible boundary just seems counterproductive. Plus, the normal way to set these things would be in postgresql.conf, why override them on the commandline? It's not the first time I've seen people complain about this, it'd be good to know why. Those are not comments on the actual patch, of course. For that one, it looks to me like it's the wrong fix. I don't think we should be adding to shared buffers like that - if somebody asked for a specific value they should get that. But in that case the error message needs to be changed, since it's misleading. //Magnus
Magnus Hagander wrote: > Anybody know *why* Gentoo does such a thing? Having shared buffers at > the very lowest possible boundary just seems counterproductive. Plus, > the normal way to set these things would be in postgresql.conf, why > override them on the commandline? > > It's not the first time I've seen people complain about this, it'd be > good to know why. It's been brought up on the Gentoo bugzilla (http://bugs.gentoo.org/show_bug.cgi?id=206725), so hopefully something will come of that. > Those are not comments on the actual patch, of course. For that one, > it looks to me like it's the wrong fix. I don't think we should be > adding to shared buffers like that - if somebody asked for a specific > value they should get that. But in that case the error message needs > to be changed, since it's misleading. If we follow that logic, there shouldn't be an error message at all. ;-) Cheers, Andreas
Andreas Kling wrote: > Magnus Hagander wrote: >> Anybody know *why* Gentoo does such a thing? Having shared buffers at >> the very lowest possible boundary just seems counterproductive. Plus, >> the normal way to set these things would be in postgresql.conf, why >> override them on the commandline? >> >> It's not the first time I've seen people complain about this, it'd be >> good to know why. > It's been brought up on the Gentoo bugzilla > (http://bugs.gentoo.org/show_bug.cgi?id=206725), so hopefully something > will come of that. That's good to see. I fully agree with the guy who wrote it and his comment "this was a huge surprise" :-) >> Those are not comments on the actual patch, of course. For that one, >> it looks to me like it's the wrong fix. I don't think we should be >> adding to shared buffers like that - if somebody asked for a specific >> value they should get that. But in that case the error message needs >> to be changed, since it's misleading. > If we follow that logic, there shouldn't be an error message at all. ;-) I think you misunderstand me. I don't mean he should actually get the number of buffers he asks for if it's invalid, of course. But that we shouldn't silently adjust the given parameter - we should tell the user that the given parameter are wrong, and how. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> Those are not comments on the actual patch, of course. For that one, it
> looks to me like it's the wrong fix. I don't think we should be adding
> to shared buffers like that - if somebody asked for a specific value
> they should get that.
Agreed, this is really inappropriate.
The whole test probably needs to be rethought. I think the idea
was to ensure that you couldn't run out of pinnable buffers if all
backends were concurrently doing UPDATEs that touched two pages. But
it's an underestimate --- for instance, if the UPDATE is doing an
indexscan then there's likely an index page pinned somewhere as well.
Worse, if you're joining N tables then there's likely N or N*2 pages
pinned at any instant.
Personally what I think we should do is intentionally break the current
Gentoo packaging --- we already unintentionally broke it, but changing
the code that those ridiculous parameter values are accepted again is
NOT the appropriate next step. I'd be for raising the minimum -B to a
couple hundred. I'm not sure if we should continue to enforce any
minimum -B-to-N ratio, but if we do, let's make sure that it's measured
without counting the autovac processes, so as to keep the error message
simple.
I think at the time we set the current minimum -B we were still
intending that you could run in a half meg or so SHMMAX allocation.
That's certainly history. Maybe we should target 2 meg as the rock
bottom minimum?
regards, tom lane
Tom Lane wrote: > I think at the time we set the current minimum -B we were still > intending that you could run in a half meg or so SHMMAX allocation. > That's certainly history. Maybe we should target 2 meg as the rock > bottom minimum? That makes sense to me. It corresponds to 128 connections under the old arithmetic, which seems reasonable all around. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> I think at the time we set the current minimum -B we were still
>> intending that you could run in a half meg or so SHMMAX allocation.
>> That's certainly history. Maybe we should target 2 meg as the rock
>> bottom minimum?
> That makes sense to me. It corresponds to 128 connections under the old
> arithmetic, which seems reasonable all around.
I did a bit of experimentation on a 64-bit machine (which is probably a
bit hungrier than a 32-bit machine for shared memory, but not enormously
so). With SHMMAX set to exactly 2MB, the smallest configuration that
initdb tries,
--max_connections=10 --shared_buffers=50 --max_fsm_pages=20000
fits with about 100K to spare. You can get to 100 buffers if you back
off the other two parameters even more, but there's no hope at all of
getting to 200.
At SHMMAX = 4MB you can get up to combinations like this
--max_connections=20 --shared_buffers=285 --max_fsm_pages=20000
--max_connections=10 --shared_buffers=314 --max_fsm_pages=20000
which still look pretty tiny.
There are a couple of things we could hope to do to make this a bit more
tolerable. One is to get rid of FSM in its current form, which was
already on my hit list for 8.4. That would buy about 180K to put back
into the buffer arena, given the FSM parameters used here. Another idea
that's been kicked around is merging the dedicated SLRU buffers into the
common buffer pool. Those are currently hardwired to 64 buffers, which
looks like a lot in this context. I'm still not sure about the
potential concurrency costs (or benefits?) of eliminating SLRU, but it
seems worth looking at, particularly seeing that the fixed allocation is
bad at the high end as well as the low end. Or we could keep the SLRU
areas separate but make their buffer counts tunable instead of fixed.
Anyway, it seems that we cannot raise the minimum value of
shared_buffers to 200, or even 100, unless we are prepared to blow off
SHMMAX = 2MB or do something about the fixed SLRU allocation. I'm
not sure whether 2MB is still an interesting number; is anyone aware
of systems that have that as the default SHMMAX? It seems possible
that targeting 4MB instead of 2MB as the minimum might not change
matters for anyone, in terms of whether they have to reconfigure their
kernel or not.
regards, tom lane
I wrote:
> Anyway, it seems that we cannot raise the minimum value of
> shared_buffers to 200, or even 100, unless we are prepared to blow off
> SHMMAX = 2MB or do something about the fixed SLRU allocation.
After further thought I propose that we just eliminate the interlock
between -N and -B. I don't think it's accomplishing anything very
useful (for sure it's not enough to guarantee that you can't run out of
pinnable buffers), and the addition of the autovac worker processes
makes the whole thing too confusing to be helpful.
I am still interested in a TODO of increasing the minimum allowed value
of shared_buffers, but that's really an independent issue. And we can't
do very much there anyway until after eliminating the fixed-size-SLRU-
buffers problem.
regards, tom lane
FYI, the restriction that -B must be larger than -N will be removed in 8.4. --------------------------------------------------------------------------- Andreas Kling wrote: > Greetings, > > Starting PostgreSQL 8.3.0 with the default options used by Gentoo Linux > (-N 40 -B 80) causes it to bail with an error message. > > "the number of buffers (-B) must be at least twice the number of allowed > connections (-N) and at least 16" > > The problem is that NBuffers is actually "max autovacuum connections" + > NBuffers. > > My attached patch fixes this by adding "max autovacuum connections" * 2 > to NBuffers before the check. > > Best regards, > Andreas Kling > ACG Nystr?m AB > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +