Re: Postgresql with max_connections=4096

Richard Huxton wrote:

> denis@edistar.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.
>
Already checked.
We set a machine with only pg_pool installed as a fronted to the real db.
If I disable persistent connections and I use pg_pool with 4096
preforked clients, the load of the pg_pool server goes very high and the
takes down also the real database server. Maybe should I try installing
pg_pool direcly into the database server to avoid network traffic?

>> 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


Already read. I put 25% of total memory as that specified by Bruce
Momjian performance howto but now i'm lowering 65536

>
> 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).
>
If that was the problem, I should obtain always that error but I obtain
that error only after two/three hours of testing.

Thank you for your help,
Denis