Обсуждение: VACUUM and fsm_max_pages

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 ?

Thank you for your help.
Regards,
Alexandra DANTE

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…

Oh, and if you can backup your database and import it into a test
server, see how much smaller your new data/base directory is over the
one on your production server.  That'll give you an idea of how bloated
your database is.  10 to 30% larger is fine.  100 to 1000% larger is
bad.  You get the idea.

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?
>
>
>