Re: [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.

On Thu, Jun 07, 2007 at 11:46:17PM +0100, Gregory Stark wrote:
> Does anything use the pgstats data for anything other than presenting feedback
> to users?
>
> Autovacuum uses it to estimate when tables should be vacuumed right? This
> wouldn't have caused autovacuum to go nuts vacuuming these tables would it?
> But I doubt even then that it could consume much i/o bandwidth.

I discovered this problem after noticing that pg_shdepend had gotten
horribly bloated -- apparently due to heavy use of temporary tables
by an application whose performance I was investigating -- despite
autovacuum being enabled. When I looked at the statistics for
pg_shdepend the values for n_tup_{ins,upd,del} were much lower than
I expected. After watching the numbers grow for a few minutes I
saw them reset; after observing this behavior several times I was
able to correlate the resets with vacuums of other tables.

Since the statistics for pg_shdepend rarely got high enough to
trigger an autovacuum that table was almost never being vacuumed.
I suggested to the DBA that he vacuum it manually; after five minutes
the vacuum completed and the application's performance improved
immediately.

INFO: "pg_shdepend": found 8475403 removable, 3907 nonremovable row versions in 76783 pages

--
Michael Fuhr