Skip site navigation (1) Skip section navigation (2)

Re: Master/Slave, DB separation or just spend $$$?

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Master/Slave, DB separation or just spend $$$?
Date: 2009-07-22 16:30:49
Message-ID: 3d6c244be1c95471329963bbf8544cd1@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----                                  
Hash: RIPEMD160                                                     


> I have a db which is being constantly updated and queried by a few
> computers. We are doing datamining. The machine is running on a   
> moderately powered machine and processors constantly hit 90%.     
...                                                                 
> 2) Do a master-slave configuration                                
> 3) Separate the DB into 2 - One for pure mining purposes, the other
> purely for web serving                                             

Why not combine the two (if I'm understanding correctly)? Use Bucardo
or Slony to make two slaves, one for the web servers to hit (assuming
they are read-only queries), and one to act as a data warehouse.
Your main box gets all the updates but has no selects or complex
queries to weigh it down. If the we server does read and write, have
your app maintain two database handles.

> For (2), I do not know if it will be very effective since the master
> will probably have many changes at any moment. I do not understand how
> the changes will be propagated from the master to the slave without
> impacting the slave's performance. Anyone with more experience here?

The slave will get the updates as well, but in a more efficient manner
as there will be no WHERE clauses or other logic associated with the
original update. Bucardo or Slony will simply COPY over the rows as
needed. Keep in mind that both are asynchronous, so changes won't appear
on the slaves at the same time as the master, but the delay is typically
measured in seconds.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200907221229
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkpnPpsACgkQvJuQZxSWSsggKgCfT0EbxWQdym30n7IV1J1X6dC6
HRkAoND4nCMVeffE2VW34VVmPcRtLclI
=tTjn
-----END PGP SIGNATURE-----



In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2009-07-22 17:59:44
Subject: Re: Atomic access to large arrays
Previous:From: Chris BrowneDate: 2009-07-22 16:25:20
Subject: Re: Master/Slave, DB separation or just spend $$$?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group