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