Hi hackers,
I think that it's currently not always possible to determine how many times
logical_decoding_work_mem has been reached.
For example, say a transaction is made of 40 subtransactions, and I get:
slot_name | spill_txns | spill_count | total_txns
--------------+------------+-------------+------------
logical_slot | 40 | 41 | 1
(1 row)
Then I know that logical_decoding_work_mem has been reached one time (total_txns).
But as soon as another transaction is decoded (that does not involve spilling):
slot_name | spill_txns | spill_count | total_txns
--------------+------------+-------------+------------
logical_slot | 40 | 41 | 2
(1 row)
Then we don't know if logical_decoding_work_mem has been reached one or two
times.
Please find attached a patch to $SUBJECT, to report the number of times the
logical_decoding_work_mem has been reached.
With such a counter one could get a ratio like total_txns/memory_limit_hits.
That could help to see if reaching logical_decoding_work_mem is rare or
frequent enough. If frequent, then maybe there is a need to adjust
logical_decoding_work_mem.
Based on my simple example above, one could say that it might be possible to get
the same with:
(spill_count - spill_txns) + (stream_count - stream_txns)
but that doesn't appear to be the case with a more complicated example (277 vs 247):
slot_name | spill_txns | spill_count | total_txns | stream_txns | stream_count | memory_limit_hits |
(spc-spct)+(strc-strt)
--------------+------------+-------------+------------+-------------+--------------+-------------------+------------------------
logical_slot | 405 | 552 | 19 | 5 | 105 | 277 |
247
(1 row)
Not sure I like memory_limit_hits that much, maybe work_mem_exceeded is better?
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
