pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR performance costs
8+ messages / 7 participants
[nested] [flat]
* PITR performance costs
@ 2007-05-28 12:45 Dave Cramer <pg@fastcrypt.com>
2007-05-28 12:53 ` Re: PITR performance costs Bill Moran <wmoran@collaborativefusion.com>
2007-05-28 12:54 ` Re: PITR performance costs A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-05-28 16:31 ` Re: PITR performance costs Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-28 20:40 ` Re: PITR performance costs Simon Riggs <simon@2ndquadrant.com>
2007-05-29 13:06 ` Re: PITR performance costs Merlin Moncure <mmoncure@gmail.com>
0 siblings, 5 replies; 8+ messages in thread
From: Dave Cramer @ 2007-05-28 12:45 UTC (permalink / raw)
To: pgsql-performance
Since PITR has to enable archiving does this not increase the amount
of disk I/O required ?
Dave
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-28 12:53 ` Bill Moran <wmoran@collaborativefusion.com>
4 siblings, 0 replies; 8+ messages in thread
From: Bill Moran @ 2007-05-28 12:53 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
Dave Cramer <pg@fastcrypt.com> wrote:
> Since PITR has to enable archiving does this not increase the amount
> of disk I/O required ?
It does increase the required amount of I/O.
--
Bill Moran
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-28 12:54 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
4 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2007-05-28 12:54 UTC (permalink / raw)
To: pgsql-performance
am Mon, dem 28.05.2007, um 8:45:38 -0400 mailte Dave Cramer folgendes:
> Since PITR has to enable archiving does this not increase the amount
> of disk I/O required ?
Yes. But you can use a different hard drive for this log.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-28 16:31 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-28 18:48 ` Re: PITR performance costs Dave Cramer <pg@fastcrypt.com>
4 siblings, 1 reply; 8+ messages in thread
From: Heikki Linnakangas @ 2007-05-28 16:31 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
Dave Cramer wrote:
> Since PITR has to enable archiving does this not increase the amount of
> disk I/O required ?
There's no difference in normal DML operations, but some bulk operations
like CREATE INDEX that don't otherwise generate WAL, need to be WAL
logged when archiving is enabled.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
2007-05-28 16:31 ` Re: PITR performance costs Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-05-28 18:48 ` Dave Cramer <pg@fastcrypt.com>
2007-05-28 19:46 ` Re: PITR performance costs Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 8+ messages in thread
From: Dave Cramer @ 2007-05-28 18:48 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
Heikki,
Don't the archived logs have to be copied as well as the regular WAL
logs get recycled ?
Dave
On 28-May-07, at 12:31 PM, Heikki Linnakangas wrote:
> Dave Cramer wrote:
>> Since PITR has to enable archiving does this not increase the
>> amount of disk I/O required ?
>
> There's no difference in normal DML operations, but some bulk
> operations like CREATE INDEX that don't otherwise generate WAL,
> need to be WAL logged when archiving is enabled.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
2007-05-28 16:31 ` Re: PITR performance costs Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-28 18:48 ` Re: PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-28 19:46 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 8+ messages in thread
From: Stephen Frost @ 2007-05-28 19:46 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance
Dave, et al,
* Dave Cramer (pg@fastcrypt.com) wrote:
> Don't the archived logs have to be copied as well as the regular WAL
> logs get recycled ?
Yes, but I'd expect at the point they're being copied off to some other
store (probably a seperate disk, or even over the network to another
system, etc), they're probably in the system cache, so you're probably
not going out to disk to get those blocks anyway. That might not be the
case on a slow-write system, but in those cases it seems at least
somewhat unlikely you'll be hit very hard by the occational 16MB copy
off the disk...
Thanks,
Stephen
> On 28-May-07, at 12:31 PM, Heikki Linnakangas wrote:
>
> >Dave Cramer wrote:
> >>Since PITR has to enable archiving does this not increase the
> >>amount of disk I/O required ?
> >
> >There's no difference in normal DML operations, but some bulk
> >operations like CREATE INDEX that don't otherwise generate WAL,
> >need to be WAL logged when archiving is enabled.
> >
> >--
> > Heikki Linnakangas
> > EnterpriseDB http://www.enterprisedb.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-28 20:40 ` Simon Riggs <simon@2ndquadrant.com>
4 siblings, 0 replies; 8+ messages in thread
From: Simon Riggs @ 2007-05-28 20:40 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
On Mon, 2007-05-28 at 08:45 -0400, Dave Cramer wrote:
> Since PITR has to enable archiving does this not increase the amount
> of disk I/O required ?
As Heikki says, some operations need logging when PITR is on; these are
now documented in the performance tips section of the latest dev docs:
http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PITR
This isn't additional logging because of PITR, its just that we've had
to exclude PITR from some recent tuning operations. I'll be looking at
ways of making that optional in certain cases.
Overall, the cost of shipping WAL files away has been measured in large
scale tests by Mark Wong to be around 1% drop in measured peak
transaction throughput, tests about ~2 years ago now on 8.0. It's
possible that has increased as we have further tuned the server, but I'm
thinking its still fairly negligible overall.
Replication solutions currently weigh in significantly more than this
overhead, which is one reason to make me start thinking about log based
replication in future releases.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PITR performance costs
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
@ 2007-05-29 13:06 ` Merlin Moncure <mmoncure@gmail.com>
4 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2007-05-29 13:06 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
On 5/28/07, Dave Cramer <pg@fastcrypt.com> wrote:
> Since PITR has to enable archiving does this not increase the amount
> of disk I/O required ?
I've set up warm standbys on a few servers (some of them quite
busy!)...the additional load is virtually unmeasurable. I usually
don't copy the files locally...I scp them off to some other server.
When archived, the WAL files are likely cached but there is some
overhead to copying them off however.
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-29 13:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-28 12:45 PITR performance costs Dave Cramer <pg@fastcrypt.com>
2007-05-28 12:53 ` Bill Moran <wmoran@collaborativefusion.com>
2007-05-28 12:54 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-05-28 16:31 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-28 18:48 ` Dave Cramer <pg@fastcrypt.com>
2007-05-28 19:46 ` Stephen Frost <sfrost@snowman.net>
2007-05-28 20:40 ` Simon Riggs <simon@2ndquadrant.com>
2007-05-29 13:06 ` Merlin Moncure <mmoncure@gmail.com>
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