Re: Recovery performance of standby for multiple concurrent truncates on large tables

On Tue, Jul 10, 2018 at 10:05 AM Jamison, Kirk <k(dot)jamison(at)jp(dot)fujitsu(dot)com>
wrote:

> Since in the current implementation, the replay of each TRUNCATE/DROP
> TABLE scans the whole shared buffer.
>
> One approach (though idea is not really developed yet) is to improve the
> recovery by delaying the shared buffer scan and invalidation
> (DropRelFileNodeBuffers) and to put it after the next checkpoint (after
> failover completion). The replay of TRUNCATE/DROP TABLE just make the
> checkpointer process remember what relations should be invalidated in the
> shared buffer during subsequent checkpoint. The checkpointer then scans the
> shared buffer only once to invalidate the buffers of relations that was
> dropped and truncated.
>

How about using the background writer for this? It seems to me that the
main reason to invalidate buffers would be to free them up for buffer
allocation, which is precisely the task of background writer. When adding a
filenode to be invalidated, take note of bgwriter position and add it to a
queue. When bgwriter is advancing, check each buffer tag against a hash
table of filenodes being invalidated. When background writer has completed
a loop it can remove the invalidated filenode. When bgwriter falls behind
the clock sweep and there are filenodes to invalidate it should run the
invalidation scan instead of skipping ahead. If there are already too many
filenodes being invalidated, then whoever is trying to add a new one gets
to run the invalidation scan until something can be evicted.

--
Ants Aasma
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com/