Re: Session WAL activity

Hello.

At Tue, 3 Dec 2019 18:01:28 +0300, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote in
> 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.

It seems to be useful to me. We also might want statistics of other
session IOs.  In that case the table name would be
"pg_stat_session/process_activity". We are aleady collecting most
kinds of the IO activity but it loses session information...

Briefly looking the patch, I have some comments on it.

As mentioned above, if we are intending future exantion of the
session-stats table, the name should be changed.

Backend status is more appropriate than PGPROC. See pgstat.c.

Some kind of locking is needed to update the fields on shared segment.
(LWLocks for PGPROC and PGSTAT_BEGIN/END_WRITE_ACTIVITY for
PgBackendStatus)

Knitpickings:

The patch contains a trace of older trial in
pg_stat_get_activity. Proc OID should be >= 8000 in
patches. src/include/catalog/unused_oids offers some OID for you.


regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center