Horton

RE: [Patch] Optimize dropping of relation buffers using dlist

k.jamison@fujitsu.com <k.jamison@fujitsu.com>

View thread View on postgr.es
From: "k.jamison@fujitsu.com" <k.jamison@fujitsu.com>
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>, 'Robert Haas' <robertmhaas@gmail.com>
Date: 2020-03-25T06:24:32Z
Lists: pgsql-hackers

Attachments

Hi,

I know this might already be late at end of CommitFest, but attached
is the latest version of the patch. The previous version only includes buffer
invalidation improvement for VACUUM. The new patch adds the same
routine for TRUNCATE WAL replay.

In summary, this patch aims to improve the buffer invalidation process
of VACUUM and  TRUNCATE. Although it may not be a common use
case, our customer uses these commands a lot. Recovery and WAL
replay of these commands can take time depending on the size of
database buffers. So this patch optimizes that using the newly-added
auxiliary cache and doubly-linked list on the shared memory, so that
we don't need to scan the shared buffers anymore.

As for the performance and how it affects the read-only workloads.
Using pgbench, I've kept the overload to a minimum, less than 1%.
I'll post follow-up results.

Although the additional hash table utilizes shared memory, there's
a significant performance gain for both TRUNCATE and VACUUM
from execution to recovery.

Regards,
Kirk Jamison

Commits

  1. Fix size overflow in calculation introduced by commits d6ad34f3 and bea449c6.

  2. Optimize DropRelFileNodesAllBuffers() for recovery.

  3. Optimize DropRelFileNodeBuffers() for recovery.

  4. Cache smgrnblocks() results in recovery.

  5. Add a check to prevent overwriting valid data if smgrnblocks() gives a