pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: improving write performance for logging
8+ messages / 5 participants
[nested] [flat]
* Re: improving write performance for logging
@ 2006-01-04 14:29 Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2006-01-04 14:29 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-04 16:00 Ian Westmacott <ianw@intellivid.com>
parent: Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 8+ messages in thread
From: Ian Westmacott @ 2006-01-04 16:00 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance
On Wed, 2006-01-04 at 09:29 -0500, Ron wrote:
> 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?
It seems we are pushing it there. Our intentions are to scale much
further, but our plans are to distribute at this point.
> How large is each record being inserted?
They are small: 32 (data) bytes.
> How much can you put in a COPY and how many COPYs
> can you put into a transactions?
These are driven by the application; we do about 60 COPYs and a couple
dozen INSERT/UPDATE/DELETEs in a single transaction. Each COPY is
doing a variable number of rows, up to several hundred. We do 15 of
these transactions per second.
> What values are you using for bgwriter* and checkpoint*?
bgwriter is 100%/500 pages, and checkpoint is 50 segments/300 seconds.
wal_buffers doesn't do much for us, and fsync is enabled.
> What HW on you running on and what kind of performance do you typically get?
The WAL is a 2-spindle (SATA) RAID0 with its own controller (ext3).
The tables are on a 10-spindle (SCSI) RAID50 with dual U320
controllers (XFS). This is overkill for writing and querying the data,
but we need to constantly ANALYZE and VACUUM in the
background without interrupting the inserts (the app is 24x7). The
databases are 4TB, so these operations can be lengthy.
--
Ian Westmacott <ianw@intellivid.com>
Intellivid Corp.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 01:08 Jim C. Nasby <jnasby@pervasive.com>
parent: Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2006-01-06 01:08 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: Ron <rjpeace@earthlink.net>; pgsql-performance
On Wed, Jan 04, 2006 at 11:00:38AM -0500, Ian Westmacott wrote:
> The WAL is a 2-spindle (SATA) RAID0 with its own controller (ext3).
> The tables are on a 10-spindle (SCSI) RAID50 with dual U320
> controllers (XFS). This is overkill for writing and querying the data,
> but we need to constantly ANALYZE and VACUUM in the
> background without interrupting the inserts (the app is 24x7). The
> databases are 4TB, so these operations can be lengthy.
How come you're using RAID50 instead of just RAID0? Or was WAL being on
RAID0 a typo?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 14:00 Ian Westmacott <ianw@intellivid.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 8+ messages in thread
From: Ian Westmacott @ 2006-01-06 14:00 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
On Thu, 2006-01-05 at 19:08 -0600, Jim C. Nasby wrote:
> On Wed, Jan 04, 2006 at 11:00:38AM -0500, Ian Westmacott wrote:
> > The WAL is a 2-spindle (SATA) RAID0 with its own controller (ext3).
> > The tables are on a 10-spindle (SCSI) RAID50 with dual U320
> > controllers (XFS). This is overkill for writing and querying the data,
> > but we need to constantly ANALYZE and VACUUM in the
> > background without interrupting the inserts (the app is 24x7). The
> > databases are 4TB, so these operations can be lengthy.
>
> How come you're using RAID50 instead of just RAID0? Or was WAL being on
> RAID0 a typo?
We use RAID50 instead of RAID0 for the tables for some fault-tolerance.
We use RAID0 for the WAL for performance.
I'm missing the implication of the question...
--
Ian Westmacott <ianw@intellivid.com>
Intellivid Corp.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 16:03 Michael Stone <mstone+postgres@mathom.us>
parent: Ian Westmacott <ianw@intellivid.com>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Stone @ 2006-01-06 16:03 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jan 06, 2006 at 09:00:06AM -0500, Ian Westmacott wrote:
>We use RAID50 instead of RAID0 for the tables for some fault-tolerance.
>We use RAID0 for the WAL for performance.
>
>I'm missing the implication of the question...
If you have the WAL on RAID 0 you have no fault tolerance, regardless of
what level you use for the tables.
Mike Stone
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 16:37 Jim C. Nasby <jnasby@pervasive.com>
parent: Ian Westmacott <ianw@intellivid.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jim C. Nasby @ 2006-01-06 16:37 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: pgsql-performance
On Fri, Jan 06, 2006 at 09:00:06AM -0500, Ian Westmacott wrote:
> On Thu, 2006-01-05 at 19:08 -0600, Jim C. Nasby wrote:
> > On Wed, Jan 04, 2006 at 11:00:38AM -0500, Ian Westmacott wrote:
> > > The WAL is a 2-spindle (SATA) RAID0 with its own controller (ext3).
> > > The tables are on a 10-spindle (SCSI) RAID50 with dual U320
> > > controllers (XFS). This is overkill for writing and querying the data,
> > > but we need to constantly ANALYZE and VACUUM in the
> > > background without interrupting the inserts (the app is 24x7). The
> > > databases are 4TB, so these operations can be lengthy.
> >
> > How come you're using RAID50 instead of just RAID0? Or was WAL being on
> > RAID0 a typo?
>
> We use RAID50 instead of RAID0 for the tables for some fault-tolerance.
> We use RAID0 for the WAL for performance.
>
> I'm missing the implication of the question...
The problem is that if you lose WAL or the data, you've lost everything.
So you might as well use raid0 for the data if you're using it for WAL.
Or switch WAL to raid1. Actually, a really good controller *might* be
able to do a good job of raid5 for WAL. Or just use raid10.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 19:02 Ian Westmacott <ianw@intellivid.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ian Westmacott @ 2006-01-06 19:02 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
On Fri, 2006-01-06 at 10:37 -0600, Jim C. Nasby wrote:
> The problem is that if you lose WAL or the data, you've lost everything.
> So you might as well use raid0 for the data if you're using it for WAL.
> Or switch WAL to raid1. Actually, a really good controller *might* be
> able to do a good job of raid5 for WAL. Or just use raid10.
If the WAL is lost, can you lose more than the data since the last
checkpoint?
--
Ian Westmacott <ianw@intellivid.com>
Intellivid Corp.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: improving write performance for logging
@ 2006-01-06 20:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ian Westmacott <ianw@intellivid.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-01-06 20:27 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-performance
Ian Westmacott <ianw@intellivid.com> writes:
> If the WAL is lost, can you lose more than the data since the last
> checkpoint?
The problem is that you might have partially-applied actions since the
last checkpoint, rendering your database inconsistent; then *all* the
data is suspect if not actually corrupt.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-01-06 20:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-04 14:29 Re: improving write performance for logging Ron <rjpeace@earthlink.net>
2006-01-04 16:00 ` Ian Westmacott <ianw@intellivid.com>
2006-01-06 01:08 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-06 14:00 ` Ian Westmacott <ianw@intellivid.com>
2006-01-06 16:03 ` Michael Stone <mstone+postgres@mathom.us>
2006-01-06 16:37 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-06 19:02 ` Ian Westmacott <ianw@intellivid.com>
2006-01-06 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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