Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
Peter Eisentraut [EMAIL PROTECTED] 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=2 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=2 --max_connections=10 --shared_buffers=314 --max_fsm_pages=2 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] [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. */ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] [PATCH] Don't bail with legitimate -N/-B options
Magnus Hagander [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
