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

Hi Amit,

>But how can we conclude NBuffers/128 is the maximum relation size?
>Because the maximum size would be where the performance is worse than 
>the master, no? I guess we need to try by NBuffers/64, NBuffers/32, 
>.... till we get the threshold where master performs better.

You are right, we should keep on testing until no optimization.

>I think we should find a better way to display these numbers because in 
>cases like where master takes 537.978s and patch takes 3.815s

Yeah, I think we can change the %reg formula from (patched- master)/ master to (patched- master)/ patched.

>Table size should be more than 8k to get all this data because 8k means 
>just one block. I guess either it is a typo or some other mistake.

8k here is the relation size, not data size. 
For example, when I tested recovery performance of 400M relation size, I used 51200 tables(8k per table).
Please let me know if you think this is not appropriate.

Regards
Tang

-----Original Message-----
From: Amit Kapila <amit.kapila16@gmail.com> 
Sent: Thursday, December 24, 2020 9:11 PM
To: Tang, Haiying/唐 海英 <tanghy.fnst@cn.fujitsu.com>
Cc: Tsunakawa, Takayuki/綱川 貴之 <tsunakawa.takay@fujitsu.com>; Jamison, Kirk/ジャミソン カーク <k.jamison@fujitsu.com>; Kyotaro
Horiguchi<horikyota.ntt@gmail.com>; Andres Freund <andres@anarazel.de>; Tom Lane <tgl@sss.pgh.pa.us>; Thomas Munro
<thomas.munro@gmail.com>;Robert Haas <robertmhaas@gmail.com>; Tomas Vondra <tomas.vondra@2ndquadrant.com>;
pgsql-hackers<pgsql-hackers@postgresql.org>
 
Subject: Re: [Patch] Optimize dropping of relation buffers using dlist

On Thu, Dec 24, 2020 at 2:31 PM Tang, Haiying <tanghy.fnst@cn.fujitsu.com> wrote:
>
> Hi Amit, Kirk
>
> >One idea could be to remove "nBlocksToInvalidate < 
> >BUF_DROP_FULL_SCAN_THRESHOLD" part of check "if (cached && 
> >nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)" so that it 
> >always use optimized path for the tests. Then use the relation size 
> >as NBuffers/128, NBuffers/256, NBuffers/512 for different values of 
> >shared buffers as 128MB, 1GB, 20GB, 100GB.
>
> I followed your idea to remove check and use different relation size for different shared buffers as
128M,1G,20G,50G(myenvironment can't support 100G, so I choose 50G).
 
> According to results, all three thresholds can get optimized, even NBuffers/128 when shared_buffers > 128M.
> IMHO, I think NBuffers/128 is the maximum relation size we can get optimization in the three thresholds, Please let
meknow if I made something wrong.
 
>

But how can we conclude NBuffers/128 is the maximum relation size?
Because the maximum size would be where the performance is worse than the master, no? I guess we need to try by
NBuffers/64,NBuffers/32, .... till we get the threshold where master performs better.
 

> Recovery after vacuum test results as below ' Optimized percentage' and ' Optimization details(unit: second)' shows:
> (512),(256),(128): means relation size is NBuffers/512, NBuffers/256, 
> NBuffers/128
> %reg: means (patched(512)- master(512))/ master(512)
>
> Optimized percentage:
> shared_buffers  %reg(512)       %reg(256)       %reg(128)
> -----------------------------------------------------------------
> 128M            0%              -1%             -1%
> 1G              -65%            -49%            -62%
> 20G             -98%            -98%            -98%
> 50G             -99%            -99%            -99%
>
> Optimization details(unit: second):
> shared_buffers  master(512)     patched(512)    master(256)     patched(256)    master(128)     patched(128)
>
-----------------------------------------------------------------------------------------------------------------------------
> 128M            0.108           0.108           0.109           0.108           0.109           0.108
> 1G              0.310           0.107           0.410           0.208           0.811           0.309
> 20G             94.493          1.511           188.777         3.014           380.633         6.020
> 50G             537.978         3.815           867.453         7.524           1559.076        15.541
>

I think we should find a better way to display these numbers because in cases like where master takes 537.978s and
patchtakes 3.815s, it is clear that patch has reduced the time by more than 100 times whereas in your table it shows
99%.

> Test prepare:
> Below is test table amount for different shared buffers. Each table 
> size is 8k,
>

Table size should be more than 8k to get all this data because 8k means just one block. I guess either it is a typo or
someother mistake.
 

--
With Regards,
Amit Kapila.