Autovacuum and resource management

[Soumyadeep Chakraborty]

Hi folks,

I wanted to discuss the interaction of resource queues/groups with autovacuum.

Resource queues:

Automatic vacuums will bypass resource queues, which is fine since we want to
only restrict user workloads.

This is because of how ResLockPortal() is called (only for role=dispatch and
non-su).

Autovacuum executes as a superuser. Verified by patch:

--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -2563,6 +2563,8 @@ do_autovacuum(void)
/* Use PortalContext for any per-table allocations */
MemoryContextSwitchTo(PortalContext);

+ elog(LOG, "user id = %u, su = %d", GetUserId(), superuser());

Also, all background workers are assigned BOOTSTRAP_SUPERUSERID(10) during init.
See InitializeSessionUserIdStandalone().

This bypass behavior extends to autoanalyze as well due to su (user id gets
dispatched with analyze dispatched from autovacuum worker to segments).

Resource groups (need feedback):

Autovacuum/autoanalyze will go into the admin resource group. This is because:

AV workers and launchers both enter into NormalProcessingMode and
ShouldAssignResGroupOnMaster() will return true:

return IsResGroupActivated() &&
IsNormalProcessingMode() &&
Gp_role == GP_ROLE_DISPATCH &&
!proc_exit_inprogress &&
!procIsWaiting(MyProc);

Do we want to bypass, by adding a condition IsAutoVacuumWorker() etc?

My take is that we should bypass at least for catalog-only autovacuum
(i.e. our default
setting of autovacuum targeting only catalog tables) and definitely for
anti-wraparound autovacuums (when the cluster is in danger of xid wraparound).
In general, I wouldn't be opposed to having it be bypassed even when autovacuum
is executing on user tables or autoanalyze in general, as this is a crucial
system activity that needs to be executed continuously for cluster health.

Regards,
Soumyadeep (VMware)

[Ashwin Agrawal]

On Tue, Feb 7, 2023 at 9:12 PM Soumyadeep Chakraborty <soumyad...@gmail.com> wrote:

Resource groups (need feedback):

Autovacuum/autoanalyze will go into the admin resource group. This is because:

AV workers and launchers both enter into NormalProcessingMode and
ShouldAssignResGroupOnMaster() will return true:

return IsResGroupActivated() &&
        IsNormalProcessingMode() &&
        Gp_role == GP_ROLE_DISPATCH &&
        !proc_exit_inprogress &&
        !procIsWaiting(MyProc);

Do we want to bypass, by adding a condition IsAutoVacuumWorker() etc?

My take is that we should bypass at least for catalog-only autovacuum
(i.e. our default
setting of autovacuum targeting only catalog tables) and definitely for
anti-wraparound autovacuums (when the cluster is in danger of xid wraparound).
In general, I wouldn't be opposed to having it be bypassed even when autovacuum
is executing on user tables or autoanalyze in general, as this is a crucial
system activity that needs to be executed continuously for cluster health.

We shouldn't bypass RG for autovacuum or autoanalyze. Ideally, they MUST be resource managed (except anti-wraparound vacuums which is a must perform now activity). From experience we have learned everything in the GPDB system should be accounted for (smallest to smallest allocation). Now being under RG doesn't mean limiting the resources and constraining this work always. It can be configured in other ways too, like guaranteeing a certain number of CPU cores to such activity to give it priority. Also, the memory used for such a job has to be accounted for (even if not constrained) in the system so that RG doesn't oversubscribe.

If the admin group feels too open ended for this, possibility exists to create a system group for such tasks to fall under. But it has to be accounted for and fall under some group and provision must exist to constrain its Resource usage as desired (will have to understand how GUCs related to autovacuum and RG interact). The RG memory model is being worked on, should discuss with the team and see what can be done with this respect.

-- 

Ashwin Agrawal (VMware)

[Soumyadeep Chakraborty]

Hey Ashwin,

On Wed, Feb 15, 2023 at 4:01 PM Ashwin Agrawal <ashwi...@gmail.com> wrote:

> We shouldn't bypass RG for autovacuum or autoanalyze. Ideally, they MUST be resource managed (except anti-wraparound vacuums which is a must perform now activity). From experience we have learned everything in the GPDB system should be accounted for (smallest to smallest allocation). Now being under RG doesn't mean limiting the resources and constraining this work always. It can be configured in other ways too, like guaranteeing a certain number of CPU cores to such activity to give it priority. Also, the memory used for such a job has to be accounted for (even if not constrained) in the system so that RG doesn't oversubscribe.

I generally agree, especially for CPU. Currently, autovacuum workers don't take
more than maintenance_work_mem and I'm not sure if/how we need to fit it into
the RG memory model though.

> If the admin group feels too open ended for this, possibility exists to create a system group for such tasks to fall under. But it has to be accounted for and fall under some group and provision must exist to constrain its Resource usage as desired (will have to understand how GUCs related to autovacuum and RG interact). The RG memory model is being worked on, should discuss with the team and see what can be done with this respect.

The admin group does seem open ended as all su roles land there implicitly. Now,
if a given deployment has superusers running heavy analytical workloads, we will
have unintended resource contention. Also, it might be a frequent practice to
lower the resource allocation for admin_group even lower than the defaults to
squeeze every bit of performance possible. I can envision that being the case
especially if only fast DCL is done with those roles.

Carving out a system_group may not be a bad thing. For instance, if we were to
introduce an online checksum validator (or something similar) in the future, I
can see it being useful. With it, we can push our opinionated defaults and help
prevent misconfiguration.

Yes, we should definitely have a discussion. Not just for memory, but for CPU
and IO as well.

Regards,
Soumyadeep (VMware)