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

1 or 2 servers for large DB scenario.

From: David Brain <dbrain(at)bandwidth(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: 1 or 2 servers for large DB scenario.
Date: 2008-01-25 16:36:52
Message-ID: A97726DE-B9AA-470A-A111-32DB145CAD4A@bandwidth.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I'd appreciate some assistance in working through what would be the  
optimal configuration for the following situation.

We currently have one large DB (~1.2TB on disk), that essentially  
consists of 1 table with somewhere in the order of 500 million rows ,  
this database has daily inserts as well as being used for some semi- 
data mining type operations, so there are a fairly large number of  
indices on the table.  The hardware storing this DB (a software RAID6)  
array seems to be very IO bound for writes and this is restricting our  
insert performance to ~50TPS.

As we need to achieve significantly faster insert performance I have  
been considering splitting  the table into 'new' and 'old' data,  
making inserts into the 'new' table (which will also help as there are  
really 1 insert, an update and some selects involved in populating the  
table), then moving the data over to the 'old' DB on a periodic  
basis.  There would be new hardware involved, I'm thinking of HW RAID  
10 to improve the write performance.

The question really is, is it best to use two separate servers and  
databases (and have to come up with some copy process to move data  
from one to another), or to just add the faster storage hardware to  
the existing server and create a new tablespace for the 'new data'  
table on that hardware.  Doing this would enable us to potentially  
move data more easily from new to old (we can't use partitioning  
because there is some logic involved in when things would need to be  
moved to 'old').  Are there any global resources that make just adding  
the faster storage to the existing box a bad idea (the wal_log springs  
to mind - although that could be moved too), that would make adding an  
additional server instead a better idea?

Also are there any settings that might boost our TPS on the existing  
hardware (sync=off isn't an option.. (-: ).  I have already  
significantly increased the various buffers, but this was mainly to  
improve select performance?

Verson of  Postgresql is 8.2.3.

Thanks,

David.




Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2008-01-25 16:55:43
Subject: Re: 1 or 2 servers for large DB scenario.
Previous:From: Stephen DenneDate: 2008-01-25 10:14:40
Subject: Re: 8.3rc1 Out of memory when performing update

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