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
Attachments:
vacuum-resetshared.patchtext/x-diff; charset=us-asciiDownload
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.158
diff -c -p -r1.158 pgstat.c
*** src/backend/postmaster/pgstat.c 27 May 2007 17:28:35 -0000 1.158
--- src/backend/postmaster/pgstat.c 7 Jun 2007 15:25:30 -0000
*************** pgstat_vacuum_tabstat(void)
*** 897,902 ****
--- 897,906 ----
* Collect the OIDs of either all databases or all tables, according to
* the parameter, into a temporary hash table. Caller should hash_destroy
* the result when done with it.
+ *
+ * NB -- this function adds an entry with InvalidOid if asked for a list of
+ * databases. This is because we use a shared table for such a database to
+ * keep stats for shared tables.
* ----------
*/
static HTAB *
*************** pgstat_collect_oids(Oid catalogid)
*** 930,935 ****
--- 934,950 ----
heap_endscan(scan);
heap_close(rel, AccessShareLock);
+ /*
+ * If we were asked for databases, add an entry for the pseudo-database
+ * with InvalidOid, where we store shared tables.
+ */
+ if (catalogid == DatabaseRelationId)
+ {
+ Oid invalid = InvalidOid;
+
+ hash_search(htab, (void *) &invalid, HASH_ENTER, NULL);
+ }
+
return htab;
}
Alvaro Herrera <alvherre@commandprompt.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@commandprompt.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@fuhr.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@fuhr.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)