ryapandt opened a new issue, #1915:
URL: https://github.com/apache/cloudberry/issues/1915
### Apache Cloudberry version
Apache Cloudberry 2.1.0
### What happened
Cloudberry repeatedly performs automatic aggressive wraparound vacuums on
thousands of nearly empty TOAST relations belonging to append-optimized row
tables.
Typical log message:
```text
automatic aggressive vacuum to prevent wraparound of table
"<database>.pg_toast.pg_toast_<oid>"
```
This happens even though the affected TOAST relations are nowhere near the
configured wraparound threshold.
Production observations from two databases:
- One database repeatedly vacuumed exactly 1,231 TOAST relations per cycle.
- Another database repeatedly vacuumed exactly 2,198 TOAST relations per
cycle.
- These counts exactly matched the number of AO row parent tables having
non-empty storage `reloptions`.
- The same TOAST OIDs were processed again during every autovacuum cycle.
- All affected parent tables used the `ao_row` access method.
- Their `reloptions` contained only AO storage settings such as
`compresstype`, `compresslevel`, `blocksize`, or `checksum`.
- Affected TOAST relations had transaction ID ages of only a few hundred;
the maximum observed age was below 1,000.
- Their multixact ages were zero.
- None was close to the normal `autovacuum_freeze_max_age` value of
approximately 200 million transactions.
- The vacuums commonly reported zero pages and zero tuples.
- There was no `cutoff for removing and freezing tuples is far in the past`
warning.
- `log_autovacuum_min_duration` was globally set to `-1`, but these vacuums
were still logged.
- In one five-second sample, 466 aggressive vacuum records were written and
the logs grew by approximately 1 MB.
This causes continuous autovacuum worker activity, CPU consumption, buffer
accesses, WAL generation, and very large log volume on databases containing
many AO tables.
This may explain the still-unresolved AO/TOAST behavior reported in #1850.
However, this case is deterministic and is not caused by a held-back
`OldestXmin`: the affected relations have very low XID ages, there is no
old-Xmin warning, and the affected set exactly matches AO parents with storage
reloptions.
The problem appears to be caused by an interaction between AO reloption
parsing and TOAST autovacuum option inheritance.
1. Autovacuum reloptions such as `autovacuum_freeze_max_age` are registered
only for `RELOPT_KIND_HEAP | RELOPT_KIND_TOAST`, not for
`RELOPT_KIND_APPENDOPTIMIZED`:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions.c#L286-L297
2. `allocateReloptStruct()` zero-initializes the complete `StdRdOptions`
structure using `palloc0()`:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions.c#L1735-L1760
3. `ao_amoptions()` parses AO parent-table reloptions using only
`RELOPT_KIND_APPENDOPTIMIZED`:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions_gp.c#L1911-L1931
4. Therefore, when an AO table has storage reloptions, a non-NULL
`StdRdOptions` is returned, but its embedded `AutoVacOpts` fields were never
populated with the expected `-1` sentinel/default values. They remain zero
because of `palloc0()`.
5. `extract_autovac_opts()` explicitly accepts `AO_ROW_TABLE_AM_OID` and
`AO_COLUMN_TABLE_AM_OID`, then copies the zero-filled embedded `AutoVacOpts`:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L2855-L2887
6. A TOAST relation without its own reloptions inherits this copied
structure from its AO parent:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L2245-L2301
7. The resulting effective values include:
```text
enabled = false
freeze_min_age = 0
freeze_max_age = 0
freeze_table_age = 0
multixact_freeze_max_age = 0
vacuum_cost_delay = 0
log_min_duration = 0
```
8. `relation_needs_vacanalyze()` considers any non-negative `freeze_max_age`
to be an explicitly configured value:
https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L3262-L3287
Because the inherited value is zero, the force limit becomes effectively:
```text
xidForceLimit = recentXid - 0
```
Consequently, almost every normal TOAST `relfrozenxid` precedes the force
limit and is immediately classified as requiring wraparound vacuum.
The forced-wraparound condition bypasses `autovacuum_enabled=false`, while
`freeze_table_age=0` makes the operation aggressive. The inherited
`log_min_duration=0` also explains why the operations are logged even when the
global `log_autovacuum_min_duration` is `-1`.
### What you think should happen instead
AO storage reloptions must not be interpreted as explicit autovacuum
settings.
When an AO parent table has only compression, checksum, or block-size
options:
- Its unused embedded `AutoVacOpts` values should not be copied as
zero-valued overrides.
- Its TOAST relation should use its own explicit autovacuum reloptions, if
any.
- Otherwise, the TOAST relation should use the normal global autovacuum
defaults.
- A TOAST relation with an XID age of only a few hundred must not be
classified as requiring wraparound vacuum.
- The global `log_autovacuum_min_duration=-1` setting should remain
effective unless a real per-table override exists.
### How to reproduce
For faster reproduction, use a short `autovacuum_naptime`, for example one
second. Keep `log_autovacuum_min_duration=-1`; this helps demonstrate that the
zero-valued inherited option overrides the global setting.
Create an AO row table with a TOAST-able column and explicit AO storage
reloptions:
```sql
CREATE SCHEMA av_ao_relopts_repro;
CREATE TABLE av_ao_relopts_repro.ao_with_storage_opts
(
id integer,
payload text
)
WITH
(
appendonly=true,
orientation=row,
compresstype=zlib,
compresslevel=1,
checksum=true
)
DISTRIBUTED RANDOMLY;
```
Confirm the AO parent, its storage reloptions, and its TOAST relation:
```sql
SELECT
n.nspname AS parent_schema,
c.relname AS parent_relation,
am.amname AS access_method,
c.reloptions AS parent_reloptions,
t.oid AS toast_oid,
t.relname AS toast_relation,
age(t.relfrozenxid) AS toast_xid_age,
mxid_age(t.relminmxid) AS toast_mxid_age
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
JOIN pg_am am
ON am.oid = c.relam
JOIN pg_class t
ON t.oid = c.reltoastrelid
WHERE n.nspname = 'av_ao_relopts_repro'
AND c.relname = 'ao_with_storage_opts';
```
Advance several normal transactions:
```sql
SELECT txid_current();
SELECT txid_current();
SELECT txid_current();
SELECT txid_current();
SELECT txid_current();
```
For a continuous reproduction, an external session can generate one
transaction at a time:
```bash
for i in $(seq 1 300); do
psql -Atqc 'SELECT txid_current()' >/dev/null
sleep 0.2
done
```
Wait for at least two autovacuum cycles and inspect the coordinator and
segment logs.
Expected buggy result:
```text
automatic aggressive vacuum to prevent wraparound of table
"<database>.pg_toast.pg_toast_<toast_oid>"
```
The message appears while `age(relfrozenxid)` is still very small. As
additional transactions are generated, the same TOAST relation is selected
repeatedly.
To reproduce the high-volume effect, create multiple AO tables with storage
reloptions:
```sql
DO $$
DECLARE
i integer;
BEGIN
FOR i IN 1..100 LOOP
EXECUTE format(
'CREATE TABLE av_ao_relopts_repro.ao_bug_%s
(
id integer,
payload text
)
WITH
(
appendonly=true,
orientation=row,
compresstype=zlib,
compresslevel=1,
checksum=true
)
DISTRIBUTED RANDOMLY',
i
);
END LOOP;
END
$$;
```
After advancing transactions, the TOAST relations of these tables should be
selected for aggressive vacuum repeatedly, despite their very low XID ages.
Cleanup:
```sql
DROP SCHEMA av_ao_relopts_repro CASCADE;
```
### Operating System
rocky 9.6
### Anything else
The exact problematic logic is still present in the latest `REL_2_STABLE`
branch:
- AO option parsing:
https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/access/common/reloptions_gp.c#L1911-L1931
- AO autovacuum option extraction:
https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/postmaster/autovacuum.c#L2858-L2890
- TOAST inheritance:
https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/postmaster/autovacuum.c#L2248-L2304
One possible minimal fix is to prevent an AO parent relation from returning
an `AutoVacOpts` structure when AO autovacuum reloptions are not supported:
```c
relam = ((Form_pg_class) GETSTRUCT(tup))->relam;
if (IsAccessMethodAO(relam))
return NULL;
```
AO auxiliary relations and TOAST relations use the heap access method, so
this guard would only prevent the invalid AO parent options from being
inherited.
Another possible fix is to initialize unsupported/missing `AutoVacOpts`
members to their intended `-1` sentinel values instead of leaving them zero.
A regression test should cover both AO row and AO column tables with
non-empty storage reloptions and verify that:
- Their low-age TOAST relations are not marked for wraparound vacuum.
- The configured global `autovacuum_freeze_max_age` is used.
- `log_autovacuum_min_duration=-1` is not overridden by an unintended zero
value.
- Explicit TOAST autovacuum options continue to work.
### Are you willing to submit PR?
- [ ] Yes, I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
