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

From: "Tang, Haiying" <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)fujitsu(dot)com>, "Jamison, Kirk" <k(dot)jamison(at)fujitsu(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, "Andres Freund" <andres(at)anarazel(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: [Patch] Optimize dropping of relation buffers using dlist
Date: 2020-12-30 05:57:52
Message-ID: 387c64c59fcb4566a03a32e04bbd184d@G08CNEXMBPEKD05.g08.fujitsu.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Amit,

In last mail(https://www.postgresql.org/message-id/66851e198f6b41eda59e6257182564b6%40G08CNEXMBPEKD05.g08.fujitsu.local),
I've sent you the performance test results(run only 1 time) on single table. Here is my the retested results(average by 15 times) which I think is more accurate.

In terms of 20G and 100G, the optimization on 100G is linear, but 20G is nonlinear(also include test results on shared buffers of 50G/60G), so it's a little difficult to decide the threshold from the two for me.
If just consider 100G, I think NBuffers/32 is the optimized max relation size. But I don't know how to judge for 20G. If you have any suggestion, kindly let me know.

#%reg 128M 1G 20G 100G
---------------------------------------------------------------
%reg(NBuffers/512) 0% -1% -5% -26%
%reg(NBuffers/256) 0% 0% 5% -20%
%reg(NBuffers/128) -1% -1% -10% -16%
%reg(NBuffers/64) -1% 0% 0% -8%
%reg(NBuffers/32) 0% 0% -2% -4%
%reg(NBuffers/16) 0% 0% -6% 4%
%reg(NBuffers/8) 1% 0% 2% -2%
%reg(NBuffers/4) 0% 0% 2% 2%

Optimization details(unit: second):
patched (sec)
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64 NBuffers/32 NBuffers/16 NBuffers/8 NBuffers/4
----------------------------------------------------------------------------------------------------------------------------------------------------------
128M 0.107 0.107 0.107 0.107 0.107 0.107 0.108 0.208
1G 0.107 0.108 0.107 0.108 0.208 0.208 0.308 0.409
20G 0.199 0.299 0.317 0.408 0.591 0.900 1.561 2.866
100G 0.318 0.381 0.645 0.992 1.913 3.640 6.615 13.389

master(HEAD) (sec)
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64 NBuffers/32 NBuffers/16 NBuffers/8 NBuffers/4
----------------------------------------------------------------------------------------------------------------------------------------------------------
128M 0.107 0.107 0.108 0.108 0.107 0.107 0.107 0.208
1G 0.108 0.108 0.108 0.108 0.208 0.207 0.308 0.409
20G 0.208 0.283 0.350 0.408 0.601 0.955 1.529 2.806
100G 0.400 0.459 0.751 1.068 1.984 3.506 6.735 13.101

Regards
Tang

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Smith 2020-12-30 06:08:30 Re: Single transaction in the tablesync worker?
Previous Message Dilip Kumar 2020-12-30 05:19:38 Re: Parallel Inserts in CREATE TABLE AS