Hi,
In thread [1], we are discussing to expose WAL usage data for each
statement in a way quite similar to how we expose BufferUsage data.
The way it exposes seems reasonable to me and no one else raises any
objection. It could be that it appears fine to others who have
reviewed the patch but I thought it would be a good idea to write a
separate email just for its UI and see if anybody has objection.
It exposes three variables (a) wal_records (Number of WAL records
produced), (b) wal_num_fpw (Number of WAL full page image records),
(c) wal_bytes (size of WAL records produced).
The patch has exposed these three variables via explain (analyze, wal)
<statement>, auto_explain and pg_stat_statements.
Exposed via Explain
------------------------------------
Note the usage via line displaying WAL. This parameter may only be
used when ANALYZE is also enabled.
postgres=# explain (analyze, buffers, wal) update t1 set c2='cccc';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Update on t1 (cost=0.00..53.99 rows=1199 width=414) (actual
time=6.030..6.030 rows=0 loops=1)
Buffers: shared hit=2484 dirtied=44
WAL: records=2359 full page records=42 bytes=447788
-> Seq Scan on t1 (cost=0.00..53.99 rows=1199 width=414) (actual
time=0.040..0.540 rows=1199 loops=1)
Buffers: shared hit=42
Planning Time: 0.179 ms
Execution Time: 6.119 ms
(7 rows)
Exposed via auto_explain
------------------------------------------
Users need to set auto_explain.log_wal to print WAL usage statistics.
This parameter has no effect unless auto_explain.log_analyze is
enabled. Note the usage via line displaying WAL.
LOG: duration: 0.632 ms plan:
Query Text: update t1 set c2='cccc';
Update on t1 (cost=0.00..16.10 rows=610 width=414) (actual
time=0.629..0.629 rows=0 loops=1)
Buffers: shared hit=206 dirtied=5 written=2
WAL: records=200 full page records=2 bytes=37387
-> Seq Scan on t1 (cost=0.00..16.10 rows=610 width=414) (actual
time=0.022..0.069 rows=100 loops=1)
Buffers: shared hit=2 dirtied=1
Exposed via pg_stat_statements
------------------------------------------------
Three new parameters are added to pg_stat_statements function.
select query, wal_bytes, wal_records, wal_num_fpw from
pg_stat_statements where query like 'VACUUM%';
query | wal_bytes | wal_records | wal_num_fpw
--------------------------+-----------+-------------+-------------
VACUUM test | 72814331 | 8857 | 8855
Any objections/suggestions?
[1] - https://www.postgresql.org/message-id/CAB-hujrP8ZfUkvL5OYETipQwA%3De3n7oqHFU%3D4ZLxWS_Cza3kQQ%40mail.gmail.com
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com