Home / server / Questions / 275287
Accepted
Xeoncross
Asked: 2011-05-31 09:22:20 +0800 CST

Should the database connection limit equal the number of processes connecting?

  • 772

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?

connection mysql database postgresql

1 Answers

  1. Best Answer

    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.

    • 4