From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | denis(at)edistar(dot)com |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgresql with max_connections=4096 |
Date: | 2005-07-27 07:52:52 |
Message-ID: | 42E73D54.10001@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Janning Vygen | 2005-07-27 08:01:23 | Re: Trigger disactivation and SELECT WAITING |
Previous Message | Riaan van der Westhuizen | 2005-07-27 07:48:24 | GUID for postgreSQL |