Re: Add database to PGXACT / per database vacuuming

On 30.08.2013 19:01, Andres Freund wrote:
> For the logical decoding patch I added support for pegging
> RecentGlobalXmin (and GetOldestXmin) to a lower value. To avoid causing
> undue bloat& cpu overhead (hot pruning is friggin expensive) I split
> RecentGlobalXmin into RecentGlobalXmin and RecentGlobalDataXmin where
> the latter is the the xmin horizon used for non-shared, non-catalog
> tables. That removed almost all overhead I could measure.
>
> During that I was tinkering with the idea of reusing that split to
> vacuum/prune user tables in a per db fashion. In a very quick and hacky
> test that sped up the aggregate performance of concurrent pgbenches in
> different databases by about 30%. So, somewhat worthwile ;).
>
> The problem with that is that GetSnapshotData, which computes
> RecentGlobalXmin, only looks at the PGXACT structures and not PGPROC
> which contains the database oid. This is a recently added optimization
> which made GetSnapshotData() quite a bit faster& scalable which is
> important given the frequency it's called at.

Hmm, so you're creating a version of GetSnapshotData() that only takes
into account backends in the same backend?

> What about moving/copying the database oid from PGPROC to PGXACT?

Might be worthwhile.

> Currently a single PGXACT is 12 bytes which means we a) have several
> entries in a single cacheline b) have ugly sharing because we will have
> PGXACTs split over more than one cacheline.

I can't get excited about either of these arguments, though. The reason
for having separate PGXACT structs is that they are as small as
possible, so that you can fit as many of them as possible in as few
cache lines as possible. Whether one PGXACT crosses a cache line or not
is not important, because when taking a snapshot, you scan through all
of them.

I don't know how big an impact adding the database oid would have, on
the case that the PGPROC/PGXACT split was done in the first place. In
the worst case it will make taking a snapshot 1/3 slower under
contention. That needs to be tested.

One idea is to have a separate PGXACT array for each database? Well,
that might be difficult, but something similar, like group all PGXACTs
for one database together, and keep a separate lookup array for where
the entries for each database begins.

- Heikki