Re: Postgres performance slowly gets worse over a month

I believe, from a quick glance at the file vacuum.c, the first number is the total amount of free pages the database now occupies.  The second number is how much is actually available for new tuples to be created in.

--Michael

Gaetano Mendola wrote:

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

Check this value in the postgresql.con file:

max_fsm_pages = 100000

I had the same problem with the db growing, index no longer being used,
despite vacuums each night.  Somewhere, there is a thread on this.

Anyway, If you look at the vacuum stats each time your run vacuum, looks
to see how many pages are being updated between vacuums--i looked at the
removed x tuples in y pages value.  Then, set this value to be greater
than the number of pages changed between vacuums.  If more pages are
being updated between vacuums than what max_fsm_pages allows, the extra
pages won't be marked to be re-used--from what I understand.  This then
results in the db growing and the optimizer starts to chose full table
scans since the db spans so many pages on the disk--at least this is
what happened in my db.   

Can you explain me this line that I obatin in the log
after a vacuum analyze ?
--Relation ua_user_data_exp--
2002-07-21 05:00:02 [28492]  DEBUG:  Pages 1402: Changed 2, reaped 1192,
Empty 0, New 0; Tup 4277: Vac 16207, Keep/VTL 0/0, Crash 0, UnUsed 1, MinLen
393, MaxLen 680; Re-using: Free/Avail. Space 9148944/9141356;
EndEmpty/Avail. Pages 0/1191. CPU 0.00s/0.03u sec.

I'm wondering about "Re-using: Free/Avail. Space 9148944/9141356"


Ciao
Gaetano