Subtransactions and performance degradation, part 3: test SLRU patches
There goal here is to test patches Andrey Borodin's patches, https://commitfest.postgresql.org/34/2627/ to see how helpful it can be to mitigate negative effects observed in #21 if we increase Subtrans SLRU cache size and/or switch to different algorithm developed in one of those patches:
- https://www.postgresql.org/message-id/flat/494C5E7F-E410-48FA-A93E-F7723D859561%40yandex-team.ru#18c79477bf7fc44a3ac3d1ce55e4c169
- https://commitfest.postgresql.org/34/2627/
Some testing was done already in #21 (comment 655503554).
What we want to check:
-
does it help to increase NUM_SUBTRANS_BUFFERS
(from default 32 pages to 1M, 10M, 100M), leaving the algorithm as is (linear search)?- patch for GUCs applied to PG14: postgres/postgres!7
- alternative: manually adjusted
NUM_SUBTRANS_BUFFERS
in the source code
-
does it help to increase NUM_SUBTRANS_BUFFERS
(from default 32 pages to 1M, 10M, 100M) and to change algorithm (second patch by Andrey, making SLRU n-associative, where n = 8)?- both patches applied to PG14: postgres/postgres!6
-
how fast Subtrans SLRU does overflow? Can we have some math here? Does it depend on TPS on the primary, TPS on replica, frequency of subtransactions in transactions on the primary? Can we have a formula, that helps us predict the problem, in seconds or XID growth?
Edited by Nikolay Samokhvalov