Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
What kind of application do you have? How many database calls do you make and how long do they take, do you use persistent connections? It's hard to say without knowing the specifics. If you're not having some performance issues, then I think the default value is a safe starting point. –  Karol Piczak May 30 '11 at 17:59

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.