Hi hackers,
One of our customers complains about that some sessions generates "too
much WAL records".
Certainly WAL activity doesn't indicate a problem itself: huge workload
cause huge WAL activity.
But them are trying to understand which clients produces so much
database changes and complain that there is
no way to get such information in Postgres. For example in Oracle this
problems can be solved in this way:
http://www.dba-oracle.com/t_find_session_generating_high_redo.htm
Unfortunately there is actually no simple and accurate way to calculate
amount of WAL produced by the particular session.
It is possible to parse WAL (for example using pg_waldump), then using
XID->pid mapping accumulate size of transactions produced by each backend.
But this is very inconvenient and not DBA friendly approach.
I have implemented small patch which collects such statistic.
I have added walWritten field to PGPROC and increment it in
CopyXLogRecordToWAL.
It is possible to inspect this field using pg_stat_get_wal_activity(pid)
function and also I have added
pg_stat_wal_activity which just adds wal_written to standard
pg_activity view:
postgres=# select pid, backend_type, wal_written from pg_stat_wal_activity ;
pid | backend_type | wal_written
------+------------------------------+-------------
4405 | autovacuum launcher | 0
4407 | logical replication launcher | 0
4750 | client backend | 86195
4403 | background writer | 204
4402 | checkpointer | 328
4404 | walwriter | 0
(6 rows)
I wonder if such extra statistic about session WAL activity is
considered to be useful?
The only problem with this approach from my point of view is adding 8
bytes to PGPROC.
But there are already so many fields in this structure
(sizeof(PGPROC)=816), that adding yet another 8 bytes should not be
noticeable.
Comments are welcome.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 5f0ee50..c985b04 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1486,6 +1486,9 @@ CopyXLogRecordToWAL(int write_len, bool isLogSwitch, XLogRecData *rdata,
currpos = GetXLogBuffer(CurrPos);
freespace = INSERT_FREESPACE(CurrPos);
+ /* Accumulate amount of data written to WAL for pg_xact_activity */
+ MyProc->walWritten += write_len;
+
/*
* there should be enough space for at least the first field (xl_tot_len)
* on this page.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 000cff3..037d313 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -758,6 +758,9 @@ CREATE VIEW pg_stat_activity AS
LEFT JOIN pg_database AS D ON (S.datid = D.oid)
LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
+CREATE VIEW pg_stat_wal_activity AS
+ SELECT a.*,pg_stat_get_wal_activity(a.pid) as wal_written FROM pg_stat_activity a;
+
CREATE VIEW pg_stat_replication AS
SELECT
S.pid,
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index fff0628..bf6ab34 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -390,6 +390,8 @@ InitProcess(void)
MyPgXact->xid = InvalidTransactionId;
MyPgXact->xmin = InvalidTransactionId;
MyProc->pid = MyProcPid;
+ MyProc->walWritten = 0;
+
/* backendId, databaseId and roleId will be filled in later */
MyProc->backendId = InvalidBackendId;
MyProc->databaseId = InvalidOid;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 05240bf..b3acba5 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -546,7 +546,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
Datum
pg_stat_get_activity(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_ACTIVITY_COLS 29
+
+ #define PG_STAT_GET_ACTIVITY_COLS 29
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -919,6 +920,22 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
+pg_stat_get_wal_activity(PG_FUNCTION_ARGS)
+{
+ int32 pid = PG_GETARG_INT32(0);
+ PGPROC* proc = BackendPidGetProc(pid);
+ if (proc == NULL)
+ {
+ proc = AuxiliaryPidGetProc(pid);
+ }
+ if (proc == NULL)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_INT64(proc->walWritten);
+}
+
+
+Datum
pg_backend_pid(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyProcPid);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b759b15..6d31afd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5152,6 +5152,10 @@
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,sslcompression,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc}',
prosrc => 'pg_stat_get_activity' },
+{ oid => '2121', descr => 'statistics: WAL activity',
+ proname => 'pg_stat_get_wal_activity', provolatile => 's', proisstrict => 't',
+ proparallel => 'r', prorettype => 'int8', proargtypes => 'int4',
+ prosrc => 'pg_stat_get_wal_activity' },
{ oid => '3318',
descr => 'statistics: information about progress of backends running maintenance command',
proname => 'pg_stat_get_progress_info', prorows => '100', proretset => 't',
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index 281e1db..16859bf 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -203,6 +203,11 @@ struct PGPROC
PGPROC *lockGroupLeader; /* lock group leader, if I'm a member */
dlist_head lockGroupMembers; /* list of members, if I'm a leader */
dlist_node lockGroupLink; /* my member link, if I'm a member */
+
+ /*
+ * Amount of data written to the WAL by this process
+ */
+ uint64 walWritten;
};
/* NOTE: "typedef struct PGPROC PGPROC" appears in storage/lock.h. */
