Re: [PATCH] Speed up of vac_update_datfrozenxid. - Mailing list pgsql-hackers

 >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