Vacuuming anything zeroes shared table stats

Is vacuuming any table supposed to zero the statistics for all
shared tables? Doesn't that have implications for autovacuum? The
example below is in 8.2.4 but I'm seeing similar behavior in 8.1.9
and 8.3devel. Additionally, in 8.3devel doing anything that queries
or modifies a shared table seems to zero the statistics for all
shared tables.

test=> select relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
test-> from pg_stat_all_tables
test-> where relid in (select oid from pg_class where relisshared)
test-> order by relname;
relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
------------------+----------+----------+-----------+-----------+-----------
pg_auth_members | 25 | 3 | 1 | 0 | 1
pg_authid | 7 | 40 | 0 | 0 | 0
pg_database | 2 | 7 | 0 | 0 | 0
pg_pltemplate | 2 | 0 | 0 | 0 | 0
pg_shdepend | 0 | 4 | 2 | 0 | 2
pg_shdescription | 2 | 0 | 0 | 0 | 0
pg_tablespace | 2 | 0 | 0 | 0 | 0
pg_toast_1260 | 1 | 0 | 0 | 0 | 0
pg_toast_1262 | 1 | 0 | 0 | 0 | 0
pg_toast_2396 | 1 | 0 | 0 | 0 | 0
(10 rows)

test=> vacuum foo;
VACUUM

test=> select relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
test-> from pg_stat_all_tables
test-> where relid in (select oid from pg_class where relisshared)
test-> order by relname;
relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
------------------+----------+----------+-----------+-----------+-----------
pg_auth_members | 0 | 0 | 0 | 0 | 0
pg_authid | 0 | 0 | 0 | 0 | 0
pg_database | 1 | 0 | 0 | 0 | 0
pg_pltemplate | 0 | 0 | 0 | 0 | 0
pg_shdepend | 0 | 0 | 0 | 0 | 0
pg_shdescription | 0 | 0 | 0 | 0 | 0
pg_tablespace | 0 | 0 | 0 | 0 | 0
pg_toast_1260 | 0 | 0 | 0 | 0 | 0
pg_toast_1262 | 0 | 0 | 0 | 0 | 0
pg_toast_2396 | 0 | 0 | 0 | 0 | 0
(10 rows)

--
Michael Fuhr

Michael Fuhr wrote:
> Is vacuuming any table supposed to zero the statistics for all
> shared tables?

Huh, certainly not. In any case, I think the problem may be related to
the fact that stats for shared tables are kept in a separate hash from
regular tables.

I'll investigate the issue tomorrow -- thanks for reporting.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)

Michael Fuhr wrote:
> Is vacuuming any table supposed to zero the statistics for all
> shared tables? Doesn't that have implications for autovacuum? The
> example below is in 8.2.4 but I'm seeing similar behavior in 8.1.9
> and 8.3devel.

The problem is that the database hash is cleared of databases that no
longer exist, and the database list is constructed by scanning
pg_database. Since no entry exist for the database we use for shared
tables (InvalidOid), the hash table is dropped. The attached patch
fixes this.

> Additionally, in 8.3devel doing anything that queries or modifies a
> shared table seems to zero the statistics for all shared tables.

I'm not sure if this is fixed by the patch; can you verify, or provide a
more specific description of the problem?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> The problem is that the database hash is cleared of databases that no
> longer exist, and the database list is constructed by scanning
> pg_database. Since no entry exist for the database we use for shared
> tables (InvalidOid), the hash table is dropped.

Doh ...

> The attached patch fixes this.

Wouldn't it be easier to just special-case the shared DB in
pgstat_vacuum_tabstat?

while ((dbentry = (PgStat_StatDBEntry *) hash_seq_search(&hstat)) != NULL)
{
Oid dbid = dbentry->databaseid;

CHECK_FOR_INTERRUPTS();

- if (hash_search(htab, (void *) &dbid, HASH_FIND, NULL) == NULL)
+ /* ignore the DB entry for shared tables ... they never go away */
+ if (OidIsValid(dbid) &&
+ hash_search(htab, (void *) &dbid, HASH_FIND, NULL) == NULL)
pgstat_drop_database(dbid);
}

>> Additionally, in 8.3devel doing anything that queries or modifies a
>> shared table seems to zero the statistics for all shared tables.

> I'm not sure if this is fixed by the patch; can you verify, or provide a
> more specific description of the problem?

Seems unlikely that this bug would explain a behavior like that.

regards, tom lane

On Thu, Jun 07, 2007 at 11:41:56AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > The attached patch fixes this.
>
> Wouldn't it be easier to just special-case the shared DB in
> pgstat_vacuum_tabstat?

Thanks; I'll test these patches when I get a chance.

> >> Additionally, in 8.3devel doing anything that queries or modifies a
> >> shared table seems to zero the statistics for all shared tables.
>
> > I'm not sure if this is fixed by the patch; can you verify, or provide a
> > more specific description of the problem?
>
> Seems unlikely that this bug would explain a behavior like that.

Further investigation shows that what really seems to be happening
in 8.3devel is that the statistics for shared tables are reset every
15 seconds when autovacuum is enabled, which it is by default. I
don't observe this phenomenon when autovacuum is disabled. Here's
a test case:

select * from pg_pltemplate;

select seq_scan, idx_scan, now()
from pg_stat_all_tables where relname in ('pg_pltemplate');

Repeat the second select until the statistics for pg_pltemplate
become zero, which should be within 15 seconds. Repeating the
experiment should reveal a 15-second cycle of statistics resets.

In case this behavior is platform-dependent I'm testing on Solaris 9
sparc.

--
Michael Fuhr

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> Further investigation shows that what really seems to be happening
> in 8.3devel is that the statistics for shared tables are reset every
> 15 seconds when autovacuum is enabled, which it is by default. I
> don't observe this phenomenon when autovacuum is disabled.

OK, so it's just that pgstat_vacuum_tabstat() gets run by autovacuum.
So either form of Alvaro's patch should fix it.

regards, tom lane

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > Further investigation shows that what really seems to be happening
> > in 8.3devel is that the statistics for shared tables are reset every
> > 15 seconds when autovacuum is enabled, which it is by default. I
> > don't observe this phenomenon when autovacuum is disabled.
>
> OK, so it's just that pgstat_vacuum_tabstat() gets run by autovacuum.
> So either form of Alvaro's patch should fix it.

Right. Committed.

Thanks for the report!

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)