From revoohc@sermonaudio.com Tue Mar 15 20:46:27 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9718C568D5 for ; Tue, 15 Mar 2005 20:46:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15093-05 for ; Tue, 15 Mar 2005 20:46:22 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 36BFD56915 for ; Tue, 15 Mar 2005 20:46:20 +0000 (GMT) thread-index: AcUpoFaxsOT1DI8wSzi6zdwPOfuh9w== Received: from [10.4.13.50] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Tue, 15 Mar 2005 15:48:27 -0500 Message-ID: <423749C1.2030803@sermonaudio.com> Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Date: Tue, 15 Mar 2005 15:46:57 -0500 From: "Chris Hoover" User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Subject: Vacuum questions Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 15 Mar 2005 20:48:27.0546 (UTC) FILETIME=[56AAAFA0:01C529A0] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/231 X-Sequence-Number: 16729 Question on vacuuming. When you do a vacuum , are the "freed" tuples available only the table, or to the entire db, or to the entire cluster? The reason I'm asking is that we are getting ready to preform a major upgrade to our application that involves adding some new fields to almost every table and populating them. This is causing our test conversion db's to double in size (we are assuming that every update is causing a delete and re-insert behind the scenes due to the populating of the new columns). Anyway, we are working on trying to find the fastest way to recover the space. We have also had one test occurrence where after the upgrade, a vacuum full would not recover the space. However, when we bounced the postmaster, and then performed a vacuum full, the space was recovered. Any ideas on what might cause this? This happened on a newly restored db that was converted and then vacuumed full. There would have been no connections to the db after the conversion. PG 7.3.4 RH 2.1 Thanks, Chris From tgl@sss.pgh.pa.us Wed Mar 16 07:28:08 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 10347566AD for ; Wed, 16 Mar 2005 07:28:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66233-08 for ; Wed, 16 Mar 2005 07:27:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5D52656646 for ; Wed, 16 Mar 2005 07:27:47 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2G7RmRM003244; Wed, 16 Mar 2005 02:27:48 -0500 (EST) To: "Chris Hoover" Cc: pgsql-admin@postgresql.org Subject: Re: Vacuum questions In-reply-to: <423749C1.2030803@sermonaudio.com> References: <423749C1.2030803@sermonaudio.com> Comments: In-reply-to "Chris Hoover" message dated "Tue, 15 Mar 2005 15:46:57 -0500" Date: Wed, 16 Mar 2005 02:27:48 -0500 Message-ID: <3243.1110958068@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/236 X-Sequence-Number: 16734 "Chris Hoover" writes: > We have also had one test occurrence where after the upgrade, a vacuum > full would not recover the space. However, when we bounced the > postmaster, and then performed a vacuum full, the space was recovered. > Any ideas on what might cause this? Most likely, you had an open transaction lurking that was old enough that it could still "see" the deleted data, and so VACUUM couldn't safely reclaim the data. VACUUM's decisions about this are cluster-wide; so even though you'd recently created the database in question, a long-running transaction in another database under the same postmaster could still cause the problem. regards, tom lane From revoohc@sermonaudio.com Wed Mar 16 17:55:21 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BDDD656712 for ; Wed, 16 Mar 2005 17:55:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73451-09 for ; Wed, 16 Mar 2005 17:55:12 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 8DA4856599 for ; Wed, 16 Mar 2005 17:55:10 +0000 (GMT) thread-index: AcUqUZqIf330L3+ZTvejDpwrccnR4g== Received: from [10.4.13.50] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Wed, 16 Mar 2005 12:57:22 -0500 Message-ID: <42387328.9090806@sermonaudio.com> Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Date: Wed, 16 Mar 2005 12:55:52 -0500 From: "Chris Hoover" User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Tom Lane" Cc: Subject: Re: Vacuum questions References: <423749C1.2030803@sermonaudio.com> <3243.1110958068@sss.pgh.pa.us> In-Reply-To: <3243.1110958068@sss.pgh.pa.us> Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 16 Mar 2005 17:57:22.0281 (UTC) FILETIME=[9A814D90:01C52A51] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/249 X-Sequence-Number: 16747 Tom, Thanks so much for the possible reason the vacuum fulls are not running. But, how about the first question. When you run a vacuum
, who has access to the freed tuples? (only the table, the db, or the entire cluster)??? Thanks, Chris From smarlowe@g2switchworks.com Wed Mar 16 18:04:07 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4E3BA56581 for ; Wed, 16 Mar 2005 18:04:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78480-03 for ; Wed, 16 Mar 2005 18:04:04 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id EC003565AB for ; Wed, 16 Mar 2005 18:04:03 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 16 Mar 2005 12:04:01 -0600 Subject: Re: Vacuum questions From: Scott Marlowe To: Chris Hoover Cc: Tom Lane , pgsql-admin@postgresql.org In-Reply-To: <42387328.9090806@sermonaudio.com> References: <423749C1.2030803@sermonaudio.com> <3243.1110958068@sss.pgh.pa.us> <42387328.9090806@sermonaudio.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1110996240.28555.189.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 16 Mar 2005 12:04:01 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/250 X-Sequence-Number: 16748 On Wed, 2005-03-16 at 11:55, Chris Hoover wrote: > Tom, > > Thanks so much for the possible reason the vacuum fulls are not running. > > But, how about the first question. When you run a vacuum
, who > has access to the freed tuples? (only the table, the db, or the entire > cluster)??? the table From Julie.Meyer@noaa.gov Mon Aug 20 19:34:17 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4CF969F9552 for ; Mon, 20 Aug 2007 16:34:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 09327-08 for ; Mon, 20 Aug 2007 16:34:11 -0300 (ADT) X-Greylist: delayed 00:59:59.786247 by SQLgrey-1.7.5 Received: from mmp1.nems.noaa.gov (mmp1.nems.noaa.gov [140.90.121.156]) by postgresql.org (Postfix) with ESMTP id 1A0C39F9571 for ; Mon, 20 Aug 2007 16:34:13 -0300 (ADT) Received: from [204.227.103.140] by mmp1.nems.noaa.gov (Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005)) with ESMTPSA id <0JN3005QP4WY7360@mmp1.nems.noaa.gov> for pgsql-admin@postgresql.org; Mon, 20 Aug 2007 14:34:12 -0400 (EDT) Date: Mon, 20 Aug 2007 13:33:52 -0500 From: Juliann Meyer Subject: vacuum questions To: pgsql-admin@postgresql.org Message-id: <46C9DE90.1030206@noaa.gov> Organization: Missouri Basin River Forecast Center MIME-version: 1.0 Content-type: multipart/mixed; boundary="Boundary_(ID_WrgbPvXnVh2lGDp8fcpKMg)" User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/231 X-Sequence-Number: 26562 This is a multi-part message in MIME format. --Boundary_(ID_WrgbPvXnVh2lGDp8fcpKMg) Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT Currently running v7.4.8 postgres on a RHE Linux 4.0 box. Will be upgraded to postgres 8.2.x sometime this fall. Several of the tables in the database that resides on this system have lots of inserts and updates. Very little is deleted at this time, as the database is intended for archiving. Currently a cron job runs a vacuum analyze 3 times a day. Is there any need to run a vacuum full every so often? If so, how often should a vacuum full be done? And do all other processes that "hit" the database need to shut off when a vacuum full is done? Also another office that has a similar setup has switched to running pg_autovacuum instead of the vacuum analyze 3 times/day, are there any advantages and/or disadvantages to this approach? Thanks in advance for any information and guidance you can give, Julie --Boundary_(ID_WrgbPvXnVh2lGDp8fcpKMg) Content-type: text/x-vcard; charset=utf-8; name=julie.meyer.vcf Content-transfer-encoding: 7BIT Content-disposition: attachment; filename=julie.meyer.vcf begin:vcard fn:A. Juliann Meyer org:National Weather Service;Missouri Basin River Forecast Center adr;dom:;;;Pleasant Hill;MO;64080 email;internet:julie.meyer@noaa.gov title:Sr. Hydrologist - Data Systems x-mozilla-html:FALSE version:2.1 end:vcard --Boundary_(ID_WrgbPvXnVh2lGDp8fcpKMg)-- From robert@omniti.com Wed Aug 22 17:22:33 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 498A09F9677 for ; Wed, 22 Aug 2007 14:22:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 30506-03 for ; Wed, 22 Aug 2007 14:22:26 -0300 (ADT) X-Greylist: delayed 00:28:51.676365 by SQLgrey-1.7.5 Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by postgresql.org (Postfix) with ESMTP id 9078E9F9757 for ; Wed, 22 Aug 2007 14:22:26 -0300 (ADT) Received: from new-host.home ([71.179.23.138]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JN600GAZPKR1VD3@vms044.mailsrvcs.net> for pgsql-admin@postgresql.org; Wed, 22 Aug 2007 11:53:15 -0500 (CDT) Date: Wed, 22 Aug 2007 12:53:06 -0400 From: Robert Treat Subject: Re: vacuum questions In-reply-to: <46C9DE90.1030206@noaa.gov> To: pgsql-admin@postgresql.org Cc: Juliann Meyer Message-id: <200708221253.07375.robert@omniti.com> Organization: OmniTI MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-15 Content-transfer-encoding: 7bit Content-disposition: inline References: <46C9DE90.1030206@noaa.gov> User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/276 X-Sequence-Number: 26607 On Monday 20 August 2007 14:33, Juliann Meyer wrote: > Currently running v7.4.8 postgres on a RHE Linux 4.0 box. Will be > upgraded to postgres 8.2.x sometime this fall. > > Several of the tables in the database that resides on this system have > lots of inserts and updates. Very little is deleted at this time, as > the database is intended for archiving. > Be aware that an update is equivilant to a delete+insert, so even if you aren't "deleting", your updates will still cause accumlation of dead rows in your table. > Currently a cron job runs a vacuum analyze 3 times a day. Is there any > need to run a vacuum full every so often? There could be. If the number of updates your doing is greater than your free_space_map settings, then you wont be recovering all of the space you would want to be. Granted, a better solution is to increase free_space_map settings or vacuum analyze frequency, but you might still need an initial vacuum full to recover space. I *think* on 7.4 the output of vacuum analyze will tell you what your free space map settings need to be, so examine it's output the next few runs. > If so, how often should a vacuum full be done? And do all other > processes that "hit" the database need to shut off when a vacuum full is > done? Again, ideally you dont ever want to run vacuum full, so I'd look into getting more regular vacuum runs if needed, rather than doing regular vacuum fulls. Other things don't neccessarily need to be shut off, but vacuum full will lock tables exclusivly, so it will block other processes while it runs. How bad that is depends on your situation. Also, be aware that vacuum full may take considerably longer than a regular vacuum. > > Also another office that has a similar setup has switched to running > pg_autovacuum instead of the vacuum analyze 3 times/day, are there any > advantages and/or disadvantages to this approach? > The main advantage is that you will get more accurate vacuum/analyze information from your system while reducing overall load, as tables will only be vacuum as needed. (Think of it as a sniper approach, rather than the bazooka method your using now) The downside is that 7.4's pg_autovacuum is a little more complex than what you'll find in more recent versions of postgres, but if you're having trouble getting a handle on a good vacuum scheme, it might be worth it. -- Robert Treat Database Architect http://www.omniti.com/