RE: [Patch] Optimize dropping of relation buffers using dlist - Mailing list pgsql-hackers

On Tuesday, November 10, 2020 12:27 PM, Horiguchi-san wrote:
> To: amit.kapila16@gmail.com
> Cc: Jamison, Kirk/ジャミソン カーク <k.jamison@fujitsu.com>; Tsunakawa,
> Takayuki/綱川 貴之 <tsunakawa.takay@fujitsu.com>; tgl@sss.pgh.pa.us;
> andres@anarazel.de; robertmhaas@gmail.com;
> tomas.vondra@2ndquadrant.com; pgsql-hackers@postgresql.org
> Subject: Re: [Patch] Optimize dropping of relation buffers using dlist
>
> At Tue, 10 Nov 2020 08:33:26 +0530, Amit Kapila <amit.kapila16@gmail.com>
> wrote in
> > On Tue, Nov 10, 2020 at 8:19 AM k.jamison@fujitsu.com
> > <k.jamison@fujitsu.com> wrote:
> > >
> > > I repeated the recovery performance test for vacuum. (I made a
> > > mistake previously in NBuffers/128) The 3 kinds of thresholds are almost
> equally performant. I chose NBuffers/256 for this patch.
> > >
> > > | s_b   | Master | NBuffers/512 | NBuffers/256 | NBuffers/128 |
> > > |-------|--------|--------------|--------------|--------------|
> > > | 128MB | 1.006  | 1.007        | 1.007        | 1.007        |
> > > | 1GB   | 0.706  | 0.606        | 0.606        | 0.606        |
> > > | 20GB  | 1.907  | 0.606        | 0.606        | 0.606        |
> > > | 100GB | 7.013  | 0.706        | 0.606        | 0.606        |
> > >
> >
> > I think this data is not very clear. What is the unit of time? What is
> > the size of the relation used for the test? Did the test use an
> > optimized path for all cases? If at 128MB, there is no performance
> > gain, can we consider the size of shared buffers as 256MB as well for
> > the threshold?
>
> In the previous testing, it was shown as:
>
> Recovery Time (in seconds)
> | s_b   | master | patched | %reg   |
> |-------|--------|---------|--------|
> | 128MB | 3.043  | 2.977   | -2.22% |
> | 1GB   | 3.417  | 3.41    | -0.21% |
> | 20GB  | 20.597 | 2.409   | -755%  |
> | 100GB | 66.862 | 2.409   | -2676% |
>
>
> So... The numbers seems to be in seconds, but the master gets about 10
> times faster than this result for uncertain reasons. It seems that the result
> contains something different from the difference by this patch as a larger
> part.

The unit is in seconds.
The results that Horiguchi-san mentioned was the old test case I used where I vacuumed
database with 1000 relations that have been deleted.
I used a new test case in my last results that's why they're smaller:
VACUUM 1 parent table (350 MB) and 100 child partition tables (6 MB each)
in separate transcations after deleting the tables. After vacuum, the
parent table became 16kB and each child table was 2224kB.

I added the test for 256MB shared_buffers, and the performance is also almost the same.
We gain performance benefits for the larger shared_buffers.

| s_b    | Master | NBuffers/512 | NBuffers/256 | NBuffers/128 |
|--------|--------|--------------|--------------|--------------|
| 128MB  | 1.006  | 1.007        | 1.007        | 1.007        |
| 256 MB | 1.006  | 1.006        | 0.906        | 0.906        |
| 1GB    | 0.706  | 0.606        | 0.606        | 0.606        |
| 20GB   | 1.907  | 0.606        | 0.606        | 0.606        |
| 100GB  | 7.013  | 0.706        | 0.606        | 0.606        |

Regards,
Kirk Jamison