pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstreaming replication and wraparound vacuuming
2+ messages / 2 participants
[nested] [flat]
* streaming replication and wraparound vacuuming
@ 2011-09-30 19:28 Andrew Shved <andys@aggregateknowledge.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew Shved @ 2011-09-30 19:28 UTC (permalink / raw)
To: pgsql-admin
I have a set up where most of the data located in partitions of the large table and that data never changes. The only table that really changes is current day's partition. The problem is that transaction wraparound vacuum generates a lot of traffic quite frequently on partitions that never changed (technically read only). This is especially a problem with streaming replication due to enormous amounts of logging activity that is being generated. Is there a way to limit or eliminate those frequent vacuum. Something akin to oralce read only tablespace or a way to set frozen transaction xid on a partition level? Basically anything to make it less frequent especially when there is zero activity on those tables after initial loads.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: streaming replication and wraparound vacuuming
@ 2011-10-05 05:51 Greg Smith <greg@2ndQuadrant.com>
parent: Andrew Shved <andys@aggregateknowledge.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Smith @ 2011-10-05 05:51 UTC (permalink / raw)
To: pgsql-admin
On 09/30/2011 12:28 PM, Andrew Shved wrote:
>
> I have a set up where most of the data located in partitions of the
> large table and that data never changes. The only table that really
> changes is current day's partition. The problem is that transaction
> wraparound vacuum generates a lot of traffic quite frequently on
> partitions that never changed (technically read only). This is
> especially a problem with streaming replication due to enormous
> amounts of logging activity that is being generated. Is there a way
> to limit or eliminate those frequent vacuum. Something akin to oralce
> read only tablespace or a way to set frozen transaction xid on a
> partition level? Basically anything to make it less frequent
> especially when there is zero activity on those tables after initial
> loads.
>
There's not really a good answer to this yet. Write-once data is common
enough that I'd like to see some accelerated paths for adding it
appear. I'm not aware of anyone working in that direction right now though.
Normally writes in PostgreSQL can result in blocks being written out
many times:
-pg_xlog image
-Initial write to data block on disk
-Write(s) to update hint bits
-Initial VACUUM
-XID wraparound VACUUM FREEZE
If you aggressively run VACUUM FREEZE yourself, setting
vacuum_freeze_min_age and possibly autovacuum_freeze_max_age to lower
values than the defaults, you might avoid some number of these multiple
writes, which are wasted from your perspective. Aim to commit and
freeze as fast as possible afterwards, avoiding some of the intermediate
writes you might otherwise see. That's the best that can be done
without some hacking on the server code.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-10-05 05:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-30 19:28 streaming replication and wraparound vacuuming Andrew Shved <andys@aggregateknowledge.com>
2011-10-05 05:51 ` Greg Smith <greg@2ndQuadrant.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