Autovacuum in GPDB

[Heikki Linnakangas]

Quick question: Why is autovacuum not supported in GPDB?

I'm soon going to reach the point the 8.3 merge that would bring in the
commit that enabled autovacuum by default, so I started wondering.

- Heikki

[Ivan Novick]

I think its related to the original implementation was not auto-vacuuming consistently around the cluster.

We need through if the patch "works" in an MPP environment, if it does, i don't see any reason not to have it, in fact it would be a good feature.

@Ashwin and @Asim can provide more info

Cheers,

Ivan

[Mike Roth]

When autovacuum was enabled (in early 4.2) we would have individual segments begin vacuuming at unpredictable times.  We found that GPDB at the time was extremely sensitive performance wise to vacuum and that when one or two segments started an autovacuum process the production throughput for clients would drop dramatically.  It was not uncommon to see analytics and ETL times double while autovacuum was running.

In the end because autovacuum was 'unpredictable' to clients we decided to disable it and put the onus for maintenance onto the customer who could then schedule it so that it didn't impact their workloads.

~ mike

Ivan Novick

December 8, 2015 at 9:51 AM

I think its related to the original implementation was not auto-vacuuming consistently around the cluster.

We need through if the patch "works" in an MPP environment, if it does, i don't see any reason not to have it, in fact it would be a good feature.

@Ashwin and @Asim can provide more info

Cheers,

Ivan

Heikki Linnakangas

December 8, 2015 at 5:01 AM

[Roman Shaposhnik]

I am pretty sure it the same reason everybody doubly hates GC in distributed systems: when it kicks in on some individual nodes but not the others system overall enters a weird state (performance-wise).

Thanks,

Roman.

[Mike Roth]

Heikki Linnakangas wrote:
> In PostgreSQL, autovacuum still kicks in to prevent XID wraparound, if
> you don't run vacuum manually in a timely fashion, even when it's
> turned off. Does gpdb still launch anti-XID-wraparound vacuums? I
> don't remember if PostgreSQL always did that, or if that was changed
> at some point. But that behaviour goes back to 9.0 at least.
I don't know about PostgreSQL, but GPDB will start to issue warnings at
one point that a vacuum needs to be done. If that's not taken care of
then the segment will eventually shut down and won't restart until we
manually bring it up and force a vacuum (see varsup.c:78).

This behaviour can be problematic since the age of different segments
can become wildly out of sync with each other and issuing vacuums on the
master does not guarantee bringing down the age of the individual
segments since we only reset the age of the relation based on it's age
on the master.

~ mike

[CK Tan]

> On Dec 8, 2015, at 8:16 AM, Roman Shaposhnik <rshap...@pivotal.io> wrote:
>
> I am pretty sure it the same reason everybody doubly hates GC in distributed systems: when it kicks in on some individual nodes but not the others system overall enters a weird state (performance-wise).
>

The "weird state" is called skew. Imagine a simple select count (*). With one node doing the vacuum, all the other nodes would finish the count sooner, and the query needs to wait for the node doing the vacuum to complete.

In a big MPP, conceivably there is a vacuum going on somewhere in the system all the time. Now, your perf is *always* limited by vacuum. Therefore, it is much better to do so in one shot during the ETL cycle. Just the nature of the beast.

Thanks,
-cktan

[Andreas Scherbaum]

You can run into the same issue in Greenplum, but it takes more time.
The XID wraparound depends on the number of transactions, and there are not as many transactions in Greenplum as in some high-load PostgreSQL databases. But it is entirely possible.

That said: I think, a XID wraparound VACUUM will be kicked off no matter if autovacuum is enabled or not. According to Murphy, it will hit you during business hours.

--

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

[Cindy Lee]

Andreas is correct. XID vacuum will kick in regardless. 

--
Sent from Gmail Mobile

[Asim Praveen]

I agree with Mike and CK.  If we merge the patch to enable autovacuum from upstream as is, it will cause the regression (asynchronous vacuum on segments degrading performance).  I'm trying to think alternatives at hand - enable autovacuum but only master should kick it, in a distributed transaction.  This will be similar to manual run of vacuum.

Because we have seen XID soft limits and hard limits reach in production deployments, it may save support cycles if autovacuum is enabled.  It will also improve availability of the database because upon reaching XID stop limit, we stop accepting new connections today.

Asim