Hi, I have updated the patch (v5). I tried to reduce the lock waiting times by using spinlock when inserting/deleting buffers in the new hash table, and exclusive lock when doing lookup for buffers to be dropped. In summary, instead of scanning the whole buffer pool in shared buffers, we just traverse the doubly-linked list of linked buffers for the target relation and block.
In order to understand how this patch affects performance, I also measured the cache hit rates in addition to benchmarking db with various shared buffer size settings. Using the same machine specs, I used the default script of pgbench for read-only workload with prepared statement, and executed about 15 runs for varying shared buffer sizes. pgbench -i -s 3200 test //(about 48GB db size) pgbench -S -n -M prepared -c 16 -j 16 -T 60 test [TPS Regression] shbuf | tps(master) | tps(patch) | %reg ---------+-----------------+-----------------+------- 5GB | 195,737.23 | 191,422.23 | 2.23 10GB | 197,067.93 | 194,011.66 | 1.55 20GB | 200,241.18 | 200,425.29 | -0.09 40GB | 208,772.81 | 209,807.38 | -0.50 50GB | 215,684.33 | 218,955.43 | -1.52 [CACHE HIT RATE] Shbuf | master | patch ----------+--------------+---------- 10GB | 0.141536 | 0.141485 20GB | 0.330088 | 0.329894 30GB | 0.573383 | 0.573377 40GB | 0.819499 | 0.819264 50GB | 0.999237 | 0.999577 For this workload, the regression increases for below 20GB shared_buffers size. However, the cache hit rate both for master and patch is 32% (20 GB shbuf). Therefore, I think we can consider this kind of workload with low shared buffers size as a “special case”, because in terms of db performance tuning we want as much as possible for the db to have a higher cache hit rate (99.9%, or maybe let's say 80% is acceptable). And in this workload, ideal shared_buffers size would be around 40GB more or less to hit that acceptable cache hit rate. Looking at this patch's performance result, if it's within the acceptable cache hit rate, there would be at least no regression and the results als show almost similar tps compared to master. Your feedback about the patch and tests are welcome. Regards, Kirk Jamison
v5-Optimize-dropping-of-relation-buffers-using-dlist.patch
Description: v5-Optimize-dropping-of-relation-buffers-using-dlist.patch
