Thread: 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

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

Patrick,

> Sorry.  I wrote PG 7.4.2 and then I erased it to write something else and
> then forgot to add it back.

Odd.  You shouldn't be having to re-vacuum on 7.4.

> 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?

Well, I have to wonder if you've not run afoul of the known 7.4.2 bug
regarding indexes.   This system hasn't had an improper database shutdown or
power-out in the last few weeks, has it?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Nope.  It's been running like a champ for while now.

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM



             Josh Berkus
             <josh@agliodbs.co
             m>                                                         To
             Sent by:                  "Patrick Hatcher"
             pgsql-performance         <pathat@comcast.net>
             -owner@postgresql                                          cc
             .org                      "Robert Treat"
                                       <xzilla@users.sourceforge.net>,
                                       <pgsql-performance@postgresql.org>
             09/21/2004 10:49                                      Subject
             AM                        Re: [PERFORM] vacuum full &
                                       max_fsm_pages question










Patrick,

> Sorry.  I wrote PG 7.4.2 and then I erased it to write something else and
> then forgot to add it back.

Odd.  You shouldn't be having to re-vacuum on 7.4.

> 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?

Well, I have to wonder if you've not run afoul of the known 7.4.2 bug
regarding indexes.   This system hasn't had an improper database shutdown
or
power-out in the last few weeks, has it?

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match