X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4E6E1329D9F for ; Tue, 21 Sep 2004 14:22:04 +0100 (BST) 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 10991-08 for ; Tue, 21 Sep 2004 13:21:51 +0000 (GMT) Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56]) by svr1.postgresql.org (Postfix) with ESMTP id E26BA329D7F for ; Tue, 21 Sep 2004 14:21:54 +0100 (BST) Received: from D7MKWD21 (c-67-160-247-54.client.comcast.net[67.160.247.54]) by comcast.net (sccrmhc12) with SMTP id <2004092113215001200ctnn2e>; Tue, 21 Sep 2004 13:21:51 +0000 Message-ID: <000e01c49fde$4d122f50$02120a0a@D7MKWD21> From: "Patrick Hatcher" To: "Robert Treat" Cc: References: <002001c49f8f$ab663b40$02120a0a@D7MKWD21> <200409210212.33497.xzilla@users.sourceforge.net> Subject: Re: vacuum full & max_fsm_pages question Date: Tue, 21 Sep 2004 06:24:18 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/207 X-Sequence-Number: 8335 Sorry. I wrote PG 7.4.2 and then I erased it to write something else and then forgot to add it back. And thanks for the Page info. I was getting frustrated and looked in the wrong place. So it's probably best to drop and readd the indexes then? ----- Original Message ----- From: "Robert Treat" To: "Patrick Hatcher" Cc: Sent: Monday, September 20, 2004 11:12 PM Subject: Re: [PERFORM] vacuum full & max_fsm_pages question > On Tuesday 21 September 2004 00:01, Patrick Hatcher wrote: > > Hello. > > Couple of questions:> > > - Q1: Today I decided to do a vacuum full verbose analyze on a large table > > that has been giving me slow performance. And then I did it again. I > > noticed that after each run the values in my indexes and estimate row > > version changed. What really got me wondering is the fact my indexes > > report more rows than are in the table and then the estimated rows is less > > than the actual amount. > > > > The table is a read-only table that is updated 1/wk. After updating it is > > vacuumed full. I've also tried reindexing but the numbers still change. Is > > this normal? Below is a partial output for 4 consecutive vacuum full > > analyzes. No data was added nor was there anyone in the table. > > > > This looks normal to me for a pre 7.4 database, if I am right your running on > 7.2? Basically your indexes are overgrown, so each time you run vacuum you > are shrinking the number of pages involved, which will change the row counts, > and correspondingly change the count on the table as the sampled pages > change. > > > > - Q2: I have about a dozen 5M plus row tables. I currently have my > > max_fsm_pages set to 300,000. As you can see in vacuum full output I > > supplied, one table is already over this amount. Is there a limit on the > > size of max_fsm_pages? > > > > The limit is based on your memory... each page = 6 bytes. But according to > the output below you are not over 300000 pages yet on that table (though you > might be on some other tables.) > > > > > CONF settings: > > # - Memory - > > > > shared_buffers = 2000 # min 16, at least max_connections*2, 8KB > > each sort_mem = 12288 # min 64, size in KB > > #vacuum_mem = 8192 # min 1024, size in KB > > > > # - Free Space Map - > > > > max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each > > max_fsm_relations = 500 # min 100, ~50 bytes each > > > > > > Vacuum full information > > #after second vacuum full > > INFO: index "emaildat_fkey" now contains 8053743 row versions in 25764 > > pages DETAIL: 1895 index row versions were removed. > > 0 index pages have been deleted, 0 are currently reusable. > > CPU 2.38s/0.42u sec elapsed 11.11 sec. > > INFO: analyzing "cdm.cdm_email_data" > > INFO: "cdm_email_data": 65882 pages, 3000 rows sampled, 392410 estimated > > total rows > > > > > > #after third vacuum full > > INFO: index "emaildat_fkey" now contains 8052738 row versions in 25769 > > pages DETAIL: 890 index row versions were removed. > > 0 index pages have been deleted, 0 are currently reusable. > > CPU 2.08s/0.32u sec elapsed 4.36 sec. > > INFO: analyzing "cdm.cdm_email_data" > > INFO: "cdm_email_data": 65874 pages, 3000 rows sampled, 392363 estimated > > total rows > > > > > > #after REINDEX and vacuum full > > INFO: index "emaildat_fkey" now contains 8052369 row versions in 25771 > > pages DETAIL: 521 index row versions were removed. > > 0 index pages have been deleted, 0 are currently reusable. > > CPU 1.37s/0.35u sec elapsed 4.79 sec. > > INFO: analyzing "cdm.cdm_email_data" > > INFO: "cdm_email_data": 65869 pages, 3000 rows sampled, 392333 estimated > > total rows > > > > #After vacuum full(s) > > mdc_oz=# select count(*) from cdm.cdm_email_data; > > count > > --------- > > 5433358 > > (1 row) > > > > I do think the count(*) seems a bit off based on the vacuum output above. I'm > guessing you either have blocking transactions in the way or your not giving > us a complete copy/paste of the session involved. > > -- > Robert Treat > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL