pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robin Iddon <robin@edesix.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Date: Thu, 23 Feb 2006 23:34:22 +0000
Message-ID: <43FE467E.9090204@edesix.com> (raw)
In-Reply-To: <26139.1140730764@sss.pgh.pa.us>
References: <43FD7312.5080701@edesix.com>
	<43FDF046.2050609@zeut.net>
	<43FDFF9A.90603@edesix.com>
	<43FE0723.3070608@edesix.com>
	<26139.1140730764@sss.pgh.pa.us>

Tom,

Tom Lane wrote:

>
>Quite incorrect --- if that were so, there would be no dead tuples
>for vacuum to remove, hm?  Actually ANALYZE does an
>update-or-insert-if-not-present fandango.
>
>  
>
Thanks for the correction - I was basing my comments on the fact that 
the tuple count was just climbing for the pg_statistics table, but I did 
wonder why the system would do that deliberately - answer is, it doesn't!.

>I'm sort of mystified by your report.  I can think of several ways that
>the system might fail to notice that pg_statistic needs vacuuming, but
>none of them seem to actually occur in the current code, and I don't see
>any relevant difference in the CVS logs (I admit to being too lazy to
>build an exact 8.0.3 version to test --- but 8.0.7 seems fine).
>Can you keep an eye on pg_statistic's entry in the pg_stat_all_tables
>  
>
>view for each affected database, and confirm whether the n_tup_upd/
>n_tup_del counts are rising over time or not?  That would at least
>narrow down the problem a bit.
>
>  
>
As a baseline I see:

imes=# select * from pg_stat_all_tables where relname = 'pg_statistic';
 relid | schemaname |   relname    | seq_scan | seq_tup_read | idx_scan 
| idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------
 16408 | pg_catalog | pg_statistic |        0 |            0 |  4421694 
|       4273043 |         0 |   2120401 |         0
(1 row)


Now I do:

imes=# analyze;
ANALYZE
imes=# select * from pg_stat_all_tables where relname = 'pg_statistic';
 relid | schemaname |   relname    | seq_scan | seq_tup_read | idx_scan 
| idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------
 16408 | pg_catalog | pg_statistic |        0 |            0 |  4423073 
|       4274484 |         0 |   2121631 |         0
(1 row)

 From that I see that the pg_statistics table is never inserted/deleted 
only updated (ins/del counts are 0), and in principle simply analyzing 
doesn't increase the tuple count.

Yet the reltuples in the pg_class table grows, as does the disk space 
used for the pg_statistics table.  What makes it grow?  Is it running 
queries while analyze is running causing new row versions to be created 
and then not getting cleaned up?

I wonder if Matt's suggestion on the vacuum scale is not in fact the 
real issue here.  Because my scale is set to 2 (the default) but the 
number of inserts is always zero (unless we create new tables/indexes, I 
think the number of active stats stays the same, no?) and the number of 
updates is at most equal to the number of tuples, we can never get 
enough changes to trigger a vacuum (which would require 2x the number of 
updates).  Now any removable rows won't ever get removed.

In fact I just noticed that the number of stats tuples just climbed from 
1236 to 2634.  The ins/del counts are still zero.  I ran analyze and the 
update counter went up only by 1232.  For pg_autovacuum to vacuum this 
table I need (2*2634)+1000 = 6268 updates, which is never going to happen.

So I'm going to run with a fractional value of scale for a while and see 
if it makes things any better.

Thanks for all the help,
Robin





view thread (13+ messages)  latest in thread

Message-ID: <43FE467E.9090204@edesix.com>
Permalink:  ../43FE467E.9090204@edesix.com/
Also on:    postgresql.org/message-id/43FE467E.9090204@edesix.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: robin@edesix.com, tgl@sss.pgh.pa.us
  Subject: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
  In-Reply-To: <43FE467E.9090204@edesix.com>
  References: <43FD7312.5080701@edesix.com> <43FDF046.2050609@zeut.net> <43FDFF9A.90603@edesix.com> <43FE0723.3070608@edesix.com> <26139.1140730764@sss.pgh.pa.us> <43FE467E.9090204@edesix.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox