pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedScaling
4+ messages / 4 participants
[nested] [flat]
* Scaling
@ 2011-04-24 22:21 David Hornsby <hornsbydm2@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: David Hornsby @ 2011-04-24 22:21 UTC (permalink / raw)
To: pgsql-admin
My SMB database is currently running on a HP-UX box running in a master -
slave slonyI cluster to create a hot spare database. Recently the server has
be getting hammered and we are consistently hitting our max db connections.
We have our web CMS and our web apps, our desktop client apps, and a couple
application servers running off of this database server and it is crumbling
under the stress. Currently we have been doing table partitioning and table
archiving to trim the fat off of the indexing processes etc.
My project at hand is to upgrade this configuration to a linux cluster of
servers, 2 - 3. The ideal solution is to just do load balancing through DNS
requests and have 2 servers in a multi master configuration. So far we have
not had any hardware issues on our current set up ( knock on wood). So in
doing this I am not immediately looking for a hot spare set up. But in the
future am looking at setting up a VPN tunnel to an offsite server and
sending WAL updates to that.
Any and all comments are truly appreciated as well as any nudges in various
directions.
David.
--
David Hornsby
Beechglen Development Inc.
E-Mail: david@beechglen.com
Phone: 513 922 0509 x432
Fax: 513 347 2834
Web: beechglen.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scaling
@ 2011-04-24 23:25 Chris Ernst <cernst@zvelo.com>
parent: David Hornsby <hornsbydm2@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Chris Ernst @ 2011-04-24 23:25 UTC (permalink / raw)
To: pgsql-admin
On 04/24/2011 04:21 PM, David Hornsby wrote:
> My SMB database is currently running on a HP-UX box running in a master
> - slave slonyI cluster to create a hot spare database. Recently the
> server has be getting hammered and we are consistently hitting our max
> db connections. We have our web CMS and our web apps, our desktop client
> apps, and a couple application servers running off of this database
> server and it is crumbling under the stress. Currently we have been
> doing table partitioning and table archiving to trim the fat off of the
> indexing processes etc.
>
> My project at hand is to upgrade this configuration to a linux cluster
> of servers, 2 - 3. The ideal solution is to just do load balancing
> through DNS requests and have 2 servers in a multi master configuration.
> So far we have not had any hardware issues on our current set up ( knock
> on wood). So in doing this I am not immediately looking for a hot spare
> set up. But in the future am looking at setting up a VPN tunnel to an
> offsite server and sending WAL updates to that.
>
> Any and all comments are truly appreciated as well as any nudges in
> various directions.
I'm not aware of any production ready multi-master solution for
PostgreSQL. So I would not suggest perusing that at the moment.
If your primary problem is hitting your max db connections (and the
performance degradation that goes along with that), I would suggest
looking in to connection pooling. This will allow you to cap the number
of connections without just rejecting connections and will enable you to
dedicate more resources to each of the active connections. Ultimately,
your overall throughput should increase.
pgbouncer is very easy to set up and is great if you want simple
pooling. pgpool is is also a great pooler and a lot more (e.g. load
balancing, horizontal scaling, etc), but is a bit more complex to set up.
- Chris
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scaling
@ 2011-04-27 08:33 lst_hoe02@kwsoft.de
parent: Chris Ernst <cernst@zvelo.com>
0 siblings, 0 replies; 4+ messages in thread
From: lst_hoe02@kwsoft.de @ 2011-04-27 08:33 UTC (permalink / raw)
To: pgsql-admin
Zitat von Chris Ernst <cernst@zvelo.com>:
> On 04/24/2011 04:21 PM, David Hornsby wrote:
>> My SMB database is currently running on a HP-UX box running in a master
>> - slave slonyI cluster to create a hot spare database. Recently the
>> server has be getting hammered and we are consistently hitting our max
>> db connections. We have our web CMS and our web apps, our desktop client
>> apps, and a couple application servers running off of this database
>> server and it is crumbling under the stress. Currently we have been
>> doing table partitioning and table archiving to trim the fat off of the
>> indexing processes etc.
>>
>> My project at hand is to upgrade this configuration to a linux cluster
>> of servers, 2 - 3. The ideal solution is to just do load balancing
>> through DNS requests and have 2 servers in a multi master configuration.
>> So far we have not had any hardware issues on our current set up ( knock
>> on wood). So in doing this I am not immediately looking for a hot spare
>> set up. But in the future am looking at setting up a VPN tunnel to an
>> offsite server and sending WAL updates to that.
>>
>> Any and all comments are truly appreciated as well as any nudges in
>> various directions.
>
> I'm not aware of any production ready multi-master solution for
> PostgreSQL. So I would not suggest perusing that at the moment.
http://wiki.postgresql.org/wiki/Bucardo
But i have never used it so no idea how stable/mature it is.
Regards
Andreas
Attachments:
smime.p7s (application/pkcs7-signature, 5.9K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Scaling
@ 2011-05-12 21:04 Joshua D. Drake <jd@commandprompt.com>
parent: David Hornsby <hornsbydm2@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Joshua D. Drake @ 2011-05-12 21:04 UTC (permalink / raw)
To: pgsql-admin
On 04/24/2011 03:21 PM, David Hornsby wrote:
> My SMB database is currently running on a HP-UX box running in a master
> - slave slonyI cluster to create a hot spare database. Recently the
> server has be getting hammered and we are consistently hitting our max
> db connections. We have our web CMS and our web apps, our desktop client
> apps, and a couple application servers running off of this database
> server and it is crumbling under the stress. Currently we have been
> doing table partitioning and table archiving to trim the fat off of the
> indexing processes etc.
>
> My project at hand is to upgrade this configuration to a linux cluster
> of servers, 2 - 3. The ideal solution is to just do load balancing
> through DNS requests and have 2 servers in a multi master configuration.
> So far we have not had any hardware issues on our current set up ( knock
> on wood). So in doing this I am not immediately looking for a hot spare
> set up. But in the future am looking at setting up a VPN tunnel to an
> offsite server and sending WAL updates to that.
You are more likely to need one master, two hot standbys and push your
reads to the slaves.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-05-12 21:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-24 22:21 Scaling David Hornsby <hornsbydm2@gmail.com>
2011-04-24 23:25 ` Chris Ernst <cernst@zvelo.com>
2011-04-27 08:33 ` lst_hoe02@kwsoft.de
2011-05-12 21:04 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox