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 | 1292No 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 | 1292No 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 | 1292No 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