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 | 04407 | logical replication launcher | 04750 | client backend | 861954403 | background writer | 2044402 | checkpointer | 3284404 | 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