Re: UnUsed Tuple Space Huge - Mailing list pgsql-admin

From Michael G. Martin
Subject Re: UnUsed Tuple Space Huge
Date
Msg-id 3D07D5E6.3080300@vpmonline.com
Whole thread Raw
In response to UnUsed Tuple Space Huge  ("Michael G. Martin" <michael@vpmonline.com>)
List pgsql-admin
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 

pgsql-admin by date:

Previous
From: "Michael G. Martin"
Date:
Subject: Re: UnUsed Tuple Space Huge
Next
From: Tom Lane
Date:
Subject: Re: UnUsed Tuple Space Huge