Thread: Postgresql with max_connections=4096

Postgresql with max_connections=4096

From
denis@edistar.com
Date:
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.

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

Could anyone have suggestions or tips for the postgres configuration and
the problem we're encountering?

The postgresql version is 7.4.8 on a Dual Xeon with 4Gb of Ram.
Apache frontends are Apache 1.3.33 with PHP 4.3.11.

Thank you in advance,

Denis Gasparin

Re: Postgresql with max_connections=4096

From
Richard Huxton
Date:
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.

> 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

Re: Postgresql with max_connections=4096

From
denis@edistar.com
Date:
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

Re: Postgresql with max_connections=4096

From
Roman Neuhauser
Date:
# denis@edistar.com / 2005-07-27 10:22:48 +0200:
> Richard Huxton wrote:
> >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.

    Not necessarily.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

Re: Postgresql with max_connections=4096

From
Jeff Trout
Date:
On Jul 27, 2005, at 4:22 AM, denis@edistar.com wrote:

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

no no no.
you don't want 4096 preforked clients.

What you want to do is leave it at the default 32 preforks and run a
copy of pgpool on each frontend.  Then point your app to connect ot
localhost (or the unix socket).  Be sure you change things to use a
"normal" connect not a persistant connect.  This should be fine for
you.   I used to run a site doing hundreds of connects & queries /
second across 4 frontends each of which ran a pg_pool connected to a
db box.

If you fine 32 clients isn't enough, then bump it up.  The idea is to
keep as few connections as possible.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Re: Postgresql with max_connections=4096

From
denis@edistar.com
Date:
I'm now testing with pg_pool installed on each apache frontend with 260
pg_pool preforked clients in each machine.

The database seems to work better. At least when it goes to swap it
doesn't stop working...

I also reduced the shared buffers and moved the pg_xlog folder to
another disk on another raid container.

I'll let go the test for all the night and tomorrow I'll let you know
the results...

Thank you for your help,

Denis

Jeff Trout wrote:

>
> On Jul 27, 2005, at 4:22 AM, denis@edistar.com wrote:
>
>> 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,
>
>
> no no no.
> you don't want 4096 preforked clients.
>
> What you want to do is leave it at the default 32 preforks and run a
> copy of pgpool on each frontend.  Then point your app to connect ot
> localhost (or the unix socket).  Be sure you change things to use a
> "normal" connect not a persistant connect.  This should be fine for
> you.   I used to run a site doing hundreds of connects & queries /
> second across 4 frontends each of which ran a pg_pool connected to a
> db box.
>
> If you fine 32 clients isn't enough, then bump it up.  The idea is to
> keep as few connections as possible.
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


Re: Postgresql with max_connections=4096

From
Jeff Trout
Date:
On Jul 27, 2005, at 10:46 AM, denis@edistar.com wrote:

> I'm now testing with pg_pool installed on each apache frontend with
> 260 pg_pool preforked clients in each machine.

Why did you pick 260?

You don't need a 1:1 ratio. That is the point of the pool. Those
connections are "shared".  Chances are extremely high that all your
apache clients are not issuing queries at the same exact time so your
queries end up getting funnelled into those X  connections.

I ran with 32 kids on pg_pool and 350 apache processes. never had a
problem.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Re: Postgresql with max_connections=4096

From
"Jim C. Nasby"
Date:
On Wed, Jul 27, 2005 at 04:46:56PM +0200, denis@edistar.com wrote:
> I'm now testing with pg_pool installed on each apache frontend with 260
> pg_pool preforked clients in each machine.
>
> The database seems to work better. At least when it goes to swap it
> doesn't stop working...

Wait, are you saying your database server is swapping? You'll never get
any kind of performance if that's the case.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Postgresql with max_connections=4096

From
Scott Marlowe
Date:
On Wed, 2005-07-27 at 12:53, Jim C. Nasby wrote:
> On Wed, Jul 27, 2005 at 04:46:56PM +0200, denis@edistar.com wrote:
> > I'm now testing with pg_pool installed on each apache frontend with 260
> > pg_pool preforked clients in each machine.
> >
> > The database seems to work better. At least when it goes to swap it
> > doesn't stop working...
>
> Wait, are you saying your database server is swapping? You'll never get
> any kind of performance if that's the case.

IF it swaps out unused code / data and leaves it, that's fine, but if
it's constantly swapping out then yeah, that's a VERY bad thing.

Re: Postgresql with max_connections=4096

From
denis@edistar.com
Date:
I picked 260 because 256 are the max clients in the apache configuration
file.
My problem is that every apache request need to make a connection to the
database, so if I have all the 256 apache processes working, I need at
least 256 pg_pool processes.

However, with the pg_pgpool installed in each machine with 260 preforked
clients, the test ended quite well. The postgresql server didn't loaded
two much and errors like "There is already a transaction in progress"
disappeared (I think that was a problem with the apache persistent
connections).

I encountered also other problems that I must to check. I think they are
caused by my application.

As soon as I have some results, I'll let you know.

Thank you,
Denis


Jeff Trout wrote:

>
> On Jul 27, 2005, at 10:46 AM, denis@edistar.com wrote:
>
>> I'm now testing with pg_pool installed on each apache frontend with
>> 260 pg_pool preforked clients in each machine.
>
>
> Why did you pick 260?
>
> You don't need a 1:1 ratio. That is the point of the pool. Those
> connections are "shared".  Chances are extremely high that all your
> apache clients are not issuing queries at the same exact time so your
> queries end up getting funnelled into those X  connections.
>
> I ran with 32 kids on pg_pool and 350 apache processes. never had a
> problem.
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>



Re: Postgresql with max_connections=4096

From
denis@edistar.com
Date:
With the last test I made, the database is swapping but in a very slow
way... 100K every 10 minutes and that seems to not be a problem... in
the sense that the server doesn't slow down...

Today I'll make other tests and let you know.

Thank you,
Denis


Scott Marlowe wrote:

>On Wed, 2005-07-27 at 12:53, Jim C. Nasby wrote:
>
>
>>On Wed, Jul 27, 2005 at 04:46:56PM +0200, denis@edistar.com wrote:
>>
>>
>>>I'm now testing with pg_pool installed on each apache frontend with 260
>>>pg_pool preforked clients in each machine.
>>>
>>>The database seems to work better. At least when it goes to swap it
>>>doesn't stop working...
>>>
>>>
>>Wait, are you saying your database server is swapping? You'll never get
>>any kind of performance if that's the case.
>>
>>
>
>IF it swaps out unused code / data and leaves it, that's fine, but if
>it's constantly swapping out then yeah, that's a VERY bad thing.
>
>
>