Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread Andy Colson
On 10/23/2013 04:28 PM, Jeff Janes wrote: My page response time is sub-second, and I run quite a few queries to build the page. But also, my server isn't to busy at the moment. The load is around 0.3 to 0.5 when its busy. Wasn't your question to figure out how to make sure things conti

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread Jeff Janes
On Tue, Oct 22, 2013 at 1:13 PM, andy wrote: > On 10/22/2013 2:18 PM, John R Pierce wrote: > >> On 10/22/2013 11:25 AM, andy wrote: >> >>> Hum.. I had not thought of that. My current setup uses 40 max >>> connections, and I don't think I've ever hit it. I use apache and >>> php, and my db conne

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread Jeff Janes
On Tue, Oct 22, 2013 at 12:18 PM, John R Pierce wrote: > On 10/22/2013 11:25 AM, andy wrote: > >> Hum.. I had not thought of that. My current setup uses 40 max >> connections, and I don't think I've ever hit it. I use apache and php, and >> my db connections are not persistent. >> > > that styl

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread Jeff Janes
On Tue, Oct 22, 2013 at 10:41 AM, andy wrote: > Hi all. > > My website is about to get a little more popular. I'm trying to add in > some measurements to determine an upper limit of how many concurrent > database connections I'm currently using. > > I've started running this: > > SELECT sum(numb

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread Marko Kreen
On Tue, Oct 22, 2013 at 09:45:24PM -0500, Andy Colson wrote: > On 10/22/2013 12:59 PM, Stephen Frost wrote: > >Andy, > > > >* andy (a...@squeakycode.net) wrote: > >>My website is about to get a little more popular. I'm trying to add > >>in some measurements to determine an upper limit of how many

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread David Kerr
On Wed, Oct 23, 2013 at 12:11:39PM -0500, andy wrote: - On 10/23/2013 11:07 AM, David Kerr wrote: - >On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote: - >- Hi all. - >- - >- My website is about to get a little more popular. I'm trying to add in - >- some measurements to determine an upper limi

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread andy
On 10/23/2013 11:07 AM, David Kerr wrote: On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote: - Hi all. - - My website is about to get a little more popular. I'm trying to add in - some measurements to determine an upper limit of how many concurrent - database connections I'm currently using.

Re: [GENERAL] Monitoring number of backends

2013-10-23 Thread David Kerr
On Tue, Oct 22, 2013 at 12:41:58PM -0500, andy wrote: - Hi all. - - My website is about to get a little more popular. I'm trying to add in - some measurements to determine an upper limit of how many concurrent - database connections I'm currently using. - - I've started running this: - - SELE

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread John R Pierce
On 10/22/2013 7:45 PM, Andy Colson wrote: Ahh, bummer, man. PgBouncer doesn't work so well when you have lots of databases. I have about 90 databases, the website could connect to any one of them on any request. (They are all about as equally likely to be hit) that scenario would better b

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Andy Colson
On 10/22/2013 12:59 PM, Stephen Frost wrote: Andy, * andy (a...@squeakycode.net) wrote: My website is about to get a little more popular. I'm trying to add in some measurements to determine an upper limit of how many concurrent database connections I'm currently using. PG is really *much* ha

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Stephen Frost
* John R Pierce (pie...@hogranch.com) wrote: > On 10/22/2013 1:13 PM, andy wrote: > >No, actually, I don't think my connect overhead is huge. My > >apache and postgres are on the same box, and it connects using > >unix socket. Perhaps if my apache on db were on different boxes it > >would be a pro

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread andy
On 10/22/2013 3:44 PM, Tom Lane wrote: andy writes: On 10/22/2013 2:18 PM, John R Pierce wrote: that style of php programming, you're getting some HUGE overhead in connect/disconnect per web page.putting pg_bouncer in the middle will make a HUGE improvement, possibly a second per page load

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread John R Pierce
On 10/22/2013 1:13 PM, andy wrote: No, actually, I don't think my connect overhead is huge. My apache and postgres are on the same box, and it connects using unix socket. Perhaps if my apache on db were on different boxes it would be a problem. each postgres connection, if you're not using a

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Tom Lane
andy writes: > On 10/22/2013 2:18 PM, John R Pierce wrote: >> that style of php programming, you're getting some HUGE overhead in >> connect/disconnect per web page.putting pg_bouncer in the middle >> will make a HUGE improvement, possibly a second per page load on a busy >> server. > No, act

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread andy
On 10/22/2013 2:18 PM, John R Pierce wrote: On 10/22/2013 11:25 AM, andy wrote: Hum.. I had not thought of that. My current setup uses 40 max connections, and I don't think I've ever hit it. I use apache and php, and my db connections are not persistent. that style of php programming, you're

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Stephen Frost
* John R Pierce (pie...@hogranch.com) wrote: > On 10/22/2013 10:59 AM, Stephen Frost wrote: > >PG is really*much* happier if you have only one backend per CPU in your > >system. The way to get there is by using a connection pooler like > >pg_bouncer and configuring it based on how many CPUs you h

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread John R Pierce
On 10/22/2013 11:25 AM, andy wrote: Hum.. I had not thought of that. My current setup uses 40 max connections, and I don't think I've ever hit it. I use apache and php, and my db connections are not persistent. that style of php programming, you're getting some HUGE overhead in connect/dis

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread John R Pierce
On 10/22/2013 10:59 AM, Stephen Frost wrote: PG is really*much* happier if you have only one backend per CPU in your system. The way to get there is by using a connection pooler like pg_bouncer and configuring it based on how many CPUs you have. Actually, I've found peak throughputs on a dece

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Stephen Frost
* andy (a...@squeakycode.net) wrote: > If I did plugin pg_bouncer, is it worth switching my php from > pg_connect to pg_pconnect? No, let pg_bouncer manage the connection pooling. Having two levels of pooling isn't a good idea (and pg_bouncer does a *much* better job of it anyway, imv..). > I'd

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread andy
On 10/22/2013 12:59 PM, Stephen Frost wrote: Andy, * andy (a...@squeakycode.net) wrote: My website is about to get a little more popular. I'm trying to add in some measurements to determine an upper limit of how many concurrent database connections I'm currently using. PG is really *much* ha

Re: [GENERAL] Monitoring number of backends

2013-10-22 Thread Stephen Frost
Andy, * andy (a...@squeakycode.net) wrote: > My website is about to get a little more popular. I'm trying to add > in some measurements to determine an upper limit of how many > concurrent database connections I'm currently using. PG is really *much* happier if you have only one backend per CPU

[GENERAL] Monitoring number of backends

2013-10-22 Thread andy
Hi all. My website is about to get a little more popular. I'm trying to add in some measurements to determine an upper limit of how many concurrent database connections I'm currently using. I've started running this: SELECT sum(numbackends) AS count, sum(xact_commit) as ttlcommit FROM pg_s