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
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 |