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 9F7FA329E17 for ; Tue, 21 Sep 2004 04:59:08 +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 27453-08 for ; Tue, 21 Sep 2004 03:59:01 +0000 (GMT) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by svr1.postgresql.org (Postfix) with ESMTP id C027B329DB7 for ; Tue, 21 Sep 2004 04:59:00 +0100 (BST) Received: from D7MKWD21 (c-67-160-247-54.client.comcast.net[67.160.247.54]) by comcast.net (sccrmhc11) with SMTP id <2004092103585801100it6hoe>; Tue, 21 Sep 2004 03:58:59 +0000 Message-ID: <002001c49f8f$ab663b40$02120a0a@D7MKWD21> From: "Patrick Hatcher" To: Subject: vacuum full & max_fsm_pages question Date: Mon, 20 Sep 2004 21:01:26 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001D_01C49F54.FE56FF00" 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.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200409/204 X-Sequence-Number: 8332 This is a multi-part message in MIME format. ------=_NextPart_000_001D_01C49F54.FE56FF00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 noti= ced that after each run the values in my indexes and estimate row version c= hanged. What really got me wondering is the fact my indexes report more ro= ws than are in the table and then the estimated rows is less than the actua= l 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 an= alyzes. No data was added nor was there anyone in the table. - 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, on= e table is already over this amount. Is there a limit on the size of max_f= sm_pages? CONF settings: # - Memory - shared_buffers =3D 2000 # min 16, at least max_connections*2, 8KB= each sort_mem =3D 12288 # min 64, size in KB #vacuum_mem =3D 8192 # min 1024, size in KB # - Free Space Map - max_fsm_pages =3D 300000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations =3D 500 # min 100, ~50 bytes each Vacuum full information #after second vacuum full INFO: index "emaildat_fkey" now contains 8053743 row versions in 25764 pag= es 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 t= otal rows #after third vacuum full INFO: index "emaildat_fkey" now contains 8052738 row versions in 25769 pag= es 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 t= otal rows #after REINDEX and vacuum full INFO: index "emaildat_fkey" now contains 8052369 row versions in 25771 pag= es 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 t= otal rows #After vacuum full(s) mdc_oz=3D# select count(*) from cdm.cdm_email_data; count --------- 5433358 (1 row) TIA Patrick= ------=_NextPart_000_001D_01C49F54.FE56FF00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
Hello.
Couple of questions:
 
 
- Q1: Today I decided to do a vacuum full = verbose=20 analyze on a large table that has been giving me slow performance.  An= d=20 then I did it again.  I noticed that after each run the values in= my=20 indexes and estimate row version changed.  What really got me=20 wondering is the fact my indexes report more rows than are in the table and= then=20 the estimated rows is less than the actual amount.
 
The table is a read-only table that is upd= ated=20 1/wk.  After updating it is vacuumed full.  I've also tried reind= exing=20 but the numbers still change.
Is this normal?  Below is a partial o= utput for=20 4 consecutive vacuum full analyzes.  No data was added nor was there a= nyone=20 in the table.
 
- Q2: I have about a dozen 5M plus row=20 tables.  I currently have my max_fsm_pages set to 300,000.  As yo= u can=20 see in vacuum full output I supplied, one table is already over this=20 amount.  Is there a limit on the size of max_fsm_pages?
 
 
CONF settings:
# - Memory -
 
shared_buffers =3D=20 2000           # min 16, = at=20 least max_connections*2, 8KB each
sort_mem =3D=20 12288           &nbs= p;   =20 # min 64, size in KB
#vacuum_mem =3D=20 8192            = ; =20 # min 1024, size in KB
 
# - Free Space Map -
 
max_fsm_pages =3D=20 300000          # min=20 max_fsm_relations*16, 6 bytes each
max_fsm_relations =3D=20 500         # min 100, ~50 bytes=20 each
 
Vacuum full information
#after second vacuum full
INFO:  index "emaildat_fkey" now cont= ains=20 8053743 row versions in 25764 pages
DETAIL:  1895 index row version= s=20 were removed.
0 index pages have been deleted, 0 are currently=20 reusable.
CPU 2.38s/0.42u sec elapsed 11.11 sec.
INFO:  analyzin= g=20 "cdm.cdm_email_data"
INFO:  "cdm_email_data": 65882 pages, 3000 row= s=20 sampled, 392410 estimated total rows
 
 
#after third vacuum full
INFO:  index "emaildat_fkey" now cont= ains=20 8052738 row versions in 25769 pages
DETAIL:  890 index row versions= were=20 removed.
0 index pages have been deleted, 0 are currently reusable.
C= PU=20 2.08s/0.32u sec elapsed 4.36 sec.
INFO:  analyzing=20 "cdm.cdm_email_data"
INFO:  "cdm_email_data": 65874 pages, 3000 row= s=20 sampled, 392363 estimated total rows
 
 
#after REINDEX and  vacuum full
INFO:  index "emaildat_fkey" now cont= ains=20 8052369 row versions in 25771 pages
DETAIL:  521 index row versions= were=20 removed.
0 index pages have been deleted, 0 are currently reusable.
C= PU=20 1.37s/0.35u sec elapsed 4.79 sec.
INFO:  analyzing=20 "cdm.cdm_email_data"
INFO:  "cdm_email_data": 65869 pages, 3000 row= s=20 sampled, 392333 estimated total rows
 
#After vacuum full(s)
mdc_oz=3D# select count(*) from=20 cdm.cdm_email_data;
  count
---------
 5433358
(1=20 row)
 
TIA
Patrick
------=_NextPart_000_001D_01C49F54.FE56FF00--