On Wed, Jul 12, 2017 at 1:55 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Not to mention work done with a "buffer cleanup lock" held -- which is > compounded by the fact that acquiring such a lock is prone to starvation > if there are many scanners of that index. I've seen a case where a very > hot table is scanned so heavily that vacuum is starved for days waiting > to acquire cleanup on a single page (vacuum was only able to finish > because the app using the table was restarted). I'm sure that a uniform > distribution of keys, with a uniform distribution of values scanned, > would give a completely different behavior than a highly skewed > distribution where a single key receives a large fraction of the scans.
Good point. I'd be interested in seeing the difference it makes if Postgres is built with the call to _bt_check_unique() commented out within nbtinsert.c. The UPDATE query doesn't ever change indexed columns, so there should be no real need for the checking it does in this case. We're seeing a lot of duplicates in at least part of the keyspace, and yet the queries themselves should be as HOT-safe as possible. Another possibly weird thing that I noticed is latency standard deviation. This is from Alik's response to my request to run that SQL query: latency average = 1.375 ms tps = 93085.250384 (including connections establishing) tps = 93125.724773 (excluding connections establishing) SQL script 1: /home/nglukhov/ycsb_read_zipf.sql - weight: 1 (targets 50.0% of total) - 2782999 transactions (49.8% of total, tps = 46364.447705) - latency average = 0.131 ms - latency stddev = 0.087 ms SQL script 2: /home/nglukhov/ycsb_update_zipf.sql - weight: 1 (targets 50.0% of total) - 2780197 transactions (49.8% of total, tps = 46317.766703) - latency average = 2.630 ms - latency stddev = 14.092 ms This is from the 128 client case -- the slow case. Notice that the standard deviation is very high for ycsb_update_zipf.sql. I wonder if this degrades because of some kind of feedback loop, that reaches a kind of tipping point at higher client counts. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
