Should the database connection limit equal the number of processes connecting?
I noticed that PostgreSQL and MySQL have a 100 client connection limit by default. I'm wondering if I should turn this down since the webserver is on the same box and I only have about 20 PHP processes that would need to connect.
Should this setting match or exceed the number of processes that will try to connect?
Solution 1:
In PostgreSQL (I don't know MySQL) there is max_connections property defined as:
Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.
Increasing this parameter might cause PostgreSQL to request more System V shared memory or semaphores than your operating system's default configuration allows. See Section 17.4.1 for information on how to adjust those parameters, if necessary.
Effective limit of client connections is defined as:
max_connections - superuser_reserved_connections
Default value for superuser_reserved_connections is 3.
You need to take some perspective view. Today let's say 40 max_connections is safe for you and it keeps some OS resources free (semamphores and shared memory as described in documentation), but tomorrow it might be not enough:
psql: FATAL: sorry, too many clients already
Let's calculate what profit you get:
minSemaphoresSets = ceil((max_connections + autovacuum_max_workers)/16)
Default value for autovacuum_max_workers is 3, so:
prevSets = ceil((100 + 3)/16) = 7
newSets = ceil((40 + 3)/16) = 3
Each (Postgres) set has always 17 semaphores, so you have 68 semaphores safed:
ipcs -s | grep postgres
0x0052e2c1 589824 postgres 600 17
0x0052e2c2 622593 postgres 600 17
0x0052e2c3 655362 postgres 600 17
0x0052e2c4 688131 postgres 600 17
0x0052e2c5 720900 postgres 600 17
0x0052e2c6 753669 postgres 600 17
0x0052e2c7 786438 postgres 600 17
# changing max_connections from 100 to 40
pg_ctlcluster 8.3 main restart
ipcs -s | grep postgres
0x0052e2c1 819200 postgres 600 17
0x0052e2c2 851969 postgres 600 17
0x0052e2c3 884738 postgres 600 17
For shared memory it's ~ 1 MiB (look at Table 17-2 for more details):
ipcs -m | grep postgres
0x0052e2c1 0 postgres 600 29368320 4
# changing max_connections from 100 to 40
pg_ctlcluster 8.3 main restart
ipcs -m | grep postgres
0x0052e2c1 425984 postgres 600 28270592 4
As you see it's not too much, so you can use default limit if you don't need such optimization.