Skip site navigation (1) Skip section navigation (2)

Re: "analyze" putting wrong reltuples in pg_class

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Ron Mayer <ron(at)intervideo(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: "analyze" putting wrong reltuples in pg_class
Date: 2002-08-04 00:45:48
Message-ID: 200208040045.g740jnQ13687@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Is there any way we can warn users when their fsm parameters are too
small?

---------------------------------------------------------------------------

Tom Lane wrote:
> Ron Mayer <ron(at)intervideo(dot)com> writes:
> > logs2=# select pgstattuple('e_ip_full');
> > NOTICE:  physical length: 293.84MB live tuples: 1697755 (169.26MB, 57.60%)
> > dead tuples: 0 (0.00MB, 0.00%) free/reusable space: 110.84MB (37.72%)
> > overhead: 4.67%
> >  pgstattuple
> > -------------
> >            0
> > (1 row)
> 
> 38% overhead space is awfully high.  I am betting that your max_fsm_pages
> configuration parameter needs to be kicked up --- it would seem that
> your system is failing to reclaim free space effectively.  (Check the
> mail list archives for recent discussions of this point.)
> 
> What I think is happening is that the free space is not evenly
> distributed but is concentrated near the start of the table.  This
> causes ANALYZE to make a faulty estimate of the average number of live
> tuples per page, because its initial scan will see mostly free space
> and not very many live tuples on the first few hundred pages.  So it
> extrapolates a too-small estimate for the total number of tuples.
> 
> It would probably be good at some point to make ANALYZE more robust,
> but your immediate problem is too much wasted space.  I'd recommend
> bumping up max_fsm_pages to some reasonable fraction of your total
> database size, and then doing a VACUUM FULL to get back the space leaked
> so far.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

Responses

pgsql-bugs by date

Next:From: the kay (efesar)Date: 2002-08-04 01:13:25
Subject: PG_DUMP / PG_RESTORE
Previous:From: Tom LaneDate: 2002-08-03 16:53:19
Subject: Re: "analyze" putting wrong reltuples in pg_class

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group