Steve Eckmann <eckmann@computer.org> 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)
>
> 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.
>
> Are there general guidelines for tuning the PostgreSQL server for this
> kind of application?

First suggestion: use transactions to batch your inserts together and
use large batches - write a few hundred rows per transaction. That
should speed things up seriously. I also suggest you test the effects of
using "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" - in my application
it resulted in a further speedup.

I'm storing syslog and eventlog in a PostgreSQL database and using flat
tables I get in excess of 800 rows/s for eventlog and more than
1000 rows/s for syslog. This is with fsync enabled and several indexes
active in an application where getting data into the DB _fast_ is
everything and query performance is secondary.

This is also on a decidedly sub-optimal environment: data loading over a
100 MBit/s network, PostgreSQL host on a 4 CPU 1.9 GHz Xeon, database
filesystem on a 4 disk RAID5 with 20 MB/s throughput (linear writes)
through the FS.

> The suggestions I've found include disabling fsync
> (done),

This is _not_ a good idea. Disable fsync only if losing your entire
database or getting your database corrupted upon system crash/power
fail is acceptable.

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

While you are stretching for higher hanging fruit you should also have
a look at the filesystem storing your database: ext3 ist _not_ a good
choice for this. In my benchmarks I found XFS to deliver the best
performance as a backing store FS for PostgreSQL.

And another one: switching from PostgreSQL 8.0.3 to PostgreSQL 8.1
provided a further performance boost.

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

Regards,
Alex.
--
"Opportunity is missed by most people because it is dressed in overalls and
looks like work." -- Thomas A. Edison