Hi, While working on something else, I noticed that each WAL insert lock tracks its own last important WAL record's LSN (lastImportantAt) and both the bgwriter and checkpointer later computes the max value/server-wide last important WAL record's LSN via GetLastImportantRecPtr(). While doing so, each WAL insertion lock is acquired in exclusive mode in a for loop. This seems like too much overhead to me. I quickly coded a patch (attached herewith) that tracks the server-wide last important WAL record's LSN in XLogCtlInsert (lastImportantPos) protected with a spinlock and gets rid of lastImportantAt from each WAL insert lock. I ran pgbench with a simple insert [1] and the results are below. While the test was run, the GetLastImportantRecPtr() was called 4-5 times.
# of clients HEAD PATCHED 1 83 82 2 159 157 4 303 302 8 576 570 16 1104 1095 32 2055 2041 64 2286 2295 128 2270 2285 256 2302 2253 512 2205 2290 768 2224 2180 1024 2109 2150 2048 1941 1936 4096 1856 1848 It doesn't seem to hurt (for this use-case) anyone, however there might be some benefit if bgwriter and checkpointer come in the way of WAL inserters. With the patch, the extra exclusive lock burden on WAL insert locks is gone. Since the amount of work the WAL inserters do under the new spinlock is very minimal (updating XLogCtlInsert->lastImportantPos), it may not be an issue. Also, it's worthwhile to look at the existing comment [2], which doesn't talk about the performance impact of having a lock. Thoughts? [1] ./configure --prefix=$PWD/inst/ CFLAGS="-O3" > install.log && make -j 8 install > install.log 2>&1 & cd inst/bin ./pg_ctl -D data -l logfile stop rm -rf data logfile insert.sql free -m sudo su -c 'sync; echo 3 > /proc/sys/vm/drop_caches' free -m ./initdb -D data ./pg_ctl -D data -l logfile start ./psql -d postgres -c 'ALTER SYSTEM SET shared_buffers = "8GB";' ./psql -d postgres -c 'ALTER SYSTEM SET max_wal_size = "32GB";' ./psql -d postgres -c 'ALTER SYSTEM SET max_connections = "4096";' ./psql -d postgres -c 'ALTER SYSTEM SET bgwriter_delay = "10ms";' ./pg_ctl -D data -l logfile restart ./pgbench -i -s 1 -d postgres ./psql -d postgres -c "ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;" cat << EOF >> insert.sql \set aid random(1, 10 * :scale) \set delta random(1, 100000 * :scale) INSERT INTO pgbench_accounts (aid, bid, abalance) VALUES (:aid, :aid, :delta); EOF for c in 1 2 4 8 16 32 64 128 256 512 768 1024 2048 4096; do echo -n "$c ";./pgbench -n -M prepared -U ubuntu postgres -b simple-update -c$c -j$c -T5 2>&1|grep '^tps'|awk '{print $3}';done [2] * records. Tracking the WAL activity directly in WALInsertLock has the * advantage of not needing any additional locks to update the value. -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
v1-0001-Decouple-last-important-WAL-record-LSN-from-WAL-i.patch
Description: Binary data