Обсуждение: FSM - per database or per installation?
Are the FSM parameters for each database, or the entire Postgres system? In other words, if I have 100 databases, do I needto increase max_fsm_pages and max_fsm_relations by a factor of 100, or keep them the same as if I just have one database? I suspect they're per-database, i.e. as I add databases, I don't have to increase the FSM parameters, but the documentationisn't 100% clear on this point. Thanks, Craig
Craig James wrote: > Are the FSM parameters for each database, or the entire Postgres > system? In other words, if I have 100 databases, do I need to increase > max_fsm_pages and max_fsm_relations by a factor of 100, or keep them the > same as if I just have one database? > > I suspect they're per-database, i.e. as I add databases, I don't have to > increase the FSM parameters, but the documentation isn't 100% clear on > this point. It's per cluster, ie *not* per-database. The parameter is gone in 8.4, BTW. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Craig James wrote: >> Are the FSM parameters for each database, or the entire Postgres >> system? In other words, if I have 100 databases, do I need to increase >> max_fsm_pages and max_fsm_relations by a factor of 100, or keep them the >> same as if I just have one database? >> >> I suspect they're per-database, i.e. as I add databases, I don't have to >> increase the FSM parameters, but the documentation isn't 100% clear on >> this point. > > It's per cluster, ie *not* per-database. Hmmm ... it seems I have an impossible problem. I have ~250 databases each with about 2500 relations (as in "select count(1)from pg_class where relname not like 'pg_%'"). That makes roughly 625,000 relations. But ... for max_fsm_pages, the Postgres manual says, "This setting must be at least 16 * max_fsm_relations. The default ischosen by initdb depending on the amount of available memory, and can range from 20k to 200k pages." So max_fsm_pages should be 16*625000, or 10,000,000 ... except that the limit is 200,000. Or is it only the *default* thatcan be 200,000 max, but you can override and set it to any number you like? It appears that Postgres 8.3 and earlier can't do garbage collection on a configuration like mine. Do I misunderstand something? > The parameter is gone in 8.4, BTW. Both max_fsm_relations and max_fsm_pages? Thanks, Craig
On Wed, Dec 23, 2009 at 6:38 PM, Craig James <craig_james@emolecules.com> wrote: > Heikki Linnakangas wrote: >> >> Craig James wrote: >>> >>> Are the FSM parameters for each database, or the entire Postgres >>> system? In other words, if I have 100 databases, do I need to increase >>> max_fsm_pages and max_fsm_relations by a factor of 100, or keep them the >>> same as if I just have one database? >>> >>> I suspect they're per-database, i.e. as I add databases, I don't have to >>> increase the FSM parameters, but the documentation isn't 100% clear on >>> this point. >> >> It's per cluster, ie *not* per-database. > > Hmmm ... it seems I have an impossible problem. I have ~250 databases each > with about 2500 relations (as in "select count(1) from pg_class where > relname not like 'pg_%'"). That makes roughly 625,000 relations. > > But ... for max_fsm_pages, the Postgres manual says, "This setting must be > at least 16 * max_fsm_relations. The default is chosen by initdb depending > on the amount of available memory, and can range from 20k to 200k pages." > > So max_fsm_pages should be 16*625000, or 10,000,000 ... except that the > limit is 200,000. Or is it only the *default* that can be 200,000 max, but > you can override and set it to any number you like? NO! that's not the max (if it was I would be in serious trouble.) That's the max that you'll see done by initdb when creating the cluster. We run 10M fsm pages on our servers, and use about 2.5M of that.
Craig James wrote: > Heikki Linnakangas wrote: > >The parameter is gone in 8.4, BTW. > > Both max_fsm_relations and max_fsm_pages? Yes, both are gone. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 20/11/2009 2:33 AM, Heikki Linnakangas wrote: > Craig James wrote: >> Are the FSM parameters for each database, or the entire Postgres >> system? In other words, if I have 100 databases, do I need to increase >> max_fsm_pages and max_fsm_relations by a factor of 100, or keep them the >> same as if I just have one database? >> >> I suspect they're per-database, i.e. as I add databases, I don't have to >> increase the FSM parameters, but the documentation isn't 100% clear on >> this point. > > It's per cluster, ie *not* per-database. > > The parameter is gone in 8.4, BTW. See: http://www.postgresql.org/docs/8.4/static/release-8-4.html#AEN95067 for why they've been removed, which boils down to "PostgreSQL manages the fsm automatically now and no longer requires all that RAM to do it, either". Thanks Heikki - the fsm _really_ simplify admin and remove a bunch of common gotchas for Pg users. -- Craig Ringer