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

On Fri, Sep 25, 2020 at 1:49 PM k(dot)jamison(at)fujitsu(dot)com
<k(dot)jamison(at)fujitsu(dot)com> wrote:
>
> Hi.
>
> > I'll send performance measurement results in the next email. Thanks a lot for
> > the reviews!
>
> Below are the performance measurement results.
> I was only able to use low-spec machine:
> CPU 4v, Memory 8GB, RHEL, xfs filesystem.
>
> [Failover/Recovery Test]
> 1. (Master) Create table (ex. 10,000 tables). Insert data to tables.
> 2. (M) DELETE FROM TABLE (ex. all rows of 10,000 tables)
> 3. (Standby) To test with failover, pause the WAL replay on standby server.
> (SELECT pg_wal_replay_pause();)
> 4. (M) psql -c "\timing on" (measures total execution of SQL queries)
> 5. (M) VACUUM (whole db)
> 6. (M) After vacuum finishes, stop primary server: pg_ctl stop -w -mi
> 7. (S) Resume wal replay and promote standby.
> Because it's difficult to measure recovery time I used the attached script (resume.sh)
> that prints timestamp before and after promotion. It basically does the following
> - "SELECT pg_wal_replay_resume();" is executed and the WAL application is resumed.
> - "pg_ctl promote" to promote standby.
> - The time difference of "select pg_is_in_recovery();" from "t" to "f" is measured.
>
> [Results]
> Recovery/Failover performance (in seconds). 3 trial runs.
>
> | shared_buffers | master | patch | %reg |
> |----------------|--------|--------|---------|
> | 128MB | 32.406 | 33.785 | 4.08% |
> | 1GB | 36.188 | 32.747 | -10.51% |
> | 2GB | 41.996 | 32.88 | -27.73% |
>
> There's a bit of small regression with the default shared_buffers (128MB),
>

I feel we should try to address this. Basically, we can see the
smallest value of shared buffers above which the new algorithm is
beneficial and try to use that as threshold for doing this
optimization. I don't think it is beneficial to use this optimization
for a small value of shared_buffers.

> but as for the recovery time when we have large NBuffers, it's now at least almost constant
> so there's boosted performance. IOW, we enter the optimization most of the time
> during recovery.
>

Yeah, that is good to see. We can probably try to check with a much
larger value of shared buffers.

--
With Regards,
Amit Kapila.