Re: [PERFORM] FSM - per database or per installation?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] FSM - per database or per installation?
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? 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] FSM - per database or per installation?
On Wed, Dec 23, 2009 at 6:38 PM, Craig James craig_ja...@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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] FSM - per database or per installation?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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 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. Thanks, Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] FSM - per database or per installation?
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance