UnUsed Tuple Space Huge
UnUsed Tuple Space Huge
От:
"Michael G. Martin" <michael@vpmonline.com>
Дата:
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
Re: UnUsed Tuple Space Huge
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Michael G. Martin" 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
Re: UnUsed Tuple Space Huge
От:
"Michael G. Martin" <michael@vpmonline.com>
Дата:
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:
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
Re: UnUsed Tuple Space Huge
От:
"Michael G. Martin" <michael@vpmonline.com>
Дата:
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:
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
Re: UnUsed Tuple Space Huge
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Michael G. Martin" 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
Re: UnUsed Tuple Space Huge
От:
"Michael G. Martin" <michael@vpmonline.com>
Дата:
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
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