Autovacuum considered useful

[Heikki Linnakangas]

Let's talk about autovacuum.

Status quo
----------

In PostgreSQL, vacuuming performs several important duties. It keeps
table bloat in check, by removing dead tuple versions. And it prevents
XID wraparound, by freezing old tuples. Vacuum needs to run every now
and then, on all tables, to do those things. If old tuples are not
frozen, you eventually hit the XID wraparound limit, and the system
shuts down.

Autovacuum runs vacuum automatically, based on certain rules. You can
tune it, you can disable it for certain tables, and you can still run
VACUUM manually, e.g. when load is low, if you don't want autovacuum to
kick in at a busy period.

Avoiding XID wraparound is very important, and therefore in PostgreSQL,
even if you disable autovacuum by setting "autovacuum = off" in the
postgresql.conf file, it still runs, if necessary, to prevent XID
wraparound.

In GPDB, autovacuum is currently completely disabled. It will not run
even to prevent XID wraparound. It is therefore up to the administrator
to schedule manual VACUUMs with a cron job or similar. On all tables,
including catalog tables, on all databases. Otherwise the cluster will
shut down after about 2 billion transactions.

There is one little corner case, where that's a particular problem: The
'template0' database. The 'template0' database does not allow
connections. Therefore, you cannot VACUUM it manually. We have papered
over that in GPDB, by allowing XID wraparound to happen in the template0
database. That feels pretty questionable, even though in theory it's
safe, because all tuples in the template0 database should be frozen.
However, pg_class.relfrozenxid values in it are also not updated, so
things can get really messed up, if you create a new database using
'template0' as the template, after wrap-around has happened.


Proposal
--------

I propose that we re-enable autovacuum in GPDB. To what extent? I'm not
sure yet. But at the very least, I think we should let autovacuum handle
anti-wraparound vacuuming of 'template0', like it does in the upstream.

What about enabling it in general? From what I've heard, the reason
autovacuum was disabled in GPDB was that it caused erratic performance
[https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/fAN5gLJWlw4/IEMbZPHsCQAJ].
Autovacuum might kick in just when you're running a big, important
query, and compete for I/O. That problem is not unique to GPDB. It is
often required to tune autovacuum in PostgreSQL, too, or to set up
manual VACUUM of some tables, to avoid that. The unique thing in GPDB is
that it's an MPP system, and the time it takes for a query to finish is
determined by the slowest node in the cluster. If autovacuum kicks in at
different times on different segments, that's bad.

There is a lot of useful middle-ground between disabling autovacuum
altogether, and turning it on by default. As a first step, I think we
should enable it for anti-wraparound vacuums. Surely erratic
performance, if you forget to manually VACUUM a table, is better than
abruptly shutting down the cluster, and requiring manual steps from the
administrator to get it back up.

Even if you have set up a cron job to manually VACUUM all the big,
important tables, autovacuum can be useful to handle all the little
lookup tables that you forgot about. And for small tables, it runs
quickly, so performance won't be an issue.


So as a first baby step, I propose that we keep autovacuum=off, but
still launch it for anti-wraparound purposes, if you forget to vacuum a
table manually. Once we get some experience with that, we can consider
enabling it for catalog tables, or allowing it to be turned on in
general, if the administrator so chooses.

- Heikki

[Robert Eckhardt]

Have you considered the granularity you want to be able to enable/disable this feature? 

Just off the top of my head it would be nice to be able to turn it on for just the catalog or just a schema (perhaps the schema where ad hoc work is done that isn't managed as much by the DBA) or even just one DB. 

I think enabling it for the template0 schema as a first step is a perfect idea, before we rush to enable it everywhere we need to be able to figure out how to make it configurable and/or figure out how to better understand the impact so we don't have the issues we had before. 

-- Rob

 

- Heikki

[Andreas Scherbaum]

On Mon, Feb 12, 2018 at 12:31 PM, Heikki Linnakangas <hlinna...@pivotal.io> wrote:

Let's talk about autovacuum.

 

So as a first baby step, I propose that we keep autovacuum=off, but still launch it for anti-wraparound purposes, if you forget to vacuum a table manually. Once we get some experience with that, we can consider enabling it for catalog tables, or allowing it to be turned on in general, if the administrator so chooses.

Support might chime in and provide numbers, from what I've heard is that "cluster going down because of the wraparound issue" is a major problem.

At this point one needs to ask a simple question: does it make sense to "keep going" until the very last moment and don't ever suffer any performance penalties, until it is too late?

Or should the database handle this specific case, suffer some performance penalties once in a while, and keep the database cluster alive.

Admins can still use cron jobs and schedule regular vacuum during slow times.

I like the idea of turning it on again to prevent xid wraparound.

--

Andreas Scherbaum

Principal Software Engineer

GoPivotal Deutschland GmbH

Hauptverwaltung und Sitz: Am Kronberger Hang 2a, 65824 Schwalbach/Ts., Deutschland

Amtsgericht Königstein im Taunus, HRB 8433

Geschäftsführer: Andrew Michael Cohen, Paul Thomas Dacier

[Ivan Novick]

Thx for raising this!  This has potential for huge user satisfication gains and reducing complexity of running the system.

On Mon, Feb 12, 2018 at 3:31 AM, Heikki Linnakangas <hlinna...@pivotal.io> wrote:

What about enabling it in general? From what I've heard, the reason autovacuum was disabled in GPDB was that it caused erratic performance [https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/fAN5gLJWlw4/IEMbZPHsCQAJ]. Autovacuum might kick in just when you're running a big, important query, and compete for I/O. That problem is not unique to GPDB. It is often required to tune autovacuum in PostgreSQL, too, or to set up manual VACUUM of some tables, to avoid that. The unique thing in GPDB is that it's an MPP system, and the time it takes for a query to finish is determined by the slowest node in the cluster. If autovacuum kicks in at different times on different segments, that's bad.

Yup, if you have 500 segments, there is a large amount of time that we are in vaccum mode if they are not coordinated.

 

So as a first baby step, I propose that we keep autovacuum=off, but still launch it for anti-wraparound purposes, if you forget to vacuum a table manually. Once we get some experience with that, we can consider enabling it for catalog tables, or allowing it to be turned on in general, if the administrator so chooses.

Baby steps sounds good. 

With your proposal can the user choose to enable it for catalog tables?  Can they enable it for user tables?  Or will they be not available even with a GUC?

Cheers,

Ivan

 

- Heikki

--

Ivan Novick, Product Manager Pivotal Greenplum

ino...@pivotal.io --  (Mobile) 408-230-6491

https://www.youtube.com/GreenplumDatabase

[Heikki Linnakangas]

On 12/02/18 19:09, Ivan Novick wrote:
> On Mon, Feb 12, 2018 at 3:31 AM, Heikki Linnakangas <hlinna...@pivotal.io
>> wrote:
>
>> launch it for anti-wraparound purposes, if you forget to vacuum a table
>> manually. Once we get some experience with that, we can consider enabling
>> it for catalog tables, or allowing it to be turned on in general, if the
>> administrator so chooses.
>>
>> Baby steps sounds good.
>
> With your proposal can the user choose to enable it for catalog tables?
> Can they enable it for user tables? Or will they be not available even
> with a GUC?

To be determined. In the very first baby step, no. But once that's done,
it would be easy to allow it to be turned on, if the DBA wants to.

- Heikki

[Ivan Novick]

OK, any progress in this area is good, thank you so much!

[Robert Eckhardt]

On Mon, Feb 12, 2018 at 12:19 PM, Ivan Novick <ino...@pivotal.io> wrote:

On Mon, Feb 12, 2018 at 9:17 AM, Heikki Linnakangas <hlinna...@pivotal.io> wrote:

On 12/02/18 19:09, Ivan Novick wrote:

On Mon, Feb 12, 2018 at 3:31 AM, Heikki Linnakangas <hlinna...@pivotal.io

wrote:

launch it for anti-wraparound purposes, if you forget to vacuum a table
manually. Once we get some experience with that, we can consider enabling
it for catalog tables, or allowing it to be turned on in general, if the
administrator so chooses.

Baby steps sounds good.

With your proposal can the user choose to enable it for catalog tables?
Can they enable it for user tables?  Or will they be not available even
with a GUC?

To be determined. In the very first baby step, no. But once that's done, it would be easy to allow it to be turned on, if the DBA wants to.

OK, any progress in this area is good, thank you so much!

Ivan, 

To be clear this is a master feature only. We are investigating fixing the wrap around issue differently in 5.x. 

-- Rob

[Ivan Novick]

On Mon, Feb 12, 2018 at 9:22 AM, Robert Eckhardt <reck...@pivotal.io> wrote:

On Mon, Feb 12, 2018 at 12:19 PM, Ivan Novick <ino...@pivotal.io> wrote:

On Mon, Feb 12, 2018 at 9:17 AM, Heikki Linnakangas <hlinna...@pivotal.io> wrote:

On 12/02/18 19:09, Ivan Novick wrote:

On Mon, Feb 12, 2018 at 3:31 AM, Heikki Linnakangas <hlinna...@pivotal.io

wrote:

launch it for anti-wraparound purposes, if you forget to vacuum a table
manually. Once we get some experience with that, we can consider enabling
it for catalog tables, or allowing it to be turned on in general, if the
administrator so chooses.

Baby steps sounds good.

With your proposal can the user choose to enable it for catalog tables?
Can they enable it for user tables?  Or will they be not available even
with a GUC?

To be determined. In the very first baby step, no. But once that's done, it would be easy to allow it to be turned on, if the DBA wants to.

OK, any progress in this area is good, thank you so much!

Ivan, 

To be clear this is a master feature only. We are investigating fixing the wrap around issue differently in 5.x. 

Yes i got that, for 6.0.  Cool!

 

-- Rob

 

--

Ivan Novick, Product Manager Pivotal Greenplum

ino...@pivotal.io --  (Mobile) 408-230-6491

https://www.youtube.com/GreenplumDatabase

[Ashwin Agrawal]

On Mon, Feb 12, 2018 at 3:31 AM, Heikki Linnakangas <hlinna...@pivotal.io> wrote:

Proposal
--------

I propose that we re-enable autovacuum in GPDB.

+1 we should leverage the automation already in-place as vacuum is activity each and every GPDB customer has to perform. So, automation of the same inside is better than each and every user required to code separately for it.

 

To what extent? I'm not sure yet. But at the very least, I think we should let autovacuum handle anti-wraparound vacuuming of 'template0', like it does in the upstream.

Are we planning to have the process only on master and kick-in the vacuum ? I think the older approach of individual segments performing vacuuming at own will created management problems. Plus, distributed snapshot is needed anyways so need to trigger from master and helps know from one place what's going on.

But only part is how will master only process figure out wraparound conditions on segments ? So, basically need MPP version of auto-vacuum for GPDB.

What about enabling it in general? From what I've heard, the reason autovacuum was disabled in GPDB was that it caused erratic performance [https://groups.google.com/a/greenplum.org/d/msg/gpdb-dev/fAN5gLJWlw4/IEMbZPHsCQAJ]. Autovacuum might kick in just when you're running a big, important query, and compete for I/O. That problem is not unique to GPDB. It is often required to tune autovacuum in PostgreSQL, too, or to set up manual VACUUM of some tables, to avoid that. The unique thing in GPDB is that it's an MPP system, and the time it takes for a query to finish is determined by the slowest node in the cluster. If autovacuum kicks in at different times on different segments, that's bad.

Yes, in long term seems need to provide better flexible ways to specify policies around triggering auto-vacuum. Like on weekends trigger database-wide, or only trigger between X-Y time, or age is this much etc...

So as a first baby step, I propose that we keep autovacuum=off, but still launch it for anti-wraparound purposes, if you forget to vacuum a table manually. Once we get some experience with that, we can consider enabling it for catalog tables, or allowing it to be turned on in general, if the administrator so chooses.

+1

[Jet]

want to know the status on GP6 for the autovacuum.

I did some test on the newest GP6 stable version, the autovacuum seems run on all segments.

I suppose the autovacuum only run on master, and issue the vacuum command on master all in a distribute transaction.

在 2018年2月13日星期二 UTC+8上午2:46:55，Ashwin Agrawal写道：

[Asim R P]

On Wed, Jul 31, 2019 at 2:06 PM Jet <gnhe...@gmail.com> wrote:
>
> want to know the status on GP6 for the autovacuum.
> I did some test on the newest GP6 stable version, the autovacuum seems run on all segments.
> I suppose the autovacuum only run on master, and issue the vacuum command on master all in a distribute transaction.
>

Greenplum 6 allows autovacuum only for template0 database.  It remains disabled for all other database.  See autovacuum.c:get_database_list() function.  Your observation in line with the design.  Autovacuum operates independently on all segments, including master.

Aism

[Jet]

Thanks Aism,

But do we have any further plan for this important feature? I mean without autovacuum, there are many work to do for DBA, like table bloat and missing stats for optimizer etc. Since many tables would do massive update/insert/delete.

 

在 2019年7月31日星期三 UTC+8下午8:31:06，Asim R P写道：

[Ashwin Agrawal]

On Thu, Aug 1, 2019 at 9:38 AM Jet <gnhe...@gmail.com> wrote:

Thanks Aism,

But do we have any further plan for this important feature? I mean without autovacuum, there are many work to do for DBA, like table bloat and missing stats for optimizer etc. Since many tables would do massive update/insert/delete.

We do plan to do something (mostly enable auto-vacuum possibly only for catalog tables) for GPDB 7 to start with. For user tables the things get little tricker as wish to avoid affecting workloads. Past experience suggest auto-vacuum triggering at unpredictable times was not desirable.

[Asim R P]

There is consensus up thread that autovacuum needs to be enabled, as Ashwin also mentioned.  Implementation details need to be determined such as whether master should coordinate autovacuum workers on segments, how to determine from master if segments are approaching wraparound and so on.

Jet, you are welcome to submit a PR!

[Robert Eckhardt]

On Thu, Aug 1, 2019 at 12:08 AM Jet <gnhe...@gmail.com> wrote:

Thanks Aism,

But do we have any further plan for this important feature? I mean without autovacuum, there are many work to do for DBA, like table bloat and missing stats for optimizer etc. Since many tables would do massive update/insert/delete.

We have all of the stuff from Postgres to potentially make this work. Where we are now is just having the time to do it. 

If this is something you are interested in working on that would be awesome. Here are the notes we have about what we were thinking. 

Notes from Feb meeting:

=========================================================================

Enable Autovacuum for 7

=========================================================================

How and what do we enable for 7.

Split into 2 use cases:

1. Prevent XID wrap around

2. compaction/garbage collection

Issues when tried before:

1. was enabled locally on the segment

2. caused locking problems and performance issues

3. lot's of deadlock issue

Action items

• investigate the issues when we did the work before

• Investigate the distributed deadlocking issues

• make sure we test against old issues

Other issues/thoughts pointed out we need to consider

1. vacuum of AO tables very different from HEAP

2. Vacuum with bitmap indexes causes a reindex

3. we want to keep the segments independent as much as possible

4. Postgres vacuum improvements mean that there shouldn't be locking anymore and there should be no expectation of performance degradation

5. for AO tables we should treat the aux tables as if they are catalog tables

6. we need to consider the thresholds we set

Staging of the work:

1. make autovacuum work for all catalog tables -- bloat & XID wraparound

2. Enable it for all HEAP tables for XID wrap around

3. Figure out what we want to about bloat on HEAP

4. figure out what we want to do about bloat for AO

Separate work:

1. create a vacuum catalog option

=========================================================================

-- Rob

[Dmitry Dorofeev]

Hi all,

It might be interesting to check https://github.com/reorg/pg_repack

They done some smart hacks to avoid locking during vacuum.

-D.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-dev+u...@greenplum.org.

[David Krieger]

A minor suggestion...if autovacuum can cause a performance degradation, perhaps we can add a guc that disables autovacuum(maybe for a time?  maybe until a given date? maybe until turned back on?) for non-wraparound so that a DBA can specify that the system cannot take the autovacuum performance hit at that time.

--David

On Wednesday, July 31, 2019 at 9:19:16 PM UTC-7, Robert Eckhardt wrote:

On Thu, Aug 1, 2019 at 12:08 AM Jet rote:

[Andreas Scherbaum]

Hello,

the more pressing case is probably XID Wraparound Prevention.

PostgreSQL will start a Vacuum, even if Autovacuum is disabled, if a table has gone without Vacuum for a long time.

In Greenplum, this is disabled. In consequence, users which forget to vacuum tables, will face a system downtime at some point.

The question is if we prioritize performance over downtime.

Regards,

Andreas

[王伟]

We have submitted a PR10515 to enable distributed autovacuum in Greenplum, could you guys help review it. 

[Ashwin Agrawal]

On Tue, Aug 4, 2020 at 10:18 PM 王伟 <w...@hidva.com> wrote:

We have submitted a PR10515 to enable distributed autovacuum in Greenplum, could you guys help review it. 

Thanks for starting the work on it. Before we dive into code, let's work our way on strategy and approach at a higher level.

Our thoughts on autovacuum is that we don't need it in a distributed fashion. Instead each segment may do it independently based on its own needs.

So, curious to hear your thoughts on why you feel *distributed* autovacuum should be performed.

Also, we were thinking to incrementally enable autovacuum, possibly only for catalog tables.

So, I would like to hear more from you on this front as well. Where the need is felt for it, catalog tables or user tables and why?

How are you planning to control the triggering of autovacuum to not disrupt the workload on the system?

For what cases you are planning to enable autovacuum, only for transaction xid wraparounds or ?

--

Ashwin Agrawal (VMware)

[盏一]

If each segment does the autovacuum independently based on its own needs, this means that there may be a segment doing the vacuum work at any time, which will slow down the execution of QE on this segment. Considering that the time of the MPP query is limited to the slowest QE, it means that MPP queries will always be slowed down due to AUTOVACUUM on a segment. So I think we should let the master decide when to issue a vacuum and disable AUTOVACUUM in segments.

In PR10515, we make the statistics, especially PgStat_StatTabEntry::n_dead_tuples and PgStat_StatTabEntry::changes_since_analyze, on the master accurate, and let the auto vacuum worker work in the GP_ROLE_DISPATCH mode. The autovacuum worker on the master will

-   issue an analyze when `changes_since_analyze > analyze base threshold + analyze scale factor * number of tuples` is true.

-   issue a vacuum when `TransactionIdPrecedes(classForm->relfrozenxid, xidForceLimit)` or `MultiXactIdPrecedes(classForm->relminmxid, multiForceLimit)` is true. It means that we will vacuum the table every autovacuum_freeze_max_age-vacuum_freeze_min_age transaction. Considering that the XID allocation on the master and the segment occurs at the same time(does it?), this should avoid the occurrence of xid wraparound warnings on the segment.

-   issue a vacuum when `n_dead_tuples > vacuum base threshold + vacuum scale factor * number of tuples` is true. In fact, in our production environment, this kind of vacuum will only be triggered within a specified time period configured by our users according to their business characteristics.

Except for vacuum in specified time period, everything is the original logic of the autovacuum in Postgres.

[Junfeng Yang]

Hi guys,

Since the PR(https://github.com/greenplum-db/gpdb/pull/10515) brings the focus back to the autovacuum. So I organize autovacuum related information to help us have an overall view, then we can discuss the further steps to enable it. Here's the google document which contains below contents: https://docs.google.com/document/d/1rnO0FUadIPNC7SLY9kPntz6ce0l7XOtDflTbsgmZ1m8/edit?usp=sharing. I'll keep it updated base on our discussion. 

Google Docs - create and edit documents online, for free. Create a new document and edit with others at the same time -- from your computer, phone or tablet. Get stuff done with or without an internet connection. Use Docs to edit Word files. Free from Google. docs.google.com

Currently, GPDB enables the autovacuum daemon to perform VACUUM operations only on the Greenplum Database template database template0. Autovacuum is enabled for template0 because connections are not allowed to template0. The autovacuum daemon performs VACUUM operations on template0 to manage transaction IDs (XIDs) and help avoid transaction ID wraparound issues in template0.

Also, the autoanalyze is disabled too, but we bring in autostats feature in GPDB to collect statistics after executing CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT, and COPY. Accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing.

Why we want to enable it?

autovaccum is highly recommended in Postgres, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples.

The main jobs are:

1. Recovering Disk Space.
2. Updating Planner Statistics.
3. Updating the Visibility Map.
4. Preventing Transaction ID Wraparound Failures.

Currently, we require users to execute VACUUM periodically to avoid series issues. Enabling autovacuum will release the burden from users.

Also for ANALYZE, we rely on auto stats now. But we encounter several issues during the autostats phase.

auto_stats issues

1. It gets involved in CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT, and COPY in the same transaction, which brings extra overload in these queries. This will slow down the execution.

2. We may face deadlock issue, from mailing list https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/nPW-mbO-oAw/m/G9q5Wyz7AAAJ The issue is: T1: copy child_prt_1 on segment, trigger ANALYZE on the root partition table. Holding ShareUpdateExclusiveLock on the root table. T1 QEs: execute select * from pg_catalog.gp_acquire_sample_rows(relid, 10000, 't') on root table, which will hold AccessShareLock for each child table. Please refer to acquire_inherited_sample_rows.

T2: copy child_prt_3 on segment, trigger analysis on root partition table. Waiting for ShareUpdateExclusiveLock on the root table. T2 QEs holding RowExclusiveLock after executing COPY TO on segments since T2 is not committed.

So the wait graph is: T2 QD --> T1 QD --> T1 QE --> T2 QE --> T2 QD

• T2 QD waits for ShareUpdateExclusiveLock held by T1 QD.
• T1 QD waits for T1 QE to return from gp_acquire_sample_rows.
• T1 QE waits for T2 QE to release RowExclusiveLock.
• T2 QE waits for T2 QD to commit, then it can release lock.

The reason is the auto_stats may expand ANALYZE on the root table in the same transaction, it'll acquire locks for lots of child tables. Tune some GUCS will help to prevent the deadlock.

And the reason we expand analyze to root is that we don't have good timing to analyze the root partition table since autovacuum is not enabled.

Running ANALYZE separately could also avoid the issue. So If autoanalyze is enabled, analyze should run in separate transactions, then, we can totally remove the auto_stats logic and avoid the above issues.

We also have a similar Jira which users execute ANALYZE in the same transaction of truncate/insert on a leaf table causing deadlock. Enable antovacuum could also avoid user call ANALYZE manually in the same transaction.

Why we disable it at the beginning?

I checked the related topics from JIRA and mailing list.

https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/fAN5gLJWlw4/m/IEMbZPHsCQAJ https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/gqordopb6Gg/m/-GHXSE4qBwAJ

Then I collect issues as below:

1. Performance hit because of the additional vacuum load. GPDB is an MPP system, and the time it takes for a query to finish is determined by the slowest node in the cluster. If autovacuum kicks in at different times on different segments, that's bad.
2. Higher performance hit because of wiped out table statistics on master segment. This is because autoanalyze(it was run under utility mode in the past, now the autovacuum work still doesn't have MPP view) runs on master and table does not contain data on master.
3. Block other queries when autovacuum gets triggered on tables. (Various tools will block, such as gprecoverseg)
4. Orphan temp tables could help triggering autovac issues. And autovacuum includes temp tables in the relfrozenxid scan but autovacuum does not vacuum these temp tables. This issue used to happen on 4X, but after the PG merge, this issue should disappear.

For issue 1, Cost-based Vacuum Delay could help to reduce the impact(https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST).

For issue 3, since PG 9.4, autovacuum workers generally don't block other commands. If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum. Related code GetBlockingAutoVacuumPgproc. DeadLockCheck detects whether the current query is blocked by autovacuum(https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM).

Limitations for the autovacuum in GPDB

1. On master, when executing ANALYZE under autovacuum worker, it should have an MPP view to acquiring sample rows from segments if needed. This requires collecting pgstat into master. On each segment, it should be fine to execute autoanalyze separately if we can limit the performance issue mentioned above. But right now, I check the code, it seems neither ANALYZE nor auto_stats calculate stats on segments, which is reasonable.

2. For AO table, still uses distributed transactions for the updates on pg_aoseg during AO vacuum. Dropping old AWAITING_DROP segments that are no longer needed doesn't require a distributed transaction, so it can be done under each segment separately(this strategy works because deletes can't be performed for AO tables inside serializable transactions). But for other vacuum operations which updating pg_aoseg_* should be issued from master (see problem 3 in discussion https://groups.google.com/a/greenplum.org/g/gpdb-dev/c/lUwtNxaT3f0/m/PzGAqW1EAwAJ).

How to enable autovacuum in GPDB?

Based on the above topics, we have some basic thoughts on how to enable it.

For ANALYZE fired from autovacuum daemon

The autovacuum worker on master should have the MPP view to execute ANALYZE. Acquire sample rows from segments if needed. This should be the same with the ANALYZE command executed by user, except the autovacuum daemon should have the stats for the whole cluster to decide when to execute ANALYZE. (Seems we don't need statistics on segments right?)

For VACUUM fired from autovacuum daemon

Intuitively, there are two solutions:

1. Each segment takes care of its own autovacuum.

2. Master controls the whole cluster's vacuum. Just as VACUUM command executed by user. So we don't need autovacuum daemon on each segment.

Potential challenges.

For solution 1: We may still face issue 1 mentioned above. And whether Cost-based Vacuum Delay and lock acquisition interrupts the autovacuum could help with this?

Vacuum on AO table still needs to be fired from Master since it requires distributed transactions.

For solution 2: For a relation, maybe most of the segments have no need to execute vacuum.

For the AO table, as in the discussion linked above, independently vacuum could help to remove AWAITING_DROP segfiles.

---

发件人: 盏一 <w...@hidva.com>
发送时间: 2020年8月5日 16:31
收件人: Greenplum Developers <gpdb...@greenplum.org>
抄送: Ashwin Agrawal <ashwi...@gmail.com>; Greenplum Developers <gpdb...@greenplum.org>
主题: Re: Autovacuum considered useful

 

[Junfeng Yang]

Currently, we have a PR to enable auto-analyze in Greenplum master/7. Here I’ll briefly introduce the implementation. 

The basic idea is to collect pgstat info into Master when executing queries.

Start the master’s autovacuum launcher process. Fire an autovacuum work process for a database on master when the naptime reaches. Then the autovacuum worker will iterate through all tables/materialized views under current database, and execute ANALYZE for tables which reached the analyze threshold. Note the ANALYZE statement issued in the autovacuum worker on master is the same as executing it through query on QD. ie. The auto-analyze is coordinated by the master and segments do not start it’s own autovacuum launcher and autovacuum worker.

1 Implementations

=================================================

Collect pgstat on master for relations.

Since the auto-analyze is fired from master, we need to collect pgstat info into master. GPDB used to call `auto_stats` for queries that may process tuples for different command types. The PR brings in another function `gp_pgstat_report_tabstat` which should be called when tuples are processed, it’ll report pgstat for the command on master.

So we wrap the `auto_stats` and `gp_pgstat_report_tabstat` in a function called `collect_tabstat`. And put it as `auto_stats` used to be.

Make the autovacuum guc work to start the autovacuum launcher.

We used to disable modifying autovacuum related GUCs. Within the PR, enable them.

Recognize databases that need vacuum(template0 only) or analyze under the autovacuum launcher.

Now, we support xid wrap around VACUUM through autovacuum for template0 only. This is by checking the database’s `datallowconn` field. If it’s false, it does not allow the user to connect it directly. That’s also the reason why we enable xid wrap around VACUUM for template0.

Then we reuse the `datallowconn` for auto-analyze. If the current database’s `datallowconn` sets to true, we only perform ANALYZE in autovacuum workers. A new field `wi_for_analyze` in WorkerInfoData is added to pass this into the autovacuum worker.

Other tables xid wrap around VACUUM are ignored.

Change autovacuum worker’s Gp_role for ANALYZE.

Since auto-analyze is coordinated by the master and segments do not start it’s own autovacuum launcher and autovacuum worker. We need current master’s autovacuum workers which do auto-analyze on a database have `Gp_role = GP_ROLE_DISPATCH`. So it’ll do the ANALYZE on master to calculate statistics with right behavior.

But for VACUUM on template0, we still keep it running as `GP_ROLE_UTILITY`.

Autovacuum workers for ANALYZE should not block other commands.

In postgres, the autovacuum workers generally don't block other commands. If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum.

This feature should also work for the auto-analyze’s autovacuum workers. A test is added to ensure this.

A simple example is:

CREATE TABLE anaabort(id int); INSERT INTO anaabort select i from generate_series(1, 1000) as i; -- assume the autovacuum worker is executing ANALYZE on the table T1: BEGIN; T1: LOCK TABLE anaabort in EXCLUSIVE mode; T1: ... -- Then the autovacuum worker's ANALYZE command should abort and the pg_statistic should not get updated. SELECT count(*) FROM pg_statistic where starelid = 'anaabort'::regclass; count ------- 0     (1 row)

2 Limitations

==================================================

Partition table support

Current implementation does not collect pgstat for partition tables. So the auto-analyze will not execute for them.

The reason is we can not collect accurate pgstat for the partition table.

For example:

1. Insert into the child table, it updates the pgstat for the child table, but the root table's pgstat does not get updated on master. (Although we can simply add it to the root table)

2. Insert into the root table, it updates the pgstat for the root table on master, but underly child tables' pgstat not get updated, since GPDB only gets processed tuple count on master at each command execution end phase where `collect_tabstat` gets called. We need to collect tuple count for each child table on segments into master.

Although there’s solutions, we'll have PG 12 merge soon, so we decide to implement it later.

And for postgres, it only collects pgstat for child tables, this makes auto-analyze only executed for child tables. User still needs to manually ANALYZE on the parent table.

Cost-based delay

In postgres, the cost-base delay feature is used to limit the VACUUM and ANALYZE IO cost. Since the IO cost is not collected to master, so feature is not working in cluster wide. But we can still use it to limit IO cost for each segment.

We plan to support the disk io limit in the resource group. Once it is ready, we will see if the  resource group can help here.

VACUUM under autovacuum

Now, GPDB only supports xid wrap around VACUUM on template0 through the autovacuum. Since we still have different opinions for the auto-vacuum implementation, current PR does not include the vacuum part. 

3 Testing 

==================================================

For the test part, we already have pgstat collect test and simple auto-analyze test to make sure the auto-analyze works as expected in the PR.

But furthermore, I’m thinking do more for:

1. Enable auto-analyze as default to run all pipeline test cases to find potential bugs. Some of the test cases will fail because of the tables statistics are not up to date.

2. Run TPCB/TPC-DS with auto-analyze enabled to test the workload impact.

4 Further discussion 

==================================================

Once we merge the PR. There’s some decisions we need to make and discuss.

Shall we enable the auto-analyze as default(if the workload impact is small) in GPDB 7?

Are there any concerns?

---

发件人: Junfeng Yang <yje...@vmware.com>
发送时间: 2020年8月5日 18:21
收件人: 盏一 <w...@hidva.com>; Greenplum Developers <gpdb...@greenplum.org>
抄送: Ashwin Agrawal <ashwi...@gmail.com>
主题: 回复: Autovacuum considered useful

 

[Kirill Reshke]

Hello gpdb hackers!

I have read the whole thread, but didnt get one thing: do we need to vacuum AO/AOCS relation to prevent XID wraparound or just for compaction (remove deleted tuples)? As far as i know, AO/AOCS tables contains only plain tuples data, without any xmin/xmax thing, so there should be no XID wraparound with them. If so, maybe we can consider enable autovacuum (in any implementation) for HEAP tables only? I think, it should not impact performance much. The thing is, users usually dont create large HEAP  tables in Greenplum, mostly use AO/AOCS for `big data` purpose. So, vacuum will handle them fast.

[Ashwin Agrawal]

On Sun, Dec 25, 2022 at 12:10 AM Kirill Reshke <reshke...@gmail.com> wrote:

Hello gpdb hackers!

I have read the whole thread, but didnt get one thing: do we need to vacuum AO/AOCS relation to prevent XID wraparound or just for compaction (remove deleted tuples)? As far as i know, AO/AOCS tables contains only plain tuples data, without any xmin/xmax thing, so there should be no XID wraparound with them.

Correct append optimized tables don’t  contain any xids in tuples, hence don’t need vacuum for wraparound purpose. Just note -  auxiliary/meta-data tables associated with append optimized tables like pg_aoseg, visimap, block-directory (all these are heap tables) do need vacuuming for wraparound.

If so, maybe we can consider enable autovacuum (in any implementation) for HEAP tables only? I think, it should not impact performance much. The thing is, users usually dont create large HEAP  tables in Greenplum, mostly use AO/AOCS for `big data` purpose. So, vacuum will handle them fast.

Thanks for your feedback. Though it’s false assumption that Greenolum users don’t use HEAP tables much. We have many users which are HEAP heavy. Greenplum is mixed-workload, HTAP database and hence used for variety of workloads and purposes.

GPDB7 beta release we have enabled autovacuum for catalog tables. Autovacuum for template0 (non-connectable databases is enabled since godb5 for wraparound purpose). 

We are thinking/evaluating to provide via GUC different levels, users can choose, for autovacuum capability in gpdb - OFF, CATALOGONLY, WRAPAROUNDONLY, ALL(which will include all tables except AO).