Re: VACUUM and fsm_max_pages

Hi Scott, hi List

Thank you for your answer.
I will try to launch one VACUUM FULL the next time, and I will continue
to execute VACUUM between two tests.

I increased max_fsm_pages until 1000000, but I think it's not a good
solution...

Regards,
Alexandra

Scott Marlowe wrote:

>On Fri, 2006-07-07 at 01:57, DANTE Alexandra wrote:
>
>
>>Good morning List,
>>
>>I have seen several posts on this concept but I don’t find a complete
>>response.
>>I’m using BenchmarkSQL to evaluate PostgreSQL in transaction processing
>>and I work with PostgreSQL 8.1.3 on RHEL4-AS, Itanium-2 processor, 8GB RAM.
>>
>>The database, generated via BenchmarkSQL and used, is a 200-warehouses
>>database and its size is about 20GB. The parameter “max_fsm_pages” is
>>equal to 20000 and “max_fsm_relations” to 1000.
>>
>>Between two benchs, I launch a VACUUM but at the end of it, I see that
>>PostgreSQL asks me to increase the “max_fsm_pages” parameters and the
>>value proposed grows with the number of VACUUM launched…
>>
>>Could someone explain me why ?
>>
>>This is an example of the message I have :
>>Free space map contains 20576 pages in 17 relations
>>A total of 20000 page slots are in use (including overhead)
>>128512 page slots are required to track all free space
>>Current limits are : 20000 page slots, 1000 relations, using 223 KB
>>Number of page slots needed (128512) exceeds max_fsm_pages (20000)
>>HINT : Consider increasing the config parameter “max_fsm_pages” to a
>>value over 128512.
>>
>>In order not to launch a VACUUM FULL, I increase the value of
>>“max_fsm_pages” but is it correct ?
>>
>>
>
>Man, I'm really wishing I'd make the time to revamp the vacuum docs like
>I promised. anyway...
>
>Anytime you see a constantly growing need for fsm pages, it's a sign
>that the fsm isn't big enough and / or the vacuums aren't frequent
>enough.
>
>If they are both big enough and often enough, then it's possible your
>I/O bandwidth isn't great enough for your load and vacuum needs. in
>which case the growth of the dead tuples in your store is outrunning
>your ability to reclaim them.
>
>Can you schedule ONE vacuum full to get the system back to something
>small enough? It may be that you've got so much bloat that your I/O
>system is now transferring way too much data and vacuum (plain, not
>full) can't keep up.
>
>What did you increase max_fsm_pages to?
>
>
>