X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B6A869FB1D8 for ; Fri, 7 Jul 2006 22:29:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 35327-02 for ; Fri, 7 Jul 2006 22:28:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.202]) by postgresql.org (Postfix) with ESMTP id BA15A9FB1CF for ; Fri, 7 Jul 2006 22:28:53 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so839240wxc for ; Fri, 07 Jul 2006 18:28:52 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WTB1S41qg4Pm9g1oNxYqiVtxuaUsvNdmy4jJqH+odOSCgH6KD521mkpTVrN5sF4H6Uv+B6tRo9qnW8phh3c006aN1AdCwnkYnveDWh0v+6daV7L8flSRMbvQYiZ0r58tu3GWNQBu8xELDd/DO/HHJs0Z/QPXejz60XTs7vEV+us= Received: by 10.70.52.14 with SMTP id z14mr1810493wxz; Fri, 07 Jul 2006 18:28:52 -0700 (PDT) Received: by 10.70.73.15 with HTTP; Fri, 7 Jul 2006 18:28:52 -0700 (PDT) Message-ID: <1d219a6f0607071828q1d63fe55k8a1faeccf03b0884@mail.gmail.com> Date: Fri, 7 Jul 2006 21:28:52 -0400 From: "Chris Hoover" To: "William Scott Jordan" Subject: Re: VACUUM vs. REINDEX Cc: "Jeff Frost" , pgsql-performance@postgresql.org In-Reply-To: <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_131798_24752520.1152322132184" References: <6.2.3.0.2.20060707145857.0342ca58@mail.pandimensional.com> <6.2.3.0.2.20060707163917.06ae3ea8@mail.brownpapertickets.com> <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/80 X-Sequence-Number: 19940 ------=_Part_131798_24752520.1152322132184 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, William Scott Jordan wrote: > > Hi Jeff, > > Ah, okay. I see what information you were looking for. Doing a > VACUUM on the full DB, we get the following results: > > ---------------------------- > INFO: free space map: 885 relations, 8315 pages stored; 177632 total > pages needed > DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB > shared memory. > ---------------------------- > > -William William, You need to increase your fsm settings. The database is telling you it is trying to store 177K+ pages, but you have only provided it with 20K. Since these pages are cheap, I would set your fsm up with at least the following. max_fsm_pages 500000 max_fsm_relations 5000 This should provide PostgreSQL with enough space to work. You still might need to run one more vacuum full once you change the setting so that you can recover the space that was lost due to your fsm begin to small. Keep an eye on these last couple of lines from vacuum and adjust your setting accordingly. It may take a couple of tries to get PostgreSQL happy. Once your fsm is large enough, you should be able to dispense with the vacuum fulls and reindexes and just do normal vacuuming. Also in regards to the vacuum vs reindex. Reindexing is great and gives you nice clean "virgin" indexes, however, if you do not run an analyze (or vacuum analyze), the database will not have statistics for the new indexes. This will cause the planner to make bad choices. What I used to do before upgrading to 8.1 was run a vacuum full, reindexdb, vacuum analyze every weekend (we were on 7.3.4). This gave me pristine indexes and tables for Monday's start of the week. If you can, look hard at upgrading to 8.1.x as it will fix a lot of the issues you are having with autovacuum (along with a ton of other improvements). HTH, Chris ------=_Part_131798_24752520.1152322132184 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 7/7/06, William Scott Jordan <wsjordan@brownpapertickets.com> wrote:
Hi Jeff,

Ah, okay.  I see what information you were looking for.  Doing a
VACUUM on the full DB, we get the following results:

----------------------------
INFO:  free space map: 885 relations, 8315 pages stored; 177632 total
pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB
shared memory.
----------------------------

-William
William,

You need to increase your fsm settings.  The database is telling you it is trying to store 177K+ pages, but you have only provided it with 20K.  Since these pages are cheap, I would set your fsm up with at least the following.

max_fsm_pages 500000
max_fsm_relations 5000

This should provide PostgreSQL with enough space to work.  You still might need to run one more vacuum full once you change the setting so that you can recover the space that was lost due to your fsm begin to small.  Keep an eye on these last couple of lines from vacuum and adjust your setting accordingly.  It may take a couple of tries to get PostgreSQL happy.  Once your fsm is large enough, you should be able to dispense with the vacuum fulls and reindexes and just do normal vacuuming.

Also in regards to the vacuum vs reindex.  Reindexing is great and gives you nice clean "virgin" indexes, however, if you do not run an analyze (or vacuum analyze), the database will not have statistics for the new indexes.  This will cause the planner to make bad choices.

What I used to do before upgrading to 8.1 was run a vacuum full, reindexdb, vacuum analyze every weekend (we were on 7.3.4).  This gave me pristine indexes and tables for Monday's start of the week.

If you can, look hard at upgrading to 8.1.x as it will fix a lot of the issues you are having with autovacuum (along with a ton of other improvements).

HTH,

Chris


------=_Part_131798_24752520.1152322132184--