From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Eckmann <eckmann@computer.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: improving write performance for logging application
Date: Tue, 03 Jan 2006 19:00:12 -0500
Message-ID: <27720.1136332812@sss.pgh.pa.us> (raw)
In-Reply-To: <43BB0C5C.8020207@computer.org>
References: <43BB0C5C.8020207@computer.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, eckmann@computer.org
Subject: Re: improving write performance for logging application
In-Reply-To: <27720.1136332812@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox