Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C5CA2B5DBE0 for ; Wed, 5 Oct 2011 02:51:47 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 83843-03 for ; Wed, 5 Oct 2011 05:51:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by mail.postgresql.org (Postfix) with ESMTP id 19D12B5DBD9 for ; Wed, 5 Oct 2011 02:51:41 -0300 (ADT) Received: from [75.247.112.93] (93.sub-75-247-112.myvzw.com [75.247.112.93]) by mrelayeu.kundenserver.de (node=mreu1) with ESMTP (Nemesis) id 0LjfTA-1QZnmM3aTA-00cCRV; Wed, 05 Oct 2011 07:51:40 +0200 Message-ID: <4E8BF065.1020105@2ndQuadrant.com> Date: Tue, 04 Oct 2011 22:51:33 -0700 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: streaming replication and wraparound vacuuming References: <0370E60157CA7E4097D81B9E09728A9409A8701B31@MAILR001.mail.lan> In-Reply-To: <0370E60157CA7E4097D81B9E09728A9409A8701B31@MAILR001.mail.lan> Content-Type: multipart/alternative; boundary="------------020902070407030603050206" X-Provags-ID: V02:K0:ATvum+ZaysHs60enJEuNBcHiEYRnqIzk9lVZRyz4puI u3wKjIBJy2SZViSflY46Fh5bEcKtyoy5Si7R+M5beiregcpl33 ofJbIZnDUdN+Xx6rOUoAxNe2OgVQBIRJzsUVf35NKvX0WyPjlo 4BiB0RkVpYCMXyIKK15wOoLwtz0JI7u1/e+QnrXG9J+VBzrCiH Bu4S6lblmir+HKTU1AxCRb8RMRUpdN0trikR/BTSFyrGiV7csF HluZQGTbA2qrQQoV/hU9WKo/sarRZ2M7xZy+kvp99aFWCPRrRy ndc50gps7FX0cGh+H9r99m2kchUgmRcvDdD3iBC0KQs6pEMxwB Wt3Wexyw7G9FOQiSjPNIvqno78/zrbiQmUG9wYowZ X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.179 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SARE_SPEC_REPLICA=0.72 X-Spam-Level: X-Archive-Number: 201110/20 X-Sequence-Number: 38472 This is a multi-part message in MIME format. --------------020902070407030603050206 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------020902070407030603050206 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------020902070407030603050206--