Обсуждение: UnUsed Tuple Space Huge

 From what I understand, the new vacuum will mark deleted tuples as
unused and try to re-use the space if possible.  However, I seem to have
a huge amount of UnUsed tuples on some large tables.  The table is
updated 1x each day followed by a vacuum analyze.

Any idea why the huge UnUsed tuples.  Performance decreases and indexes
stop being used in favor of full table scans.

VACUUM verbose symbol_Data;
NOTICE:  --Relation symbol_data--
NOTICE:  Pages 767990: Changed 0, Empty 0; Tup 21595176: Vac 0, Keep 0,
UnUsed 16560877.
    Total CPU 61.94s/6.94u sec elapsed 179.25 sec.
VACUUM

I'm thinking I may just have to copy/rename periodically to keep it down.

This was on psql (PostgreSQL) 7.2rc2.  Will upgrade to 7.2.1, but didn't
see anything in the Changelogs/History to indicate anything will change.


Thanks,
Michael

"Michael G. Martin" <michael@vpmonline.com> writes:
> VACUUM verbose symbol_Data;
> NOTICE:  --Relation symbol_data--
> NOTICE:  Pages 767990: Changed 0, Empty 0; Tup 21595176: Vac 0, Keep 0,
                 ^^^^^^
> UnUsed 16560877.
>     Total CPU 61.94s/6.94u sec elapsed 179.25 sec.

That seems to be a couple orders of magnitude above the default size of
the free space map (10000 pages IIRC).  How big is your database in
total?  You definitely need to increase MAX_FSM_PAGES in
postgresql.conf, but I can't tell how far on the basis of this one
datapoint.

You will probably want to do a VACUUM FULL to clean out some of the free
space and then see where you really stand on number of pages.

            regards, tom lane

Thanks Tom,

That's what I was beginning to think from reading.  I just wasn't sure of the correlation between MAX_FSM_PAGES and the actual pages occupied by the table after a full vacuum.  1:1 ?? Any general rules?

Thanks,
Michael

Tom Lane wrote:

"Michael G. Martin" <michael@vpmonline.com> writes: 

VACUUM verbose symbol_Data;
NOTICE:  --Relation symbol_data--
NOTICE:  Pages 767990: Changed 0, Empty 0; Tup 21595176: Vac 0, Keep 0,    

                 ^^^^^^ 

UnUsed 16560877.   Total CPU 61.94s/6.94u sec elapsed 179.25 sec.   

That seems to be a couple orders of magnitude above the default size of
the free space map (10000 pages IIRC).  How big is your database in
total?  You definitely need to increase MAX_FSM_PAGES in
postgresql.conf, but I can't tell how far on the basis of this one
datapoint.

You will probably want to do a VACUUM FULL to clean out some of the free
space and then see where you really stand on number of pages.
		regards, tom lane 

A "fresh" table with this data has the following pg_class entries:

relname        | n
reltype        | 265306445
relowner       | 503
relam          | 0
relfilenode    | 265306444
relpages       | 429631
reltuples      | 2.09533e+07
reltoastrelid  | 0
reltoastidxid  | 0
relhasindex    | f
relisshared    | f
relkind        | r
relnatts       | 14
relchecks      | 0
reltriggers    | 0
relukeys       | 0
relfkeys       | 0
relrefs        | 0
relhasoids     | t
relhaspkey     | f

I read through the opening comments in src/backend/commands/vacuumlazy.c describing MaxFSMPages and VacuumMem.  I'm going to bump them up to the following values and see what happens over time.

vacuum_mem = 32768          # min 1024
max_fsm_pages = 100000      # min 1000, fsm is free space map

--Michael


Michael G. Martin wrote:

Thanks Tom,

That's what I was beginning to think from reading.  I just wasn't sure of the correlation between MAX_FSM_PAGES and the actual pages occupied by the table after a full vacuum.  1:1 ?? Any general rules?

Thanks,
Michael

Tom Lane wrote:

"Michael G. Martin" <michael@vpmonline.com> writes: 

VACUUM verbose symbol_Data;
NOTICE:  --Relation symbol_data--
NOTICE:  Pages 767990: Changed 0, Empty 0; Tup 21595176: Vac 0, Keep 0,    

                 ^^^^^^ 

UnUsed 16560877.   Total CPU 61.94s/6.94u sec elapsed 179.25 sec.   

That seems to be a couple orders of magnitude above the default size of
the free space map (10000 pages IIRC).  How big is your database in
total?  You definitely need to increase MAX_FSM_PAGES in
postgresql.conf, but I can't tell how far on the basis of this one
datapoint.

You will probably want to do a VACUUM FULL to clean out some of the free
space and then see where you really stand on number of pages.
		regards, tom lane 

"Michael G. Martin" <michael@vpmonline.com> writes:
> That's what I was beginning to think from reading.  I just wasn't sure
> of the correlation between MAX_FSM_PAGES and the actual pages occupied
> by the table after a full vacuum.  1:1 ?? Any general rules?

1:1 would be worst case, probably.  The real question is how many pages
get dirtied between vacuums --- so you can trade off more frequent
vacuums for a smaller free space map.

We don't have a lot of experience yet with tuning that parameter (if
we did, there'd be advice in the manual).  Please report anything
you learn from experimentation.

            regards, tom lane

Ok, I'll keep track of how many pages change between updates and play with the max_fsm_pages value.  I'll post my findings.

--Michael

Tom Lane wrote:

"Michael G. Martin" <michael@vpmonline.com> writes: 

That's what I was beginning to think from reading.  I just wasn't sure 
of the correlation between MAX_FSM_PAGES and the actual pages occupied 
by the table after a full vacuum.  1:1 ?? Any general rules?   

1:1 would be worst case, probably.  The real question is how many pages
get dirtied between vacuums --- so you can trade off more frequent
vacuums for a smaller free space map.

We don't have a lot of experience yet with tuning that parameter (if
we did, there'd be advice in the manual).  Please report anything
you learn from experimentation.
		regards, tom lane