Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?

[Decibel!]

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 <[EMAIL PROTECTED]>:

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
-----------------------+----------+----------+---------+----------

 pg_statistic          |     1465 |      280 |    7716 |      153
 dr_ns                 |  2305571 |     1959 |       0 |     1922
 pg_attribute          |     3787 |     1403 |     184 |     1292


go down.

Even with the regular vacuuming and even a vacuum full ( on my test DB) tst_r | 11971691 | 0 | 0 | 22390528 <-- No matter how many times I vacuum/full the deleted number still doesn't

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.

--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828

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.

smime.p7s
Description: S/MIME cryptographic signature