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

Re: 1 or 2 servers for large DB scenario.

From: "Jignesh K(dot) Shah" <J(dot)K(dot)Shah(at)Sun(dot)COM>
To: David Brain <dbrain(at)bandwidth(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 1 or 2 servers for large DB scenario.
Date: 2008-01-28 00:53:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hi David,

I have been running few tests with 8.2.4 and here is what I have seen:

If fysnc=off is not an option (and it should not be an option :-) )
then commit_delay=10 setting seems to help a lot in my OLTP runs. 
Granted it will delay your transactions a bit, but the gain is big 
considering the WAL writes end up doing bigger writes under high load 
and got a good boost in performance due to that change (IIRC it was 
about 6-10% depending on load and contention). So that might help out.

Curiosly I did spend why it helps out on write contention. Atleast on 
Solaris my observation is WAL logs then end up getting bigger than 8K 
(Blocksize). This meant an overall reduction in IOPS on the filesystem 
thats holding the logs and hence more IOPS capacity available to do do 
more Log writes. (Using EAStress type of benchmark, it ended up doing 
somewhere between 128K-256KB writes on the logs which was pretty 
fascinating since the benchmark does drive fair amount of WAL writes and 
without commit_delay, the disks were pretty saturated quickly.

Also if the load is high, then the delay in transaction is pretty much 
non existent. (atleast what I observed with commit_delay=10 and 
commit_siblings left to default)

Of course as already replied back, 8.3's async commit helps on top of 
commit_delay so thats an option if few transactions loss potential is 


David Brain wrote:
> 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.
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-01-28 04:16:20
Subject: Re: 8.3rc1 Out of memory when performing update
Previous:From: Greg SmithDate: 2008-01-28 00:08:51
Subject: Re: Postgres 8.2 memory weirdness

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