Re: [pgsql-hackers] fsm_ variables ...

Marc,

> Just thought of something after reading and deleting Gavin's email ...
> don't we have a 'pgtune' utility, or wasn't that something someone was
> working?  how many settings, like fsm, can be determined by analzying a
> database?

Well, Justin started writing something (see pg_autotune on GBorg) but it: a)
was in C and b) was based on tuning for the hardware using pgbench and not
tuning for the database.

I started something doing Q&A in Perl (e.g. "How large do you expect your
database to be?") but quickly ran into the issue that I didn't have good
mathematical formulas for most settings, just rules-of-thumb (and a perusal
of the Performance list for the last month will show that a lot of these
rules-of-thumb were based on incorrect assumptions, even if they work).

max_fsm_pages actually becomes *very* easy if you're running pg_autovacuum.
If your vacuum threshold is set to, for example, 50% updates, then you set
max_fsm_pages to about 50% of the pages you have on disk (obtainable from
pg_class). However, this has a couple of issues with doing *automatically*
without user input:
1) this may require increasing SHMMAX/SHMALL, which requires a reboot on some
systems, and root access on all systems;
2) fsm_pages can only be set at server start, so if the user expects the
database to grow dramatically over time, FSM needs to be allocated based on
the expected maximum size of the DB, not on the current size;

Plus there's the fact that some database applications should not use
autovacuum and will turn it off.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco