Re: [HACKERS] [WIP] Zipfian distribution in pgbench - Mailing list pgsql-hackers

From Peter Geoghegan
Subject Re: [HACKERS] [WIP] Zipfian distribution in pgbench
Date
Msg-id CAH2-WzmBixGB6SZ9P67YLPUU6n5SUz6=7--rohBX9f_c-k_dZg@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] [WIP] Zipfian distribution in pgbench  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Responses Re: [HACKERS] [WIP] Zipfian distribution in pgbench
List pgsql-hackers
On Wed, Jul 12, 2017 at 1:55 PM, Alvaro Herrera
<alvherre@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



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: [HACKERS] [WIP] Zipfian distribution in pgbench
Next
From: Peter Geoghegan
Date:
Subject: Re: [HACKERS] [WIP] Zipfian distribution in pgbench