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
