On Mon, 8 Apr 2019 at 04:09, Tom Lane <t...@sss.pgh.pa.us> wrote: > Also, I would not define "significantly bloated" as "the table has > grown at all". I think the threshold ought to be at least ~100 > buckets, if we're starting at 16.
I've revised the patch to add a new constant named LOCKMETHODLOCALHASH_SHRINK_SIZE. I've set this to 64 for now. Once the hash table grows over that size we shrink it back down to LOCKMETHODLOCALHASH_INIT_SIZE, which I've kept at 16. I'm not opposed to setting it to 128. For this particular benchmark, it won't make any difference as it's only going to affect something that does not quite use 128 locks and has to work with a slightly bloated local lock table. I think hitting 64 locks in a transaction is a good indication that it's not a simple transaction so users are probably unlikely to notice the small slowdown from the hash table reinitialisation. Since quite a bit has changed around partition planning lately, I've taken a fresh set of benchmarks on today's master. I'm using something very close to Amit's benchmark from upthread. I just changed the query so we hit the same partition each time instead of a random one. create table ht (a int primary key, b int, c int) partition by hash (a); select 'create table ht' || x::text || ' partition of ht for values with (modulus 8192, remainder ' || (x)::text || ');' from generate_series(0,8191) x; \gexec select.sql: \set p 1 select * from ht where a = :p master @ a193cbec119 + shrink_bloated_locallocktable_v4.patch: plan_cache_mode = 'auto'; ubuntu@ip-10-0-0-201:~$ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 14101.226982 (excluding connections establishing) tps = 14034.250962 (excluding connections establishing) tps = 14107.937755 (excluding connections establishing) plan_cache_mode = 'force_custom_plan'; ubuntu@ip-10-0-0-201:~$ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 14240.366770 (excluding connections establishing) tps = 14272.244886 (excluding connections establishing) tps = 14130.684315 (excluding connections establishing) master @ a193cbec119: plan_cache_mode = 'auto'; ubuntu@ip-10-0-0-201:~$ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 10467.027666 (excluding connections establishing) tps = 10333.700917 (excluding connections establishing) tps = 10633.084426 (excluding connections establishing) plan_cache_mode = 'force_custom_plan'; ubuntu@ip-10-0-0-201:~$ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 13938.083272 (excluding connections establishing) tps = 14143.241802 (excluding connections establishing) tps = 14097.406758 (excluding connections establishing) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
shrink_bloated_locallocktable_v4.patch
Description: Binary data
