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
recoverytime from this step)
Regards
Tang
