SEO

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

Steve Eckmann <eckmann@computer.org> writes:
> 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.

Can you conveniently alter your application to batch INSERT commands
into transactions? Ie

BEGIN;
INSERT ...;
... maybe 100 or so inserts ...
COMMIT;
BEGIN;
... lather, rinse, repeat ...

This cuts down the transactional overhead quite a bit. A downside is
that you lose multiple rows if any INSERT fails, but then the same would
be true of multiple VALUES lists per INSERT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tue, Jan 03, 2006 at 04:44:28PM -0700, Steve Eckmann wrote:
> Are there general guidelines for tuning the PostgreSQL server for this kind
> of application? The suggestions I've found include disabling fsync (done),

Are you sure you really want this? The results could be catastrophic in case
of a crash.

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

I'm a bit confused here: How can you batch multiple INSERTs into large
statements for MySQL, but not batch multiple INSERTs into COPY statements for
PostgreSQL?

Anyhow, putting it all inside one transaction (or a few) is likely to help
quite a lot, but of course less when you have fsync=false. Bunding multiple
statements in each PQexec() call won't really give you that; you'll have to
tell the database so explicitly.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Tue, 3 Jan 2006, Tom Lane wrote:

> Steve Eckmann <eckmann@computer.org> writes:
> > 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.
>
> Can you conveniently alter your application to batch INSERT commands
> into transactions? Ie
>
> BEGIN;
> INSERT ...;
> ... maybe 100 or so inserts ...
> COMMIT;
> BEGIN;
> ... lather, rinse, repeat ...
>
> This cuts down the transactional overhead quite a bit. A downside is
> that you lose multiple rows if any INSERT fails, but then the same would
> be true of multiple VALUES lists per INSERT.

Steve, you mentioned that you data collector buffers the data before
sending it to the database, modify it so that each time it goes to send
things to the database you send all the data that's in the buffer as a
single transaction.

I am working on useing postgres to deal with log data and wrote a simple
perl script that read in the log files a line at a time, and then wrote
them 1000 at a time to the database. On a dual Opteron 240 box with 2G of
ram 1x 15krpm SCSI drive (and a untuned postgress install with the compile
time defaults) I was getting 5000-8000 lines/sec (I think this was with
fsync disabled, but I don't remember for sure). and postgres was
complaining that it was overrunning it's log sizes (which limits the speed
as it then has to pause to flush the logs)

the key thing is to send multiple lines with one transaction as tom shows
above.

David Lang


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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

Tom Lane wrote:

>Steve Eckmann <eckmann@computer.org> writes:
>
>
>>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.
>>
>>
>
>Can you conveniently alter your application to batch INSERT commands
>into transactions? Ie
>
> BEGIN;
> INSERT ...;
> ... maybe 100 or so inserts ...
> COMMIT;
> BEGIN;
> ... lather, rinse, repeat ...
>
>This cuts down the transactional overhead quite a bit. A downside is
>that you lose multiple rows if any INSERT fails, but then the same would
>be true of multiple VALUES lists per INSERT.
>
> regards, tom lane
>
>
Thanks for the suggestion, Tom. Yes, I think I could do that. But I
thought what I was doing now was effectively the same, because the
PostgreSQL 8.0.0 Documentation says (section 27.3.1): "It is allowed to
include multiple SQL commands (separated by semicolons) in the command
string. Multiple queries sent in a single PQexec call are processed in a
single transaction...." Our simulation application has nearly 400 event
types, each of which is a C++ class for which we have a corresponding
database table. So every thousand events or so I issue one PQexec() call
for each event type that has unlogged instances, sending INSERT commands
for all instances. For example,

PQexec(dbConn, "INSERT INTO FlyingObjectState VALUES (...); INSERT
INTO FlyingObjectState VALUES (...); ...");

My thought was that this would be a good compromise between minimizing
transactions (one per event class per buffering interval instead of one
per event) and minimizing disk seeking (since all queries in a single
transaction insert rows into the same table). Am I overlooking something
here? One thing I haven't tried is increasing the buffering interval
from 1000 events to, say, 10,000. It turns out that 1000 is a good
number for Versant, the object database system we're replacing, and for
MySQL, so I assumed it would be a good number for PostgreSQL, too.

Regards, Steve

Steinar H. Gunderson wrote:

>On Tue, Jan 03, 2006 at 04:44:28PM -0700, Steve Eckmann wrote:
>
>
>>Are there general guidelines for tuning the PostgreSQL server for this kind
>>of application? The suggestions I've found include disabling fsync (done),
>>
>>
>
>Are you sure you really want this? The results could be catastrophic in case
>of a crash.
>
>
>
>>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.
>>
>>
>
>I'm a bit confused here: How can you batch multiple INSERTs into large
>statements for MySQL, but not batch multiple INSERTs into COPY statements for
>PostgreSQL?
>
>Anyhow, putting it all inside one transaction (or a few) is likely to help
>quite a lot, but of course less when you have fsync=false. Bunding multiple
>statements in each PQexec() call won't really give you that; you'll have to
>tell the database so explicitly.
>
>/* Steinar */
>
>
Thanks, Steinar. I don't think we would really run with fsync off, but I
need to document the performance tradeoffs. You're right that my
explanation was confusing; probably because I'm confused about how to
use COPY! I could batch multiple INSERTS using COPY statements, I just
don't see how to do it without adding another process to read from
STDIN, since the application that is currently the database client is
constructing rows on the fly. I would need to get those rows into some
process's STDIN stream or into a server-side file before COPY could be
used, right?

You're comment about bundling multiple statements in each PQexec() call
seems to disagree with a statement in 27.3.1 that I interpret as saying
each PQexec() call corresponds to a single transaction. Are you sure my
interpretation is wrong?

Regards, Steve

dlang wrote:

>On Tue, 3 Jan 2006, Tom Lane wrote:
>
>
>
>>Steve Eckmann <eckmann@computer.org> writes:
>>
>>
>>>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.
>>>
>>>
>>Can you conveniently alter your application to batch INSERT commands
>>into transactions? Ie
>>
>> BEGIN;
>> INSERT ...;
>> ... maybe 100 or so inserts ...
>> COMMIT;
>> BEGIN;
>> ... lather, rinse, repeat ...
>>
>>This cuts down the transactional overhead quite a bit. A downside is
>>that you lose multiple rows if any INSERT fails, but then the same would
>>be true of multiple VALUES lists per INSERT.
>>
>>
>
>Steve, you mentioned that you data collector buffers the data before
>sending it to the database, modify it so that each time it goes to send
>things to the database you send all the data that's in the buffer as a
>single transaction.
>
>I am working on useing postgres to deal with log data and wrote a simple
>perl script that read in the log files a line at a time, and then wrote
>them 1000 at a time to the database. On a dual Opteron 240 box with 2G of
>ram 1x 15krpm SCSI drive (and a untuned postgress install with the compile
>time defaults) I was getting 5000-8000 lines/sec (I think this was with
>fsync disabled, but I don't remember for sure). and postgres was
>complaining that it was overrunning it's log sizes (which limits the speed
>as it then has to pause to flush the logs)
>
>the key thing is to send multiple lines with one transaction as tom shows
>above.
>
>David Lang
>
Thanks, David. I will look more carefully at how to batch multiple rows
per PQexec() call. Regards, Steve.

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
>
Thanks, Ian. I will look at how to implement your suggestions.

Regards, Steve

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend