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

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

In response to

Browse pgsql-hackers by date

  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?