From: | "Tang, Haiying" <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com> |
---|---|
To: | "Tang, Haiying" <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com>, "Jamison, Kirk" <k(dot)jamison(at)fujitsu(dot)com>, 'Amit Kapila' <amit(dot)kapila16(at)gmail(dot)com> |
Cc: | "Tsunakawa, Takayuki" <tsunakawa(dot)takay(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>, "jankirk(dot)jamison(at)gmail(dot)com" <jankirk(dot)jamison(at)gmail(dot)com> |
Subject: | RE: [Patch] Optimize dropping of relation buffers using dlist |
Date: | 2021-01-07 03:57:44 |
Message-ID: | 87b6d367a3d34322aedd9eae5d612a96@G08CNEXMBPEKD05.g08.fujitsu.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>I'd like take a look at them and redo some of the tests using my machine. I'll send my test reults in a separate email after this.
I did the same tests with Kirk's scripts using the latest patch on my own machine. The results look pretty good and similar with Kirk's.
average of 5 runs.
[VACUUM failover test for 1000 relations] Unit is second, %reg=(patched-master)/ master
| s_b | Master | Patched | %reg |
|--------------|---------------|--------------|--------------|
| 128 MB | 0.408 | 0.308 | -24.44% |
| 1 GB | 0.809 | 0.308 | -61.94% |
| 20 GB | 12.529 | 0.308 | -97.54% |
| 100 GB | 59.310 | 0.369 | -99.38% |
[TRUNCATE failover test for 1000 relations] Unit is second, %reg=(patched-master)/ master
| s_b | Master | Patched | %reg |
|--------------|---------------|--------------|--------------|
| 128 MB | 0.287 | 0.207 | -27.91% |
| 1 GB | 0.688 | 0.208 | -69.84% |
| 20 GB | 12.449 | 0.208 | -98.33% |
| 100 GB | 61.800 | 0.207 | -99.66% |
Besides, I did the test for threshold value again. (I rechecked my test process and found out that I forgot to check the data synchronization state on standby which may introduce some NOISE to my results.)
The following results show we can't get optimize over NBuffers/32 just like Kirk's test results, so I do approve with Kirk on the threshold value.
%regression:
| rel_size |128MB|1GB|20GB| 100GB |
|----------|----|----|----|-------|
| NB/512 | 0% | 0% | 0% | -48% |
| NB/256 | 0% | 0% | 0% | -33% |
| NB/128 | 0% | 0% | 0% | -9% |
| NB/64 | 0% | 0% | 0% | -5% |
| NB/32 | 0% | 0% |-4% | -3% |
| NB/16 | 0% | 0% |-4% | 1% |
| NB/8 | 1% | 0% | 1% | 3% |
Optimization details(unit: second):
patched:
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64 NBuffers/32 NBuffers/16 NBuffers/8
-------------------------------------------------------------------------------------------------------------------------------------
128M 0.107 0.107 0.107 0.106 0.107 0.107 0.107
1G 0.107 0.107 0.107 0.107 0.107 0.107 0.107
20G 0.107 0.108 0.207 0.307 0.442 0.876 1.577
100G 0.208 0.308 0.559 1.060 1.961 4.567 7.922
master:
shared_buffers NBuffers/512 NBuffers/256 NBuffers/128 NBuffers/64 NBuffers/32 NBuffers/16 NBuffers/8
-------------------------------------------------------------------------------------------------------------------------------------
128M 0.107 0.107 0.107 0.107 0.107 0.107 0.106
1G 0.107 0.107 0.107 0.107 0.107 0.107 0.107
20G 0.107 0.107 0.208 0.308 0.457 0.910 1.560
100G 0.308 0.409 0.608 1.110 2.011 4.516 7.721
[Specs]
CPU : 40 processors (Intel(R) Xeon(R) Silver 4210 CPU @ 2.20GHz)
Memory: 128G
OS: CentOS 8
Any question to my test is welcome.
Regards,
Tang
From | Date | Subject | |
---|---|---|---|
Next Message | Bharath Rupireddy | 2021-01-07 04:01:44 | Re: Parallel Inserts in CREATE TABLE AS |
Previous Message | Peter Smith | 2021-01-07 03:53:23 | Re: Single transaction in the tablesync worker? |