Обсуждение: Postgresql with max_connections=4096
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
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
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
# 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
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/
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 >
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/
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?"
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.
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/ > >
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. > > >