Hello hackers!
Speaking of this patch, I've done a benchmark test on a master branch on 34740b90bc123d645a3a71231b765b778bdcf049
commitwith a patch by Thomas Munro:
https://www.postgresql.org/message-id/attachment/148040/0002-Use-ReadRecentBuffer-for-btree-root-page.patchand without
it.The configuration of the server was 96 cores and 1.5 TB of RAM.
The steps I did were:
1. Change the NUM_BUFFER_PARTITIONS to 1024:
/* Number of partitions of the shared buffer mapping hashtable */
#define NUM_BUFFER_PARTITIONS 1024
2. Build with O2 optimization:
CFLAGS='-g -O2 -fno-omit-frame-pointer' ./configure
3. Set up postgresql.conf to:
max_connections = 300
shared_buffers = 2GB
4. Run a pgbench:
psql -U postgres -c "CREATE DATABASE test;"
pgbench -i -s100 -IdtGpv --unlogged-tables -U postgres -d test
psql -U postgres -d test -c "create extension pg_prewarm;"
./bin/pgbench -n -j96 -c300 -T30 -P1 -M prepared -f script.sql -d test
script.sql is attached.
The idea was to address to different blocks in index and see whether it will affect PinBuffer efficiency.
Using a Wilcoxon signed-rank test I showed myself on 10 runs for each version that patch by Thomas speeds up the TPS by
median17k TPS in this benchmark with the possibility of error less than 5% on this configuration. Adding +34% TPS
comparingthe vanilla.
My conclusion is that this patch looks excellent on multicore systems and it would be great if it is committed.
Also I have a question if committed whether this patch would be backported to 18th version?
Kind regards,
Ian Ilyasov.
