Re: vacuum full & max_fsm_pages question

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" <xzilla@users.sourceforge.net>
To: "Patrick Hatcher" <pathat@comcast.net>
Cc: <pgsql-performance@postgresql.org>
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