Thanks for the ideas on improving the test! I am still trying to see how useful this type of testing is, but I will share what I have done.
> I wonder if it would be more realistic to throttle the work simulation > to a certain speed with pgbench -R rather than having it go flat out. good point > > If we logged the score, we could do the "unpatched" test with the > > patched code, just with commenting out the > > list_sort(tables_to_process, TableToProcessComparator); It'd then be > > interesting to zero the log_auto*_min_duration settings and review the > > order differences and how high the scores got. Would the average score > > be higher or lower with patched version? I agree. I attached a patch on top of v7 that implements a debug GUC to enable or disable sorting for testing purposes. > I'm not yet sure how meaningful it is, but I tried adding the > following to recheck_relation_needs_vacanalyze(): > > elog(LOG, "Performing autovacuum of table \"%s\" with score = %f", > get_rel_name(relid), score); The same attached patch also implements this log. I also spent more time working on the test script. I cleaned it up and combined it into a single script. I added a few things: - Ability to run with or without the batch workload. - OLTP tables are no longer the same size; they are created with different row counts using a minimum and maximum row count and a multiplier for scaling the next table. - A background collector for pg_stat_all_tables on relevant tables, stored in relstats_monitor.log. - Logs are saved after the run for further analysis, such as examining the scores. Also attached is analysis for a run with 16 OLTP tables and 3 batch tables. It shows that with sorting enabled or disabled, the vacuum/analyze activity does not show any major differences. OLTP had very similar DML and autovacuum/autoanalyze activity. A few points to highlight: 1/ In the sorted run, we had an equal number of autovacuums/autoanalyze on the smaller OLTP tables, as if every eligible table needed both autovacuum and autoanalyze. The unsorted run was less consistent on the smaller tables. I observed this on several runs. I don't think it's a big deal, but interesting nonetheless. 2/ Batch tables in the sorted run had less autovacuum time (1,257,821 vs 962,794 ms), but very similar autovacuum counts. 3/ OLTP tables, on the other hand, had more autovacuum time in the sorted run (3,590,964 vs 3,852,460 ms), but I do not see much difference in autovacuum/autoanalyze counts. Other tests I plan on running: - batch updates/deletes, since the current batch option only tests append-only tables. - OLTP only test. Also, I am thinking about another sorting strategy based on average autovacuum/autoanalyze time per table. The idea is to sort ascending by the greater of the two averages, so workers process quicker tables first instead of all workers potentially getting hung on the slowest tables. We can calculate the average now that v18 includes total_autovacuum_time and total_autoanalyze time. The way I see it, regardless of prioritization, a few large tables may still monopolize autovacuum workers. But at least this way, the quick tables get a chance to get processed first. Will this be an idea worth testing out? -- Sami Imseih Amazon Web Services (AWS)
From c24eeeb074acfb790fe44b60b2177482b9afe3c3 Mon Sep 17 00:00:00 2001 From: Ubuntu <[email protected]> Date: Tue, 4 Nov 2025 15:55:40 +0000 Subject: [PATCH 1/1] autovacuum score logging and sort enable/disable --- src/backend/postmaster/autovacuum.c | 7 ++++++- src/backend/utils/misc/guc_parameters.dat | 10 ++++++++++ src/backend/utils/misc/guc_tables.c | 6 ++++++ src/include/postmaster/autovacuum.h | 8 ++++++++ 4 files changed, 30 insertions(+), 1 deletion(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index e48bb06253b..ca9c5c615dc 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -333,6 +333,8 @@ static WorkerInfo MyWorkerInfo = NULL; /* PID of launcher, valid only in worker while shutting down */ int AutovacuumLauncherPid = 0; +int debug_autovacuum_sort = DEBUG_AUTOVACUUM_SORT_ON; + static Oid do_start_worker(void); static void ProcessAutoVacLauncherInterrupts(void); pg_noreturn static void AutoVacLauncherShutdown(void); @@ -2083,6 +2085,7 @@ do_autovacuum(void) table->oid = relid; table->score = score; + elog(LOG, "adding table:%s,%lf,av=%d,aa=%d", get_rel_name(table->oid), score, dovacuum, doanalyze); tables_to_process = lappend(tables_to_process, table); } @@ -2184,6 +2187,7 @@ do_autovacuum(void) table->oid = relid; table->score = score; + elog(LOG, "adding table:%s,%lf,av=1,aa=0", get_rel_name(table->oid), score); tables_to_process = lappend(tables_to_process, table); } @@ -2309,7 +2313,8 @@ do_autovacuum(void) MemoryContextSwitchTo(AutovacMemCxt); } - list_sort(tables_to_process, TableToProcessComparator); + if (debug_autovacuum_sort == DEBUG_AUTOVACUUM_SORT_ON) + list_sort(tables_to_process, TableToProcessComparator); /* * Optionally, create a buffer access strategy object for VACUUM to use. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index d6fc8333850..2bf9ce4ed27 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -3445,6 +3445,16 @@ options => 'debug_parallel_query_options', }, +{ name => 'debug_autovacuum_sort', type => 'enum', context => 'PGC_USERSET', group => 'DEVELOPER_OPTIONS', + short_desc => 'Enables/Disables the autovacuum sort of eligible tables.', + long_desc => 'This can be useful for testing the effect of sorting eligible tables in autovacuum.', + flags => 'GUC_NOT_IN_SAMPLE | GUC_EXPLAIN', + variable => 'debug_autovacuum_sort', + boot_val => 'DEBUG_AUTOVACUUM_SORT_ON', + options => 'debug_autovacuum_sort_options', +}, + + { name => 'password_encryption', type => 'enum', context => 'PGC_USERSET', group => 'CONN_AUTH_AUTH', short_desc => 'Chooses the algorithm for encrypting passwords.', variable => 'Password_encryption', diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 00c8376cf4d..aaa93d35187 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -404,6 +404,12 @@ static const struct config_enum_entry debug_parallel_query_options[] = { {NULL, 0, false} }; +static const struct config_enum_entry debug_autovacuum_sort_options[] = { + {"off", DEBUG_AUTOVACUUM_SORT_OFF, false}, + {"on", DEBUG_AUTOVACUUM_SORT_ON, false}, + {NULL, 0, false} +}; + static const struct config_enum_entry plan_cache_mode_options[] = { {"auto", PLAN_CACHE_MODE_AUTO, false}, {"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false}, diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index 023ac6d5fa8..32688784a06 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -25,6 +25,14 @@ typedef enum AVW_BRINSummarizeRange, } AutoVacuumWorkItemType; +/* possible values for debug_autovacuum_sort */ +typedef enum +{ + DEBUG_AUTOVACUUM_SORT_OFF, + DEBUG_AUTOVACUUM_SORT_ON, +} DebugAutovacuumSortMode; + +extern PGDLLIMPORT int debug_autovacuum_sort; /* GUC variables */ extern PGDLLIMPORT bool autovacuum_start_daemon; -- 2.43.0
################################################################
## SORT OFF
################################################################
### from config
BASE_DIR=$HOME/test_autovacuum_prioritization
OLTP_TABLES=16
OLTP_ROWS_START=1000
OLTP_ROWS_MULTIPLIER=2
OLTP_MAX_ROWS=5000000
BATCH_TABLES=3
BATCH_SIZE=100000
BATCH_CONNECTIONS=5
OLTP_CONNECTIONS=200
TIMEOUT=1800
OLTP_RATE=15000
BATCH_SLEEP=5
BUCKETS=15
### from summary_report.txt
=== Database Settings ===
name | setting
---------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 6
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 5
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_insert_scale_factor | 0.2
autovacuum_vacuum_insert_threshold | 1000
autovacuum_vacuum_max_threshold | 100000000
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
autovacuum_worker_slots | 16
debug_autovacuum_sort | off <<<-----------------
log_autovacuum_min_duration | 600000
max_connections | 1000
shared_buffers | 1048576
(20 rows)
=== Total Activity ===
Expanded display is on.
-[ RECORD 1 ]----------------+----------
total_n_dead_tup | 3555172
total_n_mod_since_analyze | 20714669
total_reltuples | 141795153
total_autovacuum_count | 1890
total_autoanalyze_count | 2004
total_n_tup_upd | 26995189
total_n_tup_hot_upd | 2
total_n_tup_newpage_upd | 3868918
total_n_tup_ins | 161298000
total_total_autovacuum_time | 4847051
total_total_autoanalyze_time | 695004
avg_autovacuum_time | 2564.58
avg_autoanalyze_time | 346.81
table_count | 19
### last snapshot from relstats_monitor.log
?column? | timestamp | relname | reltuples |
n_dead_tup | av_count | aa_count | total_av_time | total_aa_time | n_tup_upd |
n_tup_hot_upd | n_tup_ins | avg_av_time | avg_aa_time
----------+------------------------------+------------------+-----------+------------+----------+----------+---------------+---------------+-----------+---------------+-----------+-------------+-------------
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_batch_1 | 49200700 |
0 | 17 | 30 | 541848 | 46902 | 0 |
0 | 52000000 | 31873.41 | 1563.40
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_batch_2 | 44600500 |
0 | 17 | 26 | 375595 | 53137 | 0 |
0 | 49300000 | 22093.82 | 2043.73
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_batch_3 | 39800400 |
0 | 18 | 28 | 340378 | 43500 | 0 |
0 | 51200000 | 18909.89 | 1553.57
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_13_4096000 | 4094410 |
1676409 | 0 | 2 | 0 | 9029 | 1680467 |
0 | 4096000 | 0.00 | 4514.50
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_12_2048000 | 2046860 |
803638 | 2 | 4 | 931598 | 41897 | 1680863 |
0 | 2048000 | 465799.00 | 10474.25
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_11_1024000 | 1023180 |
493988 | 5 | 8 | 924292 | 54659 | 1681582 |
0 | 1024000 | 184858.40 | 6832.38
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_10_512000 | 511256 |
171819 | 12 | 19 | 728662 | 77863 | 1680512 |
0 | 512000 | 60721.83 | 4098.05
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_9_256000 | 255997 |
93717 | 24 | 41 | 251528 | 61078 | 1680115 |
0 | 256000 | 10480.33 | 1489.71
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_8_128000 | 127970 |
37869 | 42 | 60 | 166034 | 59070 | 1678672 |
0 | 128000 | 3953.19 | 984.50
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_7_64000 | 63991 |
44945 | 74 | 99 | 116048 | 54583 | 1680251 |
0 | 64000 | 1568.22 | 551.34
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_6_32000 | 32000 |
9673 | 104 | 116 | 84778 | 41956 | 1682235 |
0 | 32000 | 815.17 | 361.69
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_5_16000 | 16000 |
49469 | 186 | 190 | 74828 | 36245 | 1680406 |
0 | 16000 | 402.30 | 190.76
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_4_8000 | 7996 |
4309 | 174 | 179 | 53884 | 22075 | 1682471 |
0 | 8000 | 309.68 | 123.32
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_3_4000 | 3992 |
98236 | 214 | 215 | 49471 | 19975 | 1681034 |
0 | 4000 | 231.17 | 92.91
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_16_4000 | 3988 |
39577 | 184 | 182 | 45433 | 16430 | 1679804 |
0 | 4000 | 246.92 | 90.27
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_15_2000 | 2000 |
1753 | 199 | 194 | 42538 | 14346 | 1682234 |
0 | 2000 | 213.76 | 73.95
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_2_2000 | 1999 |
5668 | 203 | 200 | 42430 | 14802 | 1681089 |
0 | 2000 | 209.01 | 74.01
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_14_1000 | 1000 |
3934 | 203 | 199 | 38499 | 13840 | 1678710 |
1 | 1000 | 189.65 | 69.55
TAB_DATA | 2025-11-06 20:13:17.08327+00 | table_1_1000 | 903 |
55323 | 210 | 210 | 36441 | 12661 | 1681126 |
1 | 1000 | 173.53 | 60.29
(19 rows)
################################################################
## SORT ON
################################################################
### from config
BASE_DIR=$HOME/test_autovacuum_prioritization
OLTP_TABLES=16
OLTP_ROWS_START=1000
OLTP_ROWS_MULTIPLIER=2
OLTP_MAX_ROWS=5000000
BATCH_TABLES=3
BATCH_SIZE=100000
BATCH_CONNECTIONS=5
OLTP_CONNECTIONS=200
TIMEOUT=1800
OLTP_RATE=15000
BATCH_SLEEP=5
BUCKETS=15
### from summary_report.txt
=== Database Settings ===
name | setting
---------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 6
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 5
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_insert_scale_factor | 0.2
autovacuum_vacuum_insert_threshold | 1000
autovacuum_vacuum_max_threshold | 100000000
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
autovacuum_worker_slots | 16
debug_autovacuum_sort | on <<-----------
log_autovacuum_min_duration | 600000
max_connections | 1000
shared_buffers | 1048576
(20 rows)
=== Total Activity ===
Expanded display is on.
-[ RECORD 1 ]----------------+----------
total_n_dead_tup | 3596571
total_n_mod_since_analyze | 33295739
total_reltuples | 128920339
total_autovacuum_count | 1923
total_autoanalyze_count | 2036
total_n_tup_upd | 26992249
total_n_tup_hot_upd | 4
total_n_tup_newpage_upd | 3714297
total_n_tup_ins | 161398000
total_total_autovacuum_time | 5040892
total_total_autoanalyze_time | 735082
avg_autovacuum_time | 2621.37
avg_autoanalyze_time | 361.04
table_count | 19
### last snapshot from relstats_monitor.log
?column? | timestamp | relname | reltuples |
n_dead_tup | av_count | aa_count | total_av_time | total_aa_time | n_tup_upd |
n_tup_hot_upd | n_tup_ins | avg_av_time | avg_aa_time
----------+-------------------------------+------------------+-----------+------------+----------+----------+---------------+---------------+-----------+---------------+-----------+-------------+-------------
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_batch_2 | 45647800 |
0 | 19 | 32 | 397861 | 58865 | 0 |
0 | 52000000 | 20940.05 | 1839.53
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_batch_3 | 42198400 |
0 | 16 | 26 | 549624 | 48589 | 0 |
0 | 47100000 | 34351.50 | 1868.81
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_batch_1 | 32878300 |
0 | 16 | 29 | 244947 | 35940 | 0 |
0 | 53500000 | 15309.19 | 1239.31
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_13_4096000 | 4095080 |
1674368 | 0 | 2 | 0 | 11957 | 1680719 |
0 | 4096000 | 0.00 | 5978.50
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_12_2048000 | 2048010 |
785834 | 2 | 4 | 973686 | 47742 | 1683000 |
0 | 2048000 | 486843.00 | 11935.50
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_11_1024000 | 1023560 |
471569 | 5 | 8 | 1012300 | 61222 | 1677795 |
0 | 1024000 | 202460.00 | 7652.75
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_10_512000 | 513070 |
65104 | 12 | 18 | 771222 | 64852 | 1679348 |
0 | 512000 | 64268.50 | 3602.89
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_9_256000 | 255877 |
104537 | 22 | 34 | 263788 | 52513 | 1680026 |
0 | 256000 | 11990.36 | 1544.50
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_8_128000 | 127974 |
52589 | 40 | 57 | 182622 | 58427 | 1679095 |
0 | 128000 | 4565.55 | 1025.04
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_7_64000 | 63995 |
15459 | 71 | 88 | 123084 | 50789 | 1680143 |
0 | 64000 | 1733.58 | 577.15
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_6_32000 | 31998 |
18651 | 115 | 130 | 97063 | 47557 | 1678753 |
0 | 32000 | 844.03 | 365.82
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_5_16000 | 16000 |
21281 | 170 | 170 | 77925 | 35527 | 1680055 |
0 | 16000 | 458.38 | 208.98
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_4_8000 | 7997 |
23487 | 195 | 197 | 65038 | 27777 | 1682290 |
0 | 8000 | 333.53 | 141.00
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_3_4000 | 4000 |
26826 | 206 | 206 | 56811 | 22326 | 1682540 |
1 | 4000 | 275.78 | 108.38
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_16_4000 | 3999 |
25514 | 206 | 206 | 56515 | 22142 | 1681183 |
0 | 4000 | 274.34 | 107.49
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_15_2000 | 2000 |
28429 | 207 | 207 | 48227 | 19677 | 1681553 |
2 | 2000 | 232.98 | 95.06
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_2_2000 | 2000 |
28247 | 207 | 207 | 47682 | 19536 | 1679615 |
0 | 2000 | 230.35 | 94.38
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_1_1000 | 1000 |
30309 | 207 | 207 | 36442 | 16492 | 1680101 |
0 | 1000 | 176.05 | 79.67
TAB_DATA | 2025-11-06 20:46:26.073153+00 | table_14_1000 | 999 |
30314 | 207 | 207 | 36055 | 16875 | 1681990 |
1 | 1000 | 174.18 | 81.52
(19 rows)
test_autovacuum_prioritization.sh
Description: Bourne shell script
