Обсуждение: max_fsm_relations
I just did a Vacuum Analyze on a DB. It worked OK, but I got...
NOTICE: max_fsm_relations(1000) equals the number of relations checked
HINT: You have at least 1000 relations. Consider increasing the configuration parameter “max_fsm_relations”
I browsed around and learned that this has to do with keeping track of free disk space.
Q: Is there a problem with leaving this alone? How serious is this if it is a problem?
Q: Is there a way I can shrink this number (reload the data to consume the free space perhaps?)
Thanks
-dave
On Fri, 2009-02-27 at 12:37 -0700, Gauthier, Dave wrote: > I just did a Vacuum Analyze on a DB. It worked OK, but I got... > > > > > > NOTICE: max_fsm_relations(1000) equals the number of relations checked > > HINT: You have at least 1000 relations. Consider increasing the > configuration parameter “max_fsm_relations” > > > > I browsed around and learned that this has to do with keeping track of > free disk space. > > Q: Is there a problem with leaving this alone? How serious is this if > it is a problem? > > Q: Is there a way I can shrink this number (reload the data to consume > the free space perhaps?) This is "relations" which means tables, indexes etc... So unless you start dropping things, no you can't reduce it. Just increase it a bit (say 20%) it won't hurt you. Note it does use a little shared_memory,. Joshua D. Drake > > > > Thanks > > -dave > > > > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
In response to "Gauthier, Dave" <dave.gauthier@intel.com>: > I just did a Vacuum Analyze on a DB. It worked OK, but I got... > > > NOTICE: max_fsm_relations(1000) equals the number of relations checked > HINT: You have at least 1000 relations. Consider increasing the configuration parameter "max_fsm_relations" Then it didn't work OK. > I browsed around and learned that this has to do with keeping track of free disk space. > Q: Is there a problem with leaving this alone? How serious is this if it is a problem? Vacuum is probably not doing all that it needs to. Which means some tables will grow until they use up all your disk space. > Q: Is there a way I can shrink this number (reload the data to consume the free space perhaps?) Drop some databases, tables, sequences, etc. That will reduce the number of relations that PG has to track. Or, just raise the parameter and restart postgres. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
There is no way I have 1000 tables/indexes. But maybe it's counting table/index file extensions in the mix? What's themetadata query to see these 1000 relations? -dave -----Original Message----- From: Bill Moran [mailto:wmoran@potentialtech.com] Sent: Friday, February 27, 2009 3:03 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] max_fsm_relations In response to "Gauthier, Dave" <dave.gauthier@intel.com>: > I just did a Vacuum Analyze on a DB. It worked OK, but I got... > > > NOTICE: max_fsm_relations(1000) equals the number of relations checked > HINT: You have at least 1000 relations. Consider increasing the configuration parameter "max_fsm_relations" Then it didn't work OK. > I browsed around and learned that this has to do with keeping track of free disk space. > Q: Is there a problem with leaving this alone? How serious is this if it is a problem? Vacuum is probably not doing all that it needs to. Which means some tables will grow until they use up all your disk space. > Q: Is there a way I can shrink this number (reload the data to consume the free space perhaps?) Drop some databases, tables, sequences, etc. That will reduce the number of relations that PG has to track. Or, just raise the parameter and restart postgres. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
In response to "Gauthier, Dave" <dave.gauthier@intel.com>: > There is no way I have 1000 tables/indexes. But maybe it's counting table/index file extensions in the mix? What's themetadata query to see these 1000 relations? Are you counting tables, indexes, sequences, pg_toast tables, system tables? SELECT relname,reltype from pg_class; Make sure you do that query for every database and add them up. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Ya, most of it's system stuff. OK, I see where the 1000 comes from. I bumped it up to 1200 in postgresql.conf. Is therea way I can spin that in without rebooting the DB (and kicking my user off)? -dave -----Original Message----- From: Bill Moran [mailto:wmoran@potentialtech.com] Sent: Friday, February 27, 2009 4:00 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] max_fsm_relations In response to "Gauthier, Dave" <dave.gauthier@intel.com>: > There is no way I have 1000 tables/indexes. But maybe it's counting table/index file extensions in the mix? What's themetadata query to see these 1000 relations? Are you counting tables, indexes, sequences, pg_toast tables, system tables? SELECT relname,reltype from pg_class; Make sure you do that query for every database and add them up. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
In response to "Gauthier, Dave" <dave.gauthier@intel.com>: > Ya, most of it's system stuff. OK, I see where the 1000 comes from. I bumped it up to 1200 in postgresql.conf. Is therea way I can spin that in without rebooting the DB (and kicking my user off)? No. Unless something has changed that I'm not aware of, you have to do a restart for PG to change that value, as it allocates the memory on startup. Unless you're expecting a considerable amount of table bloat in the near future, you can just reset the value, then schedule the server to restart during your next off-peak time. The server's not going to meltdown immediately (unless you've got a _TON_ of update activity) -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Fri, 27 Feb 2009, Gauthier, Dave wrote:
> Is there a way I can spin that in without rebooting the DB (and kicking
> my user off)?
Nope:
# select name,context from pg_settings where name='max_fsm_pages';
name | context
---------------+------------
max_fsm_pages | postmaster
That's the definitive way to answer questions like this. See "When they
take effect" at
http://www.postgresqldocs.org/wiki/Tuning_Your_PostgreSQL_Server for a
quick description of what the context stuff means. "postmaster" means you
need to restart the server, in this case (like many of the other
parameters in that class) because the parameter changes a shared memory
allocation, which is only done at startup.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Bill Moran <wmoran@potentialtech.com> writes:
> In response to "Gauthier, Dave" <dave.gauthier@intel.com>:
>> There is no way I have 1000 tables/indexes. But maybe it's counting table/index file extensions in the mix? What's
themetadata query to see these 1000 relations?
> Are you counting tables, indexes, sequences, pg_toast tables, system tables?
I think sequences don't count here, but toast tables and system catalogs
surely do. Also, as you said, it's the total across all databases in
the installation that counts.
regards, tom lane
For the time being, I dropped a few tables in a scratch DB that I was experimenting with. I just reran the app that
gaveme the messages before and this time no messages. Tonight, I'll cycle the DB with the new fsm value.
Thanks for all the help!
(BTW, just have to say that the help I get here is faster and better than what we used to pay for from Oracle)
-dave
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 27, 2009 4:53 PM
To: Bill Moran
Cc: Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] max_fsm_relations
Bill Moran <wmoran@potentialtech.com> writes:
> In response to "Gauthier, Dave" <dave.gauthier@intel.com>:
>> There is no way I have 1000 tables/indexes. But maybe it's counting table/index file extensions in the mix? What's
themetadata query to see these 1000 relations?
> Are you counting tables, indexes, sequences, pg_toast tables, system tables?
I think sequences don't count here, but toast tables and system catalogs
surely do. Also, as you said, it's the total across all databases in
the installation that counts.
regards, tom lane