DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327;
 d=earthlink.net;
	b=kFtJPct1bagcyo94gQOdu/JZKEOm+GpFBx/HUujzv3GKPZQraP+S+Q86S4CiG/np;
	h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type:Content-Transfer-Encoding:X-ELNK-Trace:X-Originating-IP;
Message-Id: <7.0.1.0.2.20060104092139.01c37da0@earthlink.net>
Date: Wed, 04 Jan 2006 09:29:00 -0500
To: Ian Westmacott <ianw@intellivid.com>,pgsql-performance@postgresql.org
From: Ron <rjpeace@earthlink.net>
Subject: Re: improving write performance for logging
In-Reply-To: <1136382865.24450.7.camel@spectre.intellivid.com>
References: <43BB0C5C.8020207@computer.org>
	<1136382865.24450.7.camel@spectre.intellivid.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

2B is a lot of inserts.  If you had to guess,=20
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=20
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=20
> write performance of PostgreSQL for logging=20
> data from a real-time simulation. We found that=20
> MySQL 4.1.3 could log about 1480 objects/second=20
> using MyISAM tables or about 1225=20
> objects/second using InnoDB tables, but=20
> PostgreSQL 8.0.3 could log only about 540=20
> objects/second. (test system: quad-Itanium2,=20
> 8GB memory, SCSI RAID, GigE connection from=20
> simulation server, nothing running except=20
> system processes and database system under test)
> >
> > We also found that we could improve MySQL=20
> performance significantly using MySQL's=20
> "INSERT" command extension allowing multiple=20
> value-list tuples in a single command; the rate=20
> for MyISAM tables improved to about 2600=20
> objects/second. PostgreSQL doesn't support that=20
> language extension. Using the COPY command=20
> instead of INSERT might help, but since rows=20
> are being generated on the fly, I don't see how=20
> to use COPY without running a separate process=20
> that reads rows from the application and uses=20
> COPY to write to the database. The application=20
> currently has two processes: the simulation and=20
> a data collector that reads events from the sim=20
> (queued in shared memory) and writes them as=20
> rows to the database, buffering as needed to=20
> avoid lost data during periods of high=20
> activity. To use COPY I think we would have to=20
> split our data collector into two processes communicating via a pipe.
> >
> > Query performance is not an issue: we found=20
> that when suitable indexes are added PostgreSQL=20
> is fast enough on the kinds of queries our=20
> users make. The crux is writing rows to the=20
> database fast enough to keep up with the simulation.
> >
> > Are there general guidelines for tuning the=20
> PostgreSQL server for this kind of application?=20
> The suggestions I've found include disabling=20
> fsync (done), increasing the value of=20
> wal_buffers, and moving the WAL to a different=20
> disk, but these aren't likely to produce the 3x=20
> improvement that we need. On the client side=20
> I've found only two suggestions: disable=20
> autocommit and use COPY instead of INSERT. I=20
> think I've effectively disabled autocommit by=20
> batching up to several hundred INSERT commands=20
> in each PQexec() call, and it isn=E2=80=99t clear=20
> 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