>How significant is that speedup in the grand scheme of things? What
>fraction is vac_update_datfrozenxid of the overall work that autovacuum
>does?
To answer this question, I conducted additional measurements. To find
out how long vacuum takes, I decided to measure the execution time of
the `do_autovacuum` function and compare it with the execution time of
`vac_update_datfrozenxid`. I measured the execution time under and
without load.
The load was generated using the bgbench tool. In all load tests, the
results were approximately 900 TPS. (I dropped the `postgres` database
so that the vacuum only worked in 1 database)
pgbench -i -s 10 -d template1 -U postgres
pgbench -c 1 -j 1 -T 330 -d -S -d template1 -U postgres
The execution time was measured using bpftrace scripts.
Without load:
+-------------------------+----------+------------+------------+--------------+
| | Mean, ns | Mean Ratio | Median, ns | Median
Ratio |
+-------------------------+----------+------------+------------+--------------+
| do_autovacuum | 256665 | 3.68 | 247760 | 3.80 |
| vac_update_datfrozenxid | 69694 | 0.27 | 65232 | 0.26 |
+-------------------------+----------+------------+------------+--------------+
With load:
+------------------------------+----------+------------+------------+--------------+
| | Mean, ns | Mean Ratio | Median, ns |
Median Ratio |
+------------------------------+----------+------------+------------+--------------+
| do_autovacuum_load | 12673421 | 199.44 | 3109108 |
50.86 |
| vac_update_datfrozenxid_load | 63544 | 0.01 | 61128 |
0.02 |
+------------------------------+----------+------------+------------+--------------+
Thus, if there is no load on the database, then vac_update_datfrozenxid
occupies 27% of do_autovacuum. If there is a load, then 1-2%.
We can also evaluate how much faster do_autovacuum has become after
applying the patch.
Without load:
+---------------------+----------+------------+------------+--------------+
| | Mean, ns | Mean Ratio | Median, ns | Median Ratio |
+---------------------+----------+------------+------------+--------------+
| do_autovacuum | 256665 | 0.97 | 247760 | 0.97 |
| do_autovacuum_patch | 263913 | 1.03 | 255346 | 1.03 |
+---------------------+----------+------------+------------+--------------+
With load:
+--------------------------+----------+------------+------------+--------------+
| | Mean, ns | Mean Ratio | Median, ns | Median
Ratio |
+--------------------------+----------+------------+------------+--------------+
| do_autovacuum_load | 12673421 | 1.04 | 3109108 | 1.01 |
| do_autovacuum_load_patch | 12152590 | 0.96 | 3086644 | 0.99 |
+--------------------------+----------+------------+------------+--------------+
Acceleration by 3% without load and by 1-4% (depending on the metrics
being compared) under load.
>Did this patch help with the lock contention that you mentioned at
>the top?
Unfortunately, no, in my case, the acceleration was not enough.
Reconfiguring the autovacuum system helped.
However, I think this patch makes sense. What do you think about it?
Best regards,
Rustam Khamidullin
