Re: Postgresql with max_connections=4096

denis(at)edistar(dot)com wrote:
> We have a postgresql server configured with max_connections=4096.
> We have such a high number of max_connections because there are 8 web
> servers connected to the database and all use persistent connections.
> Each web server can have 256 max clients and 2 connection strings, so
> the max connections to the db is 256 * 8 * 2=4096.

The one-word answer is "pgpool" http://pgpool.projects.postgresql.org/
You don't really want them all using persistent connections directly to
the DB.

> To start the postgresql, I must to do some kernel extra configuration:
>
> kernel.shmmax = 1165063808
> kernel.sem=512 64000 100 512
>
> and the parameters i changed other than max_connections in the
> postgresql.conf are:
>
> - shared_buffers = 131072

That's a *very* big shared_buffers value. Try reducing it by a factor of
10 and reading the "performance tuning" article here:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

> - checkpoint_segments = 32
>
> We are doing pre-production tests and we encountered the following
> problems:
> - The database server is generally low loaded except when the postgres
> recycles a transaction log file.

Your disk I/O is probably saturated. However, fix your configuration
settings before worrying about hardware.

> - This causes the apache frontends to slow down and to do all together
> the most heavy operation (that is five inserts in five different tables
> and a delete)
> - After some time the postgresql starts giving the message "WARNING:
> there is already a transaction in progress". It seems like the apache
> frontend didn't close correctly the previous connection living a
> transaction open but I'm not sure this is the only problem.

Sounds like a BEGIN being re-issued alright. Solution - fix your
application(s) and don't use persistent connections (or if you do, make
sure you rollback any pre-existing transactions and issue any relevant
SET commands).

--
Richard Huxton
Archonet Ltd