[PATCH] Statistics collection for CLUSTER command
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.
I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.
Attachments:
clusterstats.patchtext/x-patch; name=clusterstats.patchDownload
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 979,984 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 979,989 ----
daemon</entry>
</row>
<row>
+ <entry><structfield>last_cluster</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Last time at which <command>CLUSTER</> was issued on this table</entry>
+ </row>
+ <row>
<entry><structfield>vacuum_count</></entry>
<entry><type>bigint</></entry>
<entry>Number of times this table has been manually vacuumed
***************
*** 1001,1006 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1006,1016 ----
<entry>Number of times this table has been analyzed by the autovacuum
daemon</entry>
</row>
+ <row>
+ <entry><structfield>cluster_count</></entry>
+ <entry><type>bigint</></entry>
+ <entry>Number of times <command>CLUSTER</> has been issued on this table</entry>
+ </row>
</tbody>
</tgroup>
</table>
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 410,419 **** CREATE VIEW pg_stat_all_tables AS
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
--- 410,421 ----
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_cluster_time(C.oid) as last_cluster,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
! pg_stat_get_cluster_count(C.oid) AS cluster_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***************
*** 35,40 ****
--- 35,41 ----
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "optimizer/planner.h"
+ #include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
***************
*** 407,412 **** cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
--- 408,417 ----
verbose);
/* NB: rebuild_relation does heap_close() on OldHeap */
+
+ /* Report CLUSTER to the stats collector, but not VACUUM FULL */
+ if (indexOid != InvalidOid)
+ pgstat_report_cluster(OldHeap);
}
/*
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 292,297 **** static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
--- 292,298 ----
static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ static void pgstat_recv_cluster(PgStat_MsgCluster *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
***************
*** 1365,1370 **** pgstat_report_analyze(Relation rel,
--- 1366,1392 ----
}
/* --------
+ * pgstat_report_cluster() -
+ *
+ * Tell the collector about the table we just CLUSTERed.
+ * --------
+ */
+ void
+ pgstat_report_cluster(Relation rel)
+ {
+ PgStat_MsgCluster msg;
+
+ if (pgStatSock == PGINVALID_SOCKET)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_CLUSTER);
+ msg.m_databaseid = rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId;
+ msg.m_tableoid = RelationGetRelid(rel);
+ msg.m_clustertime = GetCurrentTimestamp();
+ pgstat_send(&msg, sizeof(msg));
+ }
+
+ /* --------
* pgstat_report_recovery_conflict() -
*
* Tell the collector about a Hot Standby recovery conflict.
***************
*** 3246,3251 **** PgstatCollectorMain(int argc, char *argv[])
--- 3268,3277 ----
pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
break;
+ case PGSTAT_MTYPE_CLUSTER:
+ pgstat_recv_cluster((PgStat_MsgCluster *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_BGWRITER:
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
break;
***************
*** 3466,3471 **** pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
--- 3492,3499 ----
result->analyze_count = 0;
result->autovac_analyze_timestamp = 0;
result->autovac_analyze_count = 0;
+ result->cluster_timestamp = 0;
+ result->cluster_count = 0;
}
return result;
***************
*** 4537,4542 **** pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
--- 4565,4572 ----
tabentry->analyze_count = 0;
tabentry->autovac_analyze_timestamp = 0;
tabentry->autovac_analyze_count = 0;
+ tabentry->cluster_timestamp = 0;
+ tabentry->cluster_count = 0;
}
else
{
***************
*** 4836,4841 **** pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
--- 4866,4895 ----
/* ----------
+ * pgstat_recv_cluster() -
+ *
+ * Process a CLUSTER message.
+ * ----------
+ */
+ static void
+ pgstat_recv_cluster(PgStat_MsgCluster *msg, int len)
+ {
+ PgStat_StatDBEntry *dbentry;
+ PgStat_StatTabEntry *tabentry;
+
+ /*
+ * Store the data in the table's hashtable entry.
+ */
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ tabentry = pgstat_get_tab_entry(dbentry, msg->m_tableoid, true);
+
+ tabentry->cluster_timestamp = msg->m_clustertime;
+ tabentry->cluster_count++;
+ }
+
+
+ /* ----------
* pgstat_recv_bgwriter() -
*
* Process a BGWRITER message.
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 41,50 **** extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
--- 41,52 ----
extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_cluster_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_total_time(PG_FUNCTION_ARGS);
***************
*** 386,391 **** pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
--- 388,411 ----
}
Datum
+ pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = tabentry->cluster_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+ }
+
+ Datum
pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
***************
*** 446,451 **** pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)
--- 466,486 ----
}
Datum
+ pg_stat_get_cluster_count(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->cluster_count);
+
+ PG_RETURN_INT64(result);
+ }
+
+ Datum
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
{
Oid funcid = PG_GETARG_OID(0);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2607,2612 **** DATA(insert OID = 2783 ( pg_stat_get_last_analyze_time PGNSP PGUID 12 1 0 0 0 f
--- 2607,2614 ----
DESCR("statistics: last manual analyze time for a table");
DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_autoanalyze_time _null_ _null_ _null_ ));
DESCR("statistics: last auto analyze time for a table");
+ DATA(insert OID = 3178 ( pg_stat_get_last_cluster_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_cluster_time _null_ _null_ _null_ ));
+ DESCR("statistics: last CLUSTER time for a table");
DATA(insert OID = 3054 ( pg_stat_get_vacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_vacuum_count _null_ _null_ _null_ ));
DESCR("statistics: number of manual vacuums for a table");
DATA(insert OID = 3055 ( pg_stat_get_autovacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autovacuum_count _null_ _null_ _null_ ));
***************
*** 2615,2620 **** DATA(insert OID = 3056 ( pg_stat_get_analyze_count PGNSP PGUID 12 1 0 0 0 f f f
--- 2617,2624 ----
DESCR("statistics: number of manual analyzes for a table");
DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autoanalyze_count _null_ _null_ _null_ ));
DESCR("statistics: number of auto analyzes for a table");
+ DATA(insert OID = 3179 ( pg_stat_get_cluster_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_cluster_count _null_ _null_ _null_ ));
+ DESCR("statistics: number of CLUSTERs for a table");
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
DESCR("statistics: currently active backend IDs");
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 44,49 **** typedef enum StatMsgType
--- 44,50 ----
PGSTAT_MTYPE_AUTOVAC_START,
PGSTAT_MTYPE_VACUUM,
PGSTAT_MTYPE_ANALYZE,
+ PGSTAT_MTYPE_CLUSTER,
PGSTAT_MTYPE_BGWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
***************
*** 336,341 **** typedef struct PgStat_MsgVacuum
--- 337,355 ----
/* ----------
+ * PgStat_MsgCluster Sent by the backend after CLUSTER
+ * ----------
+ */
+ typedef struct PgStat_MsgCluster
+ {
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ Oid m_tableoid;
+ TimestampTz m_clustertime;
+ } PgStat_MsgCluster;
+
+
+ /* ----------
* PgStat_MsgAnalyze Sent by the backend or autovacuum daemon
* after ANALYZE
* ----------
***************
*** 590,595 **** typedef struct PgStat_StatTabEntry
--- 604,611 ----
PgStat_Counter analyze_count;
TimestampTz autovac_analyze_timestamp; /* autovacuum initiated */
PgStat_Counter autovac_analyze_count;
+ TimestampTz cluster_timestamp;
+ PgStat_Counter cluster_count;
} PgStat_StatTabEntry;
***************
*** 776,781 **** extern void pgstat_report_vacuum(Oid tableoid, bool shared,
--- 792,798 ----
PgStat_Counter tuples);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ extern void pgstat_report_cluster(Relation rel);
extern void pgstat_report_recovery_conflict(int reason);
extern void pgstat_report_deadlock(void);
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1631,1640 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
--- 1631,1642 ----
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
+ | pg_stat_get_last_cluster_time(c.oid) AS last_cluster, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count, +
! | pg_stat_get_cluster_count(c.oid) AS cluster_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
***************
*** 1726,1735 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
--- 1728,1739 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
***************
*** 1769,1778 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
--- 1773,1784 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.
Done.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/08/2013 01:52 PM, Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.
whould be a bit easier to diagnose if we knew your community account name ;)
Stefan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/08/2013 07:57 PM, Stefan Kaltenbrunner wrote:
On 08/08/2013 01:52 PM, Vik Fearing wrote:
I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.whould be a bit easier to diagnose if we knew your community account name
Sorry, it's "glaucous".
Vik
On 08/08/2013 02:26 PM, Fabien COELHO wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help
appreciated.Done.
Thank you, but it seems you've duplicated the title from the other patch
(and thanks for adding that one, too!).
https://commitfest.postgresql.org/action/patch_view?id=1190
Vik
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Thank you, but it seems you've duplicated the title from the other patch
(and thanks for adding that one, too!).
Indeed, possibly a wrong copy paste. Fixed.
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/09/2013 12:02 AM, Vik Fearing wrote:
On 08/08/2013 07:57 PM, Stefan Kaltenbrunner wrote:
On 08/08/2013 01:52 PM, Vik Fearing wrote:
I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.whould be a bit easier to diagnose if we knew your community account name
Sorry, it's "glaucous".
hmm looks like your account may be affected by one of the buglets
introduced (and fixed shortly afterwards) of the main infrastructure to
debian wheezy - please try logging in to the main website and change
your password at least once. That should make it working again for the
commitfest app...
Stefan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/09/2013 10:37 PM, Stefan Kaltenbrunner wrote:
On 08/08/2013 01:52 PM, Vik Fearing wrote:
I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.hmm looks like your account may be affected by one of the buglets
introduced (and fixed shortly afterwards) of the main infrastructure to
debian wheezy - please try logging in to the main website and change
your password at least once. That should make it working again for the
commitfest app...
That worked. Thank you.
Vik
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
(2013/08/08 20:52), Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help appreciated.
I have reviewed the patch.
Succeeded to build with the latest HEAD, and passed the regression
tests.
Looks good enough, and I'd like to add a test case here, not only
for the view definition, but also working correctly.
Please take a look at attached one.
Regards,
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
Attachments:
clusterstats_regress.patchtext/plain; charset=Shift_JIS; name=clusterstats_regress.patchDownload
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 56bace1..ba614b2 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -28,7 +28,13 @@ SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
- (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
+ coalesce(t.last_vacuum, now()) AS last_vacuum,
+ coalesce(t.last_analyze, now()) AS last_analyze,
+ coalesce(t.last_cluster, now()) AS last_cluster,
+ t.vacuum_count,
+ t.analyze_count,
+ t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
@@ -111,4 +117,27 @@ SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
t | t
(1 row)
+-- table maintenance stats
+ANALYZE tenk2;
+VACUUM tenk2;
+CLUSTER tenk2 USING tenk2_unique1;
+SELECT pg_sleep(1.0);
+ pg_sleep
+----------
+
+(1 row)
+
+SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------+----------+----------
+ t | t | t | t | t | t
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index bb349b2..71e5e27 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -22,7 +22,13 @@ SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
- (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+ (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
+ coalesce(t.last_vacuum, now()) AS last_vacuum,
+ coalesce(t.last_analyze, now()) AS last_analyze,
+ coalesce(t.last_cluster, now()) AS last_cluster,
+ t.vacuum_count,
+ t.analyze_count,
+ t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
@@ -81,4 +87,20 @@ SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
+-- table maintenance stats
+ANALYZE tenk2;
+VACUUM tenk2;
+CLUSTER tenk2 USING tenk2_unique1;
+
+SELECT pg_sleep(1.0);
+
+SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+
-- End of Stats Test
On 09/16/2013 08:26 AM, Satoshi Nagayasu wrote:
(2013/08/08 20:52), Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.I would add this to the next commitfest but I seem to be unable to log
in with my community account (I can log in to the wiki). Help
appreciated.I have reviewed the patch.
Thank you for your review.
Succeeded to build with the latest HEAD, and passed the regression
tests.Looks good enough, and I'd like to add a test case here, not only
for the view definition, but also working correctly.Please take a look at attached one.
Looks good to me. Attached is a rebased patch with those tests added.
--
Vik
Attachments:
clusterstats.v2.patchtext/x-patch; name=clusterstats.v2.patchDownload
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 979,984 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 979,989 ----
daemon</entry>
</row>
<row>
+ <entry><structfield>last_cluster</></entry>
+ <entry><type>timestamp with time zone</></entry>
+ <entry>Last time at which <command>CLUSTER</> was issued on this table</entry>
+ </row>
+ <row>
<entry><structfield>vacuum_count</></entry>
<entry><type>bigint</></entry>
<entry>Number of times this table has been manually vacuumed
***************
*** 1001,1006 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1006,1016 ----
<entry>Number of times this table has been analyzed by the autovacuum
daemon</entry>
</row>
+ <row>
+ <entry><structfield>cluster_count</></entry>
+ <entry><type>bigint</></entry>
+ <entry>Number of times <command>CLUSTER</> has been issued on this table</entry>
+ </row>
</tbody>
</tgroup>
</table>
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 410,419 **** CREATE VIEW pg_stat_all_tables AS
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
--- 410,421 ----
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
+ pg_stat_get_last_cluster_time(C.oid) as last_cluster,
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
! pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
! pg_stat_get_cluster_count(C.oid) AS cluster_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
*** a/src/backend/commands/cluster.c
--- b/src/backend/commands/cluster.c
***************
*** 35,40 ****
--- 35,41 ----
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "optimizer/planner.h"
+ #include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
***************
*** 407,412 **** cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose,
--- 408,417 ----
verbose);
/* NB: rebuild_relation does heap_close() on OldHeap */
+
+ /* Report CLUSTER to the stats collector, but not VACUUM FULL */
+ if (indexOid != InvalidOid)
+ pgstat_report_cluster(OldHeap);
}
/*
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 292,297 **** static void pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, in
--- 292,298 ----
static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ static void pgstat_recv_cluster(PgStat_MsgCluster *msg, int len);
static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
***************
*** 1385,1390 **** pgstat_report_analyze(Relation rel,
--- 1386,1412 ----
}
/* --------
+ * pgstat_report_cluster() -
+ *
+ * Tell the collector about the table we just CLUSTERed.
+ * --------
+ */
+ void
+ pgstat_report_cluster(Relation rel)
+ {
+ PgStat_MsgCluster msg;
+
+ if (pgStatSock == PGINVALID_SOCKET)
+ return;
+
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_CLUSTER);
+ msg.m_databaseid = rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId;
+ msg.m_tableoid = RelationGetRelid(rel);
+ msg.m_clustertime = GetCurrentTimestamp();
+ pgstat_send(&msg, sizeof(msg));
+ }
+
+ /* --------
* pgstat_report_recovery_conflict() -
*
* Tell the collector about a Hot Standby recovery conflict.
***************
*** 3266,3271 **** PgstatCollectorMain(int argc, char *argv[])
--- 3288,3297 ----
pgstat_recv_analyze((PgStat_MsgAnalyze *) &msg, len);
break;
+ case PGSTAT_MTYPE_CLUSTER:
+ pgstat_recv_cluster((PgStat_MsgCluster *) &msg, len);
+ break;
+
case PGSTAT_MTYPE_BGWRITER:
pgstat_recv_bgwriter((PgStat_MsgBgWriter *) &msg, len);
break;
***************
*** 3486,3491 **** pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
--- 3512,3519 ----
result->analyze_count = 0;
result->autovac_analyze_timestamp = 0;
result->autovac_analyze_count = 0;
+ result->cluster_timestamp = 0;
+ result->cluster_count = 0;
}
return result;
***************
*** 4558,4563 **** pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
--- 4586,4593 ----
tabentry->analyze_count = 0;
tabentry->autovac_analyze_timestamp = 0;
tabentry->autovac_analyze_count = 0;
+ tabentry->cluster_timestamp = 0;
+ tabentry->cluster_count = 0;
}
else
{
***************
*** 4857,4862 **** pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
--- 4887,4916 ----
/* ----------
+ * pgstat_recv_cluster() -
+ *
+ * Process a CLUSTER message.
+ * ----------
+ */
+ static void
+ pgstat_recv_cluster(PgStat_MsgCluster *msg, int len)
+ {
+ PgStat_StatDBEntry *dbentry;
+ PgStat_StatTabEntry *tabentry;
+
+ /*
+ * Store the data in the table's hashtable entry.
+ */
+ dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+ tabentry = pgstat_get_tab_entry(dbentry, msg->m_tableoid, true);
+
+ tabentry->cluster_timestamp = msg->m_clustertime;
+ tabentry->cluster_count++;
+ }
+
+
+ /* ----------
* pgstat_recv_bgwriter() -
*
* Process a BGWRITER message.
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 41,50 **** extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
--- 41,52 ----
extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_cluster_count(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_function_total_time(PG_FUNCTION_ARGS);
***************
*** 386,391 **** pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
--- 388,411 ----
}
Datum
+ pg_stat_get_last_cluster_time(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ TimestampTz result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = tabentry->cluster_timestamp;
+
+ if (result == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(result);
+ }
+
+ Datum
pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
***************
*** 446,451 **** pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)
--- 466,486 ----
}
Datum
+ pg_stat_get_cluster_count(PG_FUNCTION_ARGS)
+ {
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->cluster_count);
+
+ PG_RETURN_INT64(result);
+ }
+
+ Datum
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
{
Oid funcid = PG_GETARG_OID(0);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2607,2612 **** DATA(insert OID = 2783 ( pg_stat_get_last_analyze_time PGNSP PGUID 12 1 0 0 0 f
--- 2607,2614 ----
DESCR("statistics: last manual analyze time for a table");
DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_autoanalyze_time _null_ _null_ _null_ ));
DESCR("statistics: last auto analyze time for a table");
+ DATA(insert OID = 3178 ( pg_stat_get_last_cluster_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_cluster_time _null_ _null_ _null_ ));
+ DESCR("statistics: last CLUSTER time for a table");
DATA(insert OID = 3054 ( pg_stat_get_vacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_vacuum_count _null_ _null_ _null_ ));
DESCR("statistics: number of manual vacuums for a table");
DATA(insert OID = 3055 ( pg_stat_get_autovacuum_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autovacuum_count _null_ _null_ _null_ ));
***************
*** 2615,2620 **** DATA(insert OID = 3056 ( pg_stat_get_analyze_count PGNSP PGUID 12 1 0 0 0 f f f
--- 2617,2624 ----
DESCR("statistics: number of manual analyzes for a table");
DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autoanalyze_count _null_ _null_ _null_ ));
DESCR("statistics: number of auto analyzes for a table");
+ DATA(insert OID = 3179 ( pg_stat_get_cluster_count PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_cluster_count _null_ _null_ _null_ ));
+ DESCR("statistics: number of CLUSTERs for a table");
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
DESCR("statistics: currently active backend IDs");
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 44,49 **** typedef enum StatMsgType
--- 44,50 ----
PGSTAT_MTYPE_AUTOVAC_START,
PGSTAT_MTYPE_VACUUM,
PGSTAT_MTYPE_ANALYZE,
+ PGSTAT_MTYPE_CLUSTER,
PGSTAT_MTYPE_BGWRITER,
PGSTAT_MTYPE_FUNCSTAT,
PGSTAT_MTYPE_FUNCPURGE,
***************
*** 336,341 **** typedef struct PgStat_MsgVacuum
--- 337,355 ----
/* ----------
+ * PgStat_MsgCluster Sent by the backend after CLUSTER
+ * ----------
+ */
+ typedef struct PgStat_MsgCluster
+ {
+ PgStat_MsgHdr m_hdr;
+ Oid m_databaseid;
+ Oid m_tableoid;
+ TimestampTz m_clustertime;
+ } PgStat_MsgCluster;
+
+
+ /* ----------
* PgStat_MsgAnalyze Sent by the backend or autovacuum daemon
* after ANALYZE
* ----------
***************
*** 590,595 **** typedef struct PgStat_StatTabEntry
--- 604,611 ----
PgStat_Counter analyze_count;
TimestampTz autovac_analyze_timestamp; /* autovacuum initiated */
PgStat_Counter autovac_analyze_count;
+ TimestampTz cluster_timestamp;
+ PgStat_Counter cluster_count;
} PgStat_StatTabEntry;
***************
*** 776,781 **** extern void pgstat_report_vacuum(Oid tableoid, bool shared,
--- 792,798 ----
PgStat_Counter tuples);
extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ extern void pgstat_report_cluster(Relation rel);
extern void pgstat_report_recovery_conflict(int reason);
extern void pgstat_report_deadlock(void);
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1631,1640 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
--- 1631,1642 ----
| pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, +
| pg_stat_get_last_analyze_time(c.oid) AS last_analyze, +
| pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, +
+ | pg_stat_get_last_cluster_time(c.oid) AS last_cluster, +
| pg_stat_get_vacuum_count(c.oid) AS vacuum_count, +
| pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, +
| pg_stat_get_analyze_count(c.oid) AS analyze_count, +
! | pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count, +
! | pg_stat_get_cluster_count(c.oid) AS cluster_count +
| FROM ((pg_class c +
| LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) +
| LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
***************
*** 1726,1735 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
--- 1728,1739 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, +
***************
*** 1769,1778 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
--- 1773,1784 ----
| pg_stat_all_tables.last_autovacuum, +
| pg_stat_all_tables.last_analyze, +
| pg_stat_all_tables.last_autoanalyze, +
+ | pg_stat_all_tables.last_cluster, +
| pg_stat_all_tables.vacuum_count, +
| pg_stat_all_tables.autovacuum_count, +
| pg_stat_all_tables.analyze_count, +
! | pg_stat_all_tables.autoanalyze_count, +
! | pg_stat_all_tables.cluster_count +
| FROM pg_stat_all_tables +
| WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_stat_xact_all_tables | SELECT c.oid AS relid, +
*** a/src/test/regress/expected/stats.out
--- b/src/test/regress/expected/stats.out
***************
*** 28,34 **** SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
! (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
--- 28,40 ----
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
! (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
! coalesce(t.last_vacuum, now()) AS last_vacuum,
! coalesce(t.last_analyze, now()) AS last_analyze,
! coalesce(t.last_cluster, now()) AS last_cluster,
! t.vacuum_count,
! t.analyze_count,
! t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
***************
*** 111,114 **** SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
--- 117,143 ----
t | t
(1 row)
+ -- table maintenance stats
+ ANALYZE tenk2;
+ VACUUM tenk2;
+ CLUSTER tenk2 USING tenk2_unique1;
+ SELECT pg_sleep(1.0);
+ pg_sleep
+ ----------
+
+ (1 row)
+
+ SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
+ ----------+----------+----------+----------+----------+----------
+ t | t | t | t | t | t
+ (1 row)
+
-- End of Stats Test
*** a/src/test/regress/sql/stats.sql
--- b/src/test/regress/sql/stats.sql
***************
*** 22,28 **** SELECT pg_sleep(2.0);
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
! (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
--- 22,34 ----
CREATE TEMP TABLE prevstats AS
SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
(b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
! (b.idx_blks_read + b.idx_blks_hit) AS idx_blks,
! coalesce(t.last_vacuum, now()) AS last_vacuum,
! coalesce(t.last_analyze, now()) AS last_analyze,
! coalesce(t.last_cluster, now()) AS last_cluster,
! t.vacuum_count,
! t.analyze_count,
! t.cluster_count
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
***************
*** 81,84 **** SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
--- 87,106 ----
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ -- table maintenance stats
+ ANALYZE tenk2;
+ VACUUM tenk2;
+ CLUSTER tenk2 USING tenk2_unique1;
+
+ SELECT pg_sleep(1.0);
+
+ SELECT st.last_vacuum > pr.last_vacuum,
+ st.last_analyze > pr.last_analyze,
+ st.last_cluster > pr.last_cluster,
+ st.vacuum_count > pr.vacuum_count,
+ st.analyze_count > pr.analyze_count,
+ st.cluster_count > pr.cluster_count
+ FROM pg_stat_user_tables AS st, prevstats pr
+ WHERE st.relname='tenk2';
+
-- End of Stats Test
(2013/08/08 20:52), Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.
Adding new fields to PgStat_StatTabEntry imposes a substantial distributed
cost, because every database stats file write-out grows by the width of those
fields times the number of tables in the database. Associated costs have been
and continue to be a pain point with large table counts:
/messages/by-id/1718942738eb65c8407fcd864883f4c8@fuzzy.cz
/messages/by-id/52268887.9010509@uptime.jp
In that light, I can't justify widening PgStat_StatTabEntry by 9.5% for this.
I recommend satisfying this monitoring need in your application by creating a
cluster_table wrapper function that issues CLUSTER and then updates statistics
you store in an ordinary table. Issue all routine CLUSTERs by way of that
wrapper function. A backend change that would help here is to extend event
triggers to cover the CLUSTER command, permitting you to inject monitoring
after plain CLUSTER and dispense with the wrapper.
Thanks,
nm
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Noah Misch <noah@leadboat.com> writes:
wrapper function. A backend change that would help here is to extend event
triggers to cover the CLUSTER command, permitting you to inject monitoring
after plain CLUSTER and dispense with the wrapper.
I didn't look in any level of details, but it might be as simple as
moving the T_ClusterStmt case from standard_ProcessUtility() down to the
Event Trigger friendly part known as ProcessUtilitySlow().
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Oct 20, 2013 at 1:37 AM, Noah Misch <noah@leadboat.com> wrote:
(2013/08/08 20:52), Vik Fearing wrote:
As part of routine maintenance monitoring, it is interesting for us to
have statistics on the CLUSTER command (timestamp of last run, and
number of runs since stat reset) like we have for (auto)ANALYZE and
(auto)VACUUM. Patch against today's HEAD attached.Adding new fields to PgStat_StatTabEntry imposes a substantial distributed
cost, because every database stats file write-out grows by the width of those
fields times the number of tables in the database. Associated costs have been
and continue to be a pain point with large table counts:/messages/by-id/1718942738eb65c8407fcd864883f4c8@fuzzy.cz
/messages/by-id/52268887.9010509@uptime.jpIn that light, I can't justify widening PgStat_StatTabEntry by 9.5% for this.
I recommend satisfying this monitoring need in your application by creating a
cluster_table wrapper function that issues CLUSTER and then updates statistics
you store in an ordinary table. Issue all routine CLUSTERs by way of that
wrapper function. A backend change that would help here is to extend event
triggers to cover the CLUSTER command, permitting you to inject monitoring
after plain CLUSTER and dispense with the wrapper.
I unfortunately have to agree with this, but I think it points to the
need for further work on the pgstat infrastructure. We used to have
one file; now we have one per database. That's better for people with
lots of databases, but many people just have one big database. We
need a solution here that relieves the pain for those people.
(I can't help thinking that the root of the problem here is that we're
rewriting the whole file, and that any solution that doesn't somehow
facilitate updates of individual records will be only a small
improvement.)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers