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(at)vpmonline(dot)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
>
>