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(at)users(dot)sourceforge(dot)net>
To: "Patrick Hatcher" <pathat(at)comcast(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)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