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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
