Re: 1 or 2 servers for large DB scenario.

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
acceptable.

-Jignesh

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