On Fri, 2003-09-12 at 09:35, Bruce Momjian wrote:
> Matthew T. O'Connor wrote:
> > I made a patch to fix this, but in testing it I noticed that the stats
> > system doesn't work on shared tables as I was expecting it too (as my
> > latest patch requires it too :-). It treats instances of shared tables
> > in separate databases as totally unique tables. This makes it hard to
> > know how much activity has really gone on for a shared table.
> >
> > Is the behavior of the following example expected / desired?
>
> I suspect is just a bug because no one noticed it before. As I
> understand it, the stats system is recorded per-database. We could add
> stuff so the global tables are only recorded in template1 or perhaps
> record in template1 but report template1's numbers for all databases.
OK, well as we wait on the fix for the stats system, let me submit my
patch for pg_autovacuum. This patch assumes that the stats system will
be fixed so that all inserts, updates and deletes performed on shared
tables reguardless of what database those commands were executed from,
will show up in the stats shown in each database.
Even if this the stats system isn't fixed, this patch still is much
better about monitoring system tables that aren't shared, so it's an
improvement no matter what.
Matthew T. O'Connor
*** pg_autovacuum.c 2003-09-12 02:28:38.012400601 -0400
--- /home/matthew/downloads/pg_autovacuum.c 2003-09-12 02:42:35.891499368 -0400
***************
*** 118,130 ****
new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
new_tbl->relpages = atoi(PQgetvalue(res, row, PQfnumber(res, "relpages")));
- log_entry(PQgetvalue(res, row, PQfnumber(res, "relisshared")));
-
- if(strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
- new_tbl->relisshared = 0;
- else
- new_tbl->relisshared = 1;
-
new_tbl->analyze_threshold =
args->analyze_base_threshold + args->analyze_scaling_factor * new_tbl->reltuples;
new_tbl->vacuum_threshold =
--- 118,123 ----
***************
*** 220,226 ****
* both remove tables from the list that no longer exist and add
* tables to the list that are new
*/
! res = send_query((char *) TABLE_STATS_QUERY, dbi);
t = PQntuples(res);
/*
--- 213,219 ----
* both remove tables from the list that no longer exist and add
* tables to the list that are new
*/
! res = send_query(query_table_stats(dbi), dbi);
t = PQntuples(res);
/*
***************
*** 360,366 ****
{
sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
! sprintf(logbuffer, " relfilenode: %i; relisshared: %i", tbl->relfilenode, tbl->relisshared);
log_entry(logbuffer);
sprintf(logbuffer, " reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages);
log_entry(logbuffer);
--- 353,359 ----
{
sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
! sprintf(logbuffer, " relfilenode: %i", tbl->relfilenode);
log_entry(logbuffer);
sprintf(logbuffer, " reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages);
log_entry(logbuffer);
***************
*** 595,601 ****
res = send_query("VACUUM", dbi);
/* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
- /* FIXME: We should also reset all table stats since we just vacuumed every table */
PQclear(res);
return 1;
}
--- 588,593 ----
***************
*** 698,703 ****
--- 690,707 ----
/* Begninning of misc Functions */
+
+ char *
+ query_table_stats(db_info * dbi)
+ {
+ if (!strcmp(dbi->dbname, "template1")) /* Use template1 to
+ * monitor the system
+ * tables */
+ return (char *) TABLE_STATS_ALL;
+ else
+ return (char *) TABLE_STATS_USER;
+ }
+
/* Perhaps add some test to this function to make sure that the stats we need are available */
PGconn *
db_connect(db_info * dbi)
***************
*** 749,757 ****
if (NULL == dbi->conn)
return NULL;
- if (args->debug >= 4)
- log_entry(query);
-
res = PQexec(dbi->conn, query);
if (!res)
--- 753,758 ----
***************
*** 1054,1060 ****
if (0 == xid_wraparound_check(dbs));
{
! res = send_query((char *) TABLE_STATS_QUERY, dbs); /* Get an updated
* snapshot of this dbs
* table stats */
for (j = 0; j < PQntuples(res); j++)
--- 1055,1061 ----
if (0 == xid_wraparound_check(dbs));
{
! res = send_query(query_table_stats(dbs), dbs); /* Get an updated
* snapshot of this dbs
* table stats */
for (j = 0; j < PQntuples(res); j++)
***************
*** 1086,1096 ****
*/
if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold)
{
! /* if relisshared = t and database != template1 then only do an analyze */
! if((tbl->relisshared > 0) && (strcmp("template1",dbs->dbname)))
! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
! else
! snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
if (args->debug >= 1)
{
sprintf(logbuffer, "Performing: %s", buf);
--- 1087,1093 ----
*/
if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold)
{
! snprintf(buf, sizeof(buf), "VACUUM ANALYZE \"%s\"", tbl->table_name);
if (args->debug >= 1)
{
sprintf(logbuffer, "Performing: %s", buf);
***************
*** 1104,1110 ****
}
else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >= tbl->analyze_threshold)
{
! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
if (args->debug >= 1)
{
sprintf(logbuffer, "Performing: %s", buf);
--- 1101,1107 ----
}
else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >= tbl->analyze_threshold)
{
! snprintf(buf, sizeof(buf), "ANALYZE \"%s\"", tbl->table_name);
if (args->debug >= 1)
{
sprintf(logbuffer, "Performing: %s", buf);
*** pg_autovacuum.h 2003-09-12 02:06:11.140629744 -0400
--- /home/matthew/downloads/pg_autovacuum.h 2003-09-12 02:42:47.531599510 -0400
***************
*** 34,41 ****
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1
! #define TABLE_STATS_QUERY "select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'"
!
#define FRONTEND
#define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
--- 34,41 ----
#define VACUUM_ANALYZE 0
#define ANALYZE_ONLY 1
! #define TABLE_STATS_ALL "select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.relfilenode=b.relid"
! #define TABLE_STATS_USER "select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_user_tables b where a.relfilenode=b.relid"
#define FRONTEND
#define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
#define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
***************
*** 86,92 ****
*table_name;
int relfilenode,
reltuples,
- relisshared,
relpages;
long analyze_threshold,
vacuum_threshold;
--- 86,91 ----
***************
*** 133,138 ****
--- 132,138 ----
static PGconn *db_connect(db_info * dbi);
static void db_disconnect(db_info * dbi);
static PGresult *send_query(const char *query, db_info * dbi);
+ static char *query_table_stats(db_info * dbi);
/* Other Generally needed Functions */
static void daemonize(void);
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
