Postgres unaware of it’s own connection limits?
Normally I’d just file a bug with something like this, but there’s no PostgreSQL bug tracker. So I’m left with a blog post …
I was doing some testing with PostgreSQL memory usage and needed to know what the maximum value of max_connections is. So naturally, I consulted the documentation. It stated that this was an integer, so again, I consulted documentation which stated that any value between -2147483648 and +2147483647 was valid.
Even though -2147483648 is clearly invalid, I wanted to try it. On restart, I got the following message in pgstartup.log:
FATAL: -2147483648 is outside the valid range for parameter "max_connections" (1 .. 8388607)
This seemed a whole lot more reasonable, so I tested the maximum and got the following in pgstartup.log:
FATAL: invalid value for parameter "max_connections": 8388607
No big deal, perhaps the numbers are not inclusive? I kept trying with reduced numbers, seeing the following in the log:
FATAL: invalid value for parameter "max_connections": 8388607 FATAL: invalid value for parameter "max_connections": 8388606 FATAL: invalid value for parameter "max_connections": 8388605 FATAL: invalid value for parameter "max_connections": 8388604
Until I had set max_connections to 8388603.
So, in the spirit of this being a bug report:
PostgreSQL Version:
postgres=# SELECT VERSION();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
(1 row)
How To Repeat:
Set max_connections=8388607 in postgresql.conf and restart postgresql
Suggested Fix:
Either fix the error message to say that the maximum value for max_connections is 8388603 or increase the maximum value for max_connections to 8388607 as stated in the documentation.
September 6th, 2012 at 12:05 am
There is no official bug repository, but there is a bug submission form and bug mailing list. It’s monitored quite frequently by the people that can investigate and fix such things
http://www.postgresql.org/support/submitbug/
http://archives.postgresql.org/pgsql-bugs/
September 6th, 2012 at 12:24 am
Can you even have that many? I thought 65535 was the theoretical limit since every backend needs its own connection.
September 6th, 2012 at 1:23 am
This issue comes from undocumented upper limit of max_connections and autovacuum_max_workers.
At start-up, PG checks that:
(max_connections + autovacuum_max_workers + 1) < 8388607
"8388607" is defined as MAX_BACKENDS in source and used as hard-limit of these GUC parameters (as you see in first error message), and "1" in the formula above is for extra autovacuum launcher process.
If you increase autovacuum_max_workers, PG would complain of same max_connections setting.
September 6th, 2012 at 3:30 am
It is interesting “PostgreSQL has no bug tracker” – which while theoretically right, doesn’t hide the fact that even on main postgresql.org page there is *working* link named “report a bug”.
As for the limit – you can’t have more than 65535 processes on most UNIXes I know, which means that after removing some for system processes, and 5-6 for Postgres background tasks – means you can have probably ~ 65400 connections max.
Of course it’s integer – there is no point in designing special datatype just to serve as value for max_connections.
Will you want to file a bug that “port” is also defined as integer, but it can’t handle values like 800000 ?
Also – please note that, according to documentation – you need shared_buffers to handle connections, which means that specific limit on value might be related to how much shared_buffers you have configured.
September 6th, 2012 at 7:41 am
I believe the key is in the first link:
“but might be less if your kernel settings will not support it (as determined during initdb).”
Although it’s unclear because it’s in the context of the default setting, the overall setting has to be influenced by how much memory is available and similar considerations. The limit you found is 2**23-5, i.e., about 8 meg. A doc bug is reasonable.
September 6th, 2012 at 12:15 pm
@Joel – I’m pretty sure you’re correct that 65535 is the theoretical limit (with something slightly lower than that being the practical limit). I only really need ~1000 connections, but when I saw the limits in pgstartup.log (1 .. 8388607), I had to start testing
September 6th, 2012 at 2:16 pm
The maximum value of max_connections depends on a complicated enough formula that the documentation doesn’t try to explain it. You can find out what the computation came up with on a particular system easily enough though:
psql -c “select min_val,max_val from pg_settings where name=’max_connections’”
On my server 536870911 is the number I get back, which gives you an idea how big the server to server drift here is.
It sounds like on your system, the formula itself is off by a few integers, so that it can’t really be set at high as computed. That is a bug, but not one that is worth fixing. Real-world maximum settings for max_connections are so much lower than this that the error here is annoying but not a functional issue.
September 6th, 2012 at 9:52 pm
@Shigeru – Thanks for that explanation, that clears some things up.
postgres=# select name, min_val, max_val from pg_settings where name in (‘autovacuum_max_workers’,'max_connections’);
name | min_val | max_val
————————+———+———
autovacuum_max_workers | 1 | 8388607
max_connections | 1 | 8388607
They both have the same min/max values. However, when (max_connections+autovacuum_max_workers) > 8388607 because of the autovacuum_max_workers, I get the following in pgstartup.log:
FATAL: invalid value for parameter “autovacuum_max_workers”: 8388600
I’m a bit confused, though, how the error message is chosen (i.e. whether max_connections or autovacuum_max_workers has the invalid value). And setting them both to 4194303 (half of the max value) just tells me I need more memory … time to start testing this on the big box with 2TB RAM