Обсуждение: [PATCH] Speed up of vac_update_datfrozenxid.

On 15/07/2025 14:02, Rustam Khamidullin wrote:
> Hi hackers,
>
> While testing behavior of 'vacuum' functionality under high load
> we've noticed that the exclusive lock with 'LOCKTAG_DATABASE_FROZEN_ID'
> tag could be held for a prolonged time, thus causing contention with
> other sessions.
> This lock is obtained by the 'vac_update_datfrozenxid' function at the
> end of vacuum
> process to update value of 'datfrozenxid' in the 'pg_database' relation.
> This function currently performs two operations under the
> mentioned lock: first scanning the 'pg_class' to find minimal
> 'relfrozenxid' and 'relminmxid' and then update corresponding values
> in the 'pg_database' relation. Update of the 'pg_database' table
> includes locking the required tuple and then deciding whether row
> need to be updated or not. While looking at the
> 'vac_update_datfrozenxid' content, we've noticed following comment:
>
> /*
>   * Fetch a copy of the tuple to scribble on. We could check the syscache
>   * tuple first. If that concluded !dirty, we'd avoid waiting on
>   * concurrent heap_update() and would avoid exclusive-locking the buffer.
>   * For now, don't optimize that.
>   */
>
> The optimization described in this comment (i.e. scanning and
> locking tuple only if we really intend to update it) seems to be a
> valid one, so I've created a patch to test it (attached to this
> message). It should be applicable on top of current 'master' branch.
> The code now tries to obtain data from the SysCache first and checks
> whether it's necessary to call 'systable_inplace_update_begin()' to
> actually update xid values.
>
> To test the effect of the patch, a synthetic test was written using
> bpftrace.
> Bpftrace script measures the execution time of 'vac_update_datfrozenxid'.
> The script is attached to the email.
>
> The test scenario:
>
>  1. Create an empty database cluster
>  2. Set autovacuum_naptime = '1s' (so that 'vac_update_datfrozenxid' is
>     called more often)
>  3. Run the bpftrace script (it outputs the function execution time in
>     ns to stdout)
>
> It is not necessary to apply the load. The autovacuum worker will call
> 'vac_update_datfrozenxid' regardless.
> As a result, execution time of 'vac_update_datfrozenxid()' with the
> patch is 30% faster
>                    No patch         Patch          Speed up
> Mean:         73058 ns        51444 ns      30%
> Median:      71846  ns       48650 ns      33%
>
> It is worth noting that due to the fact thatbefore updating the
> 'datfrozenxid',a full 'pg_class' scan is performed, the performance
> improvementbecomes less significant as the number of
> relationsincreases(more time will be spent on sequentialscan of
> 'pg_class').The previous measurement was performedon an empty
> clusterwith nouser relations.To test the patch in a more realistic case,
> I generated 1000 tables in the database and repeated the measurement. No
> patch Patch Speed up
> Mean:97113 ns 73839 ns 24% Median:98328 ns 63541 ns 29% As we can see,
> the speed gain has decreased, but it is still significant.

How significant is that speedup in the grand scheme of things? What
fraction is vac_update_datfrozenxid of the overall work that autovacuum
does? Did this patch help with the lock contention that you mentioned at
the top?

- Heikki

 >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