Thread: Calculation for Max_FSM_pages : Any rules of thumb?
I just ran a vacuum verbose on the entire DB and this came out. number of page slots needed (274144) exceeds max_fsm_pages (153600) Hence, I've changed the max to 400,000 (pulled it straight out of the air). How does one calculate what's the number needed anyway? Another question is, based on what I've read in the archives (in my laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm basically screwed and will have to do a vacuum verbose FULL on the entire DB. Crap.. (I was playing with pgfouine and then I found the above piece of advice) I'm planning to run vacuum verbose full tonight/over the weekend. (is this sane?) Thanks for the advice..
Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote: > > I just ran a vacuum verbose on the entire DB and this came out. > > number of page slots needed (274144) exceeds max_fsm_pages (153600) > > Hence, I've changed the max to 400,000 (pulled it straight out of the > air). How does one calculate what's the number needed anyway? It's not simple. Every update or delete creates a "dead tuple" that needs to be tracked by an fsm entry. So it depends on how frequently your database is changing in between vacuum runs. In my experience, the best bet is to do vacuum verbose on a regular basis and get a feel for what you need. Every database load is different. > Another question is, based on what I've read in the archives (in my > laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm > basically screwed and will have to do a vacuum verbose FULL on the > entire DB. Crap.. You don't _need_ to. But it's generally a good idea to get table bloat reduced. > (I was playing with pgfouine and then I found the above piece of advice) > > I'm planning to run vacuum verbose full tonight/over the weekend. (is > this sane?) Thanks for the advice.. vacuum full is sane, if that's what you mean. The only problem is that it locks tables while working on them, so you have to take into account what other workload might be blocked while vacuum full is working, and how long vacuum full is liable to take. -- Bill Moran http://www.potentialtech.com
On Thu, 2007-11-01 at 20:56 -0400, Bill Moran wrote: > Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote: > > > > I just ran a vacuum verbose on the entire DB and this came out. > > > > number of page slots needed (274144) exceeds max_fsm_pages (153600) > > > > Hence, I've changed the max to 400,000 (pulled it straight out of the > > air). How does one calculate what's the number needed anyway? > > It's not simple. Every update or delete creates a "dead tuple" that > needs to be tracked by an fsm entry. So it depends on how frequently > your database is changing in between vacuum runs. Quite a lof actually. > > In my experience, the best bet is to do vacuum verbose on a regular > basis and get a feel for what you need. Every database load is > different. autovacuum is turned on by default.. so I didn't think of any issues _might_ occur.. (or rather.. didn't think about murphy's law) > > > Another question is, based on what I've read in the archives (in my > > laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm > > basically screwed and will have to do a vacuum verbose FULL on the > > entire DB. Crap.. > > You don't _need_ to. But it's generally a good idea to get table > bloat reduced. OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that I presume. > > I'm planning to run vacuum verbose full tonight/over the weekend. (is > > this sane?) Thanks for the advice.. > > vacuum full is sane, if that's what you mean. The only problem is that > it locks tables while working on them, so you have to take into account > what other workload might be blocked while vacuum full is working, and > how long vacuum full is liable to take. It's pulling data from the master DB (it's a data mart) every 50 to 120 seconds) I presume that it's blocked on a table by table basis??
Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that
> I presume.
Probably a lot more, and it'll bloat your indexes while it's at it.
Do you have a *reason* to run a vacuum full?
I'd suggest using contrib/pgstattuple to get a fix on how much dead
space there is in your tables. If it's really horrid (like more than
50%) then VACUUM FULL followed by REINDEX might be called for, but
otherwise you should probably not sweat it.
If you do have a problem you need to reconsider your regular vacuuming
policy, because it's not running often enough. See if autovacuum makes
sense for you.
Also, if you are not low on disk space overall, consider CLUSTER as a
substitute for VACUUM FULL + REINDEX. It'll be faster and you might get
a speed boost for subsequent queries using whichever index you cluster
on. The only drawback is that CLUSTER uses temp space equal to the
table + index sizes ...
regards, tom lane
On Thu, 2007-11-01 at 21:22 -0400, Tom Lane wrote: > Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes: > > OK.. Vacuum verbose took 2 hours.. Vacuum full will likely take 2x that > > I presume. > > Probably a lot more, and it'll bloat your indexes while it's at it. > Do you have a *reason* to run a vacuum full? Maybe you didn't read my original post. I did a vacuum verbose (playing with pgfouine) and found that my max_fsm_pages was exceeded and based on archives, I have to do a vacuum full. I decided to bite the bullet and go ahead with the vacuum full anyway. It's been ~3 hours already and I _think_ it's about to finish.. (is there a way to determine which tables are left to vacuum? Is there a _list_ which it transverse etc? tailing the vacuum_log, I can see where it's at, but not where it is in terms of overall status. > I'd suggest using contrib/pgstattuple to get a fix on how much dead > space there is in your tables. If it's really horrid (like more than > 50%) then VACUUM FULL followed by REINDEX might be called for, but > otherwise you should probably not sweat it. pgstattuple.. Okay.. anyone have a centos rpm for it? Don't really have access to a compiler on the (server) machine. > If you do have a problem you need to reconsider your regular vacuuming > policy, because it's not running often enough. See if autovacuum makes > sense for you. autovacuum _is_ running on a regular basis. (I'm not sure if it's supposed to catch the max_fsm pages being exceeded etc) > Also, if you are not low on disk space overall, consider CLUSTER as a > substitute for VACUUM FULL + REINDEX. It'll be faster and you might get > a speed boost for subsequent queries using whichever index you cluster > on. The only drawback is that CLUSTER uses temp space equal to the > table + index sizes ... I'm not low.. I have ~300G available. Total DB size is ~60G. I guess I need to read up on CLUSTER. Thanks.
2007/11/2, Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > pgstattuple.. Okay.. anyone have a centos rpm for it? Don't really have > access to a compiler on the (server) machine. don't you have postgresql-contrib package for centos? -- Filip Rembiałkowski
On Nov 1, 2007, at 8:51 PM, Ow Mun Heng wrote: > Another question is, based on what I've read in the archives (in my > laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm > basically screwed and will have to do a vacuum verbose FULL on the > entire DB. Crap.. I've seen this repeated many times as well, and I can't think of a really good reason why this should be true. Once you increase max fsm pages, won't the very next regular vacuum find all the free space in pages and add them to the map anyway? Ie, you've not "lost" any free space once the next regular vacuum runs. At worst, you've got a slightly bloated table because you allocated more pages rather than re- using some, but is that worth a full vacuum? I don't think it will be unless you're *way* under the fsm pages needed and have been for a long time.
In response to Vivek Khera <vivek@khera.org>: > > On Nov 1, 2007, at 8:51 PM, Ow Mun Heng wrote: > > > Another question is, based on what I've read in the archives (in my > > laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm > > basically screwed and will have to do a vacuum verbose FULL on the > > entire DB. Crap.. > > I've seen this repeated many times as well, and I can't think of a > really good reason why this should be true. It's not inherently true, it's just likely. > Once you increase max fsm > pages, won't the very next regular vacuum find all the free space in > pages and add them to the map anyway? Yes. > Ie, you've not "lost" any free > space once the next regular vacuum runs. At worst, you've got a > slightly bloated table because you allocated more pages rather than re- > using some, but is that worth a full vacuum? The situation you just described is the reason I recommend a full vacuum after such a situation has occurred. No, it's not required in all cases, but it's a lot easier to recommend than the research required to determine whether or not your table bloat is excessive enough to warrant it. If you can make the time to do the full vacuum, it's probably worth it, just for peace of mind. If it's difficult to schedule a full vacuum, then you need to carefully review various page usages to see if any individual tables are worth it and/or all kinds of careful consideration. As a result, I recommend a full vacuum, and if the person complains that they can't schedule it, _then_ I go into the details of how to figure out what else can/should be done. So I guess I'm recommending it to make my own life easier :) > I don't think it will be > unless you're *way* under the fsm pages needed and have been for a > long time. Frequently, when people ask for help because they've exceed max_fsm*, it's because they're not paying attention to their systems, and therefore the problem has been occurring for a while before it got so bad that they couldn't ignore it. As a result, a full vacuum is frequently a necessity. Folks who are monitoring their databases closely don't hit this problem nearly as often. -- Bill Moran http://www.potentialtech.com
On Thu, 2007-11-08 at 12:02 -0500, Bill Moran wrote: > Frequently, when people ask for help because they've exceed max_fsm*, > it's because they're not paying attention to their systems, and therefore > the problem has been occurring for a while before it got so bad that > they couldn't ignore it. As a result, a full vacuum is frequently a > necessity. > > Folks who are monitoring their databases closely don't hit this > problem nearly as often. > How does one monitor it closely anyway? the warning comes when one does a vacuum verbose and with autovacuum turned on, I don't even see it anywhere.
In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > > On Thu, 2007-11-08 at 12:02 -0500, Bill Moran wrote: > > Frequently, when people ask for help because they've exceed max_fsm*, > > it's because they're not paying attention to their systems, and therefore > > the problem has been occurring for a while before it got so bad that > > they couldn't ignore it. As a result, a full vacuum is frequently a > > necessity. > > > > Folks who are monitoring their databases closely don't hit this > > problem nearly as often. > > How does one monitor it closely anyway? the warning comes when one does > a vacuum verbose and with autovacuum turned on, I don't even see it > anywhere. 1) Run vacuum verbose from cron on a regular basis and have the output emailed to you. 2) Capture and graph (I use mrtg) various stats that would indicate to you that something is wrong. Some suggestions are graphing the output of pg_database_size(), various stuff captured from the pg_buffercache addon. I also graph transactions/second and other stats, but those are useful for detecting _other_ problems, unrelated to vacuuming. It's amazing to me how many people just throw up a database and expect it to just magically work forever. Actually, this isn't isolated to databases ... I've seen people with fileservers run around one day saying "the fileserver is full, someone delete some files!" If it's a fileserver, why aren't you monitoring disk usage so you see this coming? If it's a database server, you should be monitoring critical stats on it. Then you can throw out all those silly "rules of thumb" and use some actual data! -- Bill Moran http://www.potentialtech.com
On Tue, 2007-11-13 at 09:49 -0500, Bill Moran wrote:
> In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>:
> > How does one monitor it closely anyway? the warning comes when one does
> > a vacuum verbose and with autovacuum turned on, I don't even see it
> > anywhere.
>
> 1) Run vacuum verbose from cron on a regular basis and have the output
> emailed to you.
I'm doing this on a regular basis now coupled with pgfouine, I get a
nicely formatted HTML report. With the nightly vacuum, I noticed that I
can actually reduce my max_fsm_pages. (I raised it from 200,000 to
400,000 then to 800,000 currently, but with the regular vacuum, it's
gone down to 300,000 range)
> 2) Capture and graph (I use mrtg) various stats that would indicate to
> you that something is wrong. Some suggestions are graphing the
> output of pg_database_size(), various stuff captured from
> the pg_buffercache addon.
Currently I use cacti to monitor Disk Size (dedicated Raid), have yet to
play with pg_buffercache and needing more ideas to monitor. (anyone?)
tps is not very important to me, (I look more at cpu usage and load avg
as it's a (very!) low end server)
> I also graph transactions/second and
> other stats, but those are useful for detecting _other_ problems,
> unrelated to vacuuming.
Even with the regular vacuuming and even a vacuum full ( on my test DB)
I still see that perhaps something is wrong (from the below)
(I got this gem from the mailling list archives)
hmxmms=> SELECT
c.relname,
c.reltuples::bigint as rowcnt,
pg_stat_get_tuples_inserted(c.oid) AS inserted,
pg_stat_get_tuples_updated(c.oid) AS updated,
pg_stat_get_tuples_deleted(c.oid) AS deleted
FROM pg_class c
WHERE c.relkind = 'r'::"char"
GROUP BY c.oid, c.relname, c.reltuples
HAVING pg_stat_get_tuples_updated(c.oid) +
pg_stat_get_tuples_deleted(c.oid) > 1000
ORDER BY pg_stat_get_tuples_updated(c.oid) +
pg_stat_get_tuples_deleted(c.oid) DESC;
relname | rowcnt | inserted | updated | deleted
-----------------------+----------+----------+---------+----------
tst_r | 11971691 | 0 | 0 | 22390528 <--
pg_statistic | 1465 | 280 | 7716 | 153
dr_ns | 2305571 | 1959 | 0 | 1922
pg_attribute | 3787 | 1403 | 184 | 1292
No matter how many times I vacuum/full the deleted number still doesn't
go down.
In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > > Even with the regular vacuuming and even a vacuum full ( on my test DB) > I still see that perhaps something is wrong (from the below) > > (I got this gem from the mailling list archives) > hmxmms=> SELECT > c.relname, > c.reltuples::bigint as rowcnt, > pg_stat_get_tuples_inserted(c.oid) AS inserted, > pg_stat_get_tuples_updated(c.oid) AS updated, > pg_stat_get_tuples_deleted(c.oid) AS deleted > FROM pg_class c > WHERE c.relkind = 'r'::"char" > GROUP BY c.oid, c.relname, c.reltuples > HAVING pg_stat_get_tuples_updated(c.oid) + > pg_stat_get_tuples_deleted(c.oid) > 1000 > ORDER BY pg_stat_get_tuples_updated(c.oid) + > pg_stat_get_tuples_deleted(c.oid) DESC; > relname | rowcnt | inserted | updated | deleted > -----------------------+----------+----------+---------+---------- > tst_r | 11971691 | 0 | 0 | 22390528 <-- > pg_statistic | 1465 | 280 | 7716 | 153 > dr_ns | 2305571 | 1959 | 0 | 1922 > pg_attribute | 3787 | 1403 | 184 | 1292 > > No matter how many times I vacuum/full the deleted number still doesn't > go down. Are you sure you're interpreting that number correctly? I took it to mean a counter of the number of delete operations since server start. -- Bill Moran http://www.potentialtech.com
On Mon, 2007-11-19 at 08:24 -0500, Bill Moran wrote: > In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > > > > Even with the regular vacuuming and even a vacuum full ( on my test DB) > > I still see that perhaps something is wrong (from the below) > > > > (I got this gem from the mailling list archives) > > hmxmms=> SELECT > > c.relname, > > c.reltuples::bigint as rowcnt, > > pg_stat_get_tuples_inserted(c.oid) AS inserted, > > pg_stat_get_tuples_updated(c.oid) AS updated, > > pg_stat_get_tuples_deleted(c.oid) AS deleted > > FROM pg_class c > > WHERE c.relkind = 'r'::"char" > > GROUP BY c.oid, c.relname, c.reltuples > > HAVING pg_stat_get_tuples_updated(c.oid) + > > pg_stat_get_tuples_deleted(c.oid) > 1000 > > ORDER BY pg_stat_get_tuples_updated(c.oid) + > > pg_stat_get_tuples_deleted(c.oid) DESC; > > relname | rowcnt | inserted | updated | deleted > > -----------------------+----------+----------+---------+---------- > > tst_r | 11971691 | 0 | 0 | 22390528 <-- > > pg_statistic | 1465 | 280 | 7716 | 153 > > dr_ns | 2305571 | 1959 | 0 | 1922 > > pg_attribute | 3787 | 1403 | 184 | 1292 > > > > No matter how many times I vacuum/full the deleted number still doesn't > > go down. > > Are you sure you're interpreting that number correctly? I took it to > mean a counter of the number of delete operations since server start. > You are right. This is definitely a snafu in my interpretation. After I restarted PG on the laptop, the numbers went away. So, then I'm confused as to why the above "gem" was provided as a means to see which tables needs more vacumming. ANyway...
On Nov 20, 2007, at 6:14 PM, Ow Mun Heng wrote: > On Mon, 2007-11-19 at 08:24 -0500, Bill Moran wrote: >> In response to Ow Mun Heng <Ow.Mun.Heng@wdc.com>: >>> >>> Even with the regular vacuuming and even a vacuum full ( on my >>> test DB) >>> I still see that perhaps something is wrong (from the below) >>> >>> (I got this gem from the mailling list archives) >>> hmxmms=> SELECT >>> c.relname, >>> c.reltuples::bigint as rowcnt, >>> pg_stat_get_tuples_inserted(c.oid) AS inserted, >>> pg_stat_get_tuples_updated(c.oid) AS updated, >>> pg_stat_get_tuples_deleted(c.oid) AS deleted >>> FROM pg_class c >>> WHERE c.relkind = 'r'::"char" >>> GROUP BY c.oid, c.relname, c.reltuples >>> HAVING pg_stat_get_tuples_updated(c.oid) + >>> pg_stat_get_tuples_deleted(c.oid) > 1000 >>> ORDER BY pg_stat_get_tuples_updated(c.oid) + >>> pg_stat_get_tuples_deleted(c.oid) DESC; >>> relname | rowcnt | inserted | updated | deleted >>> -----------------------+----------+----------+---------+---------- >>> tst_r | 11971691 | 0 | 0 | 22390528 >>> <-- >>> pg_statistic | 1465 | 280 | 7716 | 153 >>> dr_ns | 2305571 | 1959 | 0 | 1922 >>> pg_attribute | 3787 | 1403 | 184 | 1292 >>> >>> No matter how many times I vacuum/full the deleted number still >>> doesn't >>> go down. >> >> Are you sure you're interpreting that number correctly? I took it to >> mean a counter of the number of delete operations since server start. Actually, it's not on server start; it's on stats reset. Which can happen at server start depending on your config. > You are right. This is definitely a snafu in my interpretation. > After I > restarted PG on the laptop, the numbers went away. So, then I'm > confused > as to why the above "gem" was provided as a means to see which tables > needs more vacumming. By itself it doesn't help; you need to track how many rows have been updated or deleted since the last time you vacuumed. That, along with the rowcount, will give you an idea of how much of the table is dead space. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828