Re: improving write performance for logging - Mailing list pgsql-performance

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?

How large is each record being inserted?

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

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

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

Inquiring minds definitely want to know ;-)
Ron


At 08:54 AM 1/4/2006, Ian Westmacott wrote:
>We have a similar application thats doing upwards of 2B inserts
>per day.  We have spent a lot of time optimizing this, and found the
>following to be most beneficial:
>
>1)  use COPY (BINARY if possible)
>2)  don't use triggers or foreign keys
>3)  put WAL and tables on different spindles (channels if possible)
>4)  put as much as you can in each COPY, and put as many COPYs as
>     you can in a single transaction.
>5)  watch out for XID wraparound
>6)  tune checkpoint* and bgwriter* parameters for your I/O system
>
>On Tue, 2006-01-03 at 16:44 -0700, Steve Eckmann wrote:
> > I have questions about how to improve the
> write performance of PostgreSQL for logging
> data from a real-time simulation. We found that
> MySQL 4.1.3 could log about 1480 objects/second
> using MyISAM tables or about 1225
> objects/second using InnoDB tables, but
> PostgreSQL 8.0.3 could log only about 540
> objects/second. (test system: quad-Itanium2,
> 8GB memory, SCSI RAID, GigE connection from
> simulation server, nothing running except
> system processes and database system under test)
> >
> > We also found that we could improve MySQL
> performance significantly using MySQL's
> "INSERT" command extension allowing multiple
> value-list tuples in a single command; the rate
> for MyISAM tables improved to about 2600
> objects/second. PostgreSQL doesn't support that
> language extension. Using the COPY command
> instead of INSERT might help, but since rows
> are being generated on the fly, I don't see how
> to use COPY without running a separate process
> that reads rows from the application and uses
> COPY to write to the database. The application
> currently has two processes: the simulation and
> a data collector that reads events from the sim
> (queued in shared memory) and writes them as
> rows to the database, buffering as needed to
> avoid lost data during periods of high
> activity. To use COPY I think we would have to
> split our data collector into two processes communicating via a pipe.
> >
> > Query performance is not an issue: we found
> that when suitable indexes are added PostgreSQL
> is fast enough on the kinds of queries our
> users make. The crux is writing rows to the
> database fast enough to keep up with the simulation.
> >
> > Are there general guidelines for tuning the
> PostgreSQL server for this kind of application?
> The suggestions I've found include disabling
> fsync (done), increasing the value of
> wal_buffers, and moving the WAL to a different
> disk, but these aren't likely to produce the 3x
> improvement that we need. On the client side
> I've found only two suggestions: disable
> autocommit and use COPY instead of INSERT. I
> think I've effectively disabled autocommit by
> batching up to several hundred INSERT commands
> in each PQexec() call, and it isn’t clear
> that COPY is worth the effort in our application.
> >
> > Thanks.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
>--
>Ian Westmacott <ianw@intellivid.com>
>Intellivid Corp.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org