Re: improving write performance for logging

On Wed, 2006-01-04 at 09:29 -0500, Ron wrote:
> 2B is a lot of inserts. If you had to guess,
> what do you think is the maximum number of inserts you could do in a day?

It seems we are pushing it there. Our intentions are to scale much
further, but our plans are to distribute at this point.

> How large is each record being inserted?

They are small: 32 (data) bytes.

> How much can you put in a COPY and how many COPYs
> can you put into a transactions?

These are driven by the application; we do about 60 COPYs and a couple
dozen INSERT/UPDATE/DELETEs in a single transaction. Each COPY is
doing a variable number of rows, up to several hundred. We do 15 of
these transactions per second.

> What values are you using for bgwriter* and checkpoint*?

bgwriter is 100%/500 pages, and checkpoint is 50 segments/300 seconds.
wal_buffers doesn't do much for us, and fsync is enabled.

> What HW on you running on and what kind of performance do you typically get?

The WAL is a 2-spindle (SATA) RAID0 with its own controller (ext3).
The tables are on a 10-spindle (SCSI) RAID50 with dual U320
controllers (XFS). This is overkill for writing and querying the data,
but we need to constantly ANALYZE and VACUUM in the
background without interrupting the inserts (the app is 24x7). The
databases are 4TB, so these operations can be lengthy.

--
Ian Westmacott <ianw(at)intellivid(dot)com>
Intellivid Corp.