From: | "Tang, Haiying" <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com> |
---|---|
To: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, "Jamison, Kirk" <k(dot)jamison(at)fujitsu(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-04 03:31:01 |
Message-ID: | e68f69f792a34151a0722af724e5ed39@G08CNEXMBPEKD05.g08.fujitsu.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Amit,
Sorry for my late reply. Here are my answers for your earlier questions.
>BTW, it is not clear why the advantage for single table is not as big as multiple tables with the Truncate command
I guess it's the amount of table blocks caused this difference. For single table I tested the amount of block is threshold.
For multiple tables I test the amount of block is a value(like: one or dozens or hundreds) which far below threshold.
The closer table blocks to the threshold, the less advantage raised.
I tested below 3 situations of 50 tables when shared buffers=20G / 100G.
1. For multiple tables which had one or dozens or hundreds blocks(far below threshold) per table, we got significant improve, like [1].
2. For multiple tables which has half threshold blocks per table, advantage become less, like [2].
3. For multiple tables which has threshold blocks per table, advantage become more less, like [3].
[1]. 247 blocks per table
s_b master patched %reg((patched-master)/patched)
----------------------------------------------------
20GB 1.109 0.108 -927%
100GB 3.113 0.108 -2782%
[2]. NBuffers/256/2 blocks per table
s_b master patched %reg
----------------------------------------------------
20GB 2.012 1.210 -66%
100GB 10.226 6.4 -60%
[3]. NBuffers/256 blocks per table
s_b master patched %reg
----------------------------------------------------
20GB 3.868 2.412 -60%
100GB 14.977 10.591 -41%
>Can you share your exact test steps for any one of the tests? Also, did you change autovacumm = off for these tests?
Yes, I configured streaming replication environment as Kirk did before.
autovacumm = off.
full_page_writes = off.
checkpoint_timeout = 30min
Test steps:
e.g. shared_buffers=20G, NBuffers/512, table blocks= 20*1024*1024/8/512=5120 . table size(kB)= 20*1024*1024/512=40960kB
1. (Master) create table test(id int, v_ch varchar, v_ch1 varchar);
2. (Master) insert about 40MB data to table.
3. (Master) delete from table (all rows of table)
4. (Standby) To test with failover, pause the WAL replay on standby server.
SELECT pg_wal_replay_pause();
5. (Master) VACUUM;
6. (Master) Stop primary server. pg_ctl stop -D $PGDATA -w 7. (Standby) Resume wal replay and promote standby. (get the recovery time from this step)
Regards
Tang
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-01-04 03:49:24 | Test harness for regex code (to allow importing Tcl's test suite) |
Previous Message | Kyotaro Horiguchi | 2021-01-04 03:06:10 | Re: A failure of standby to follow timeline switch |