Re: vacuum full & max_fsm_pages question - Mailing list pgsql-performance

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