Re: [HACKERS] Lazy hash table for XidInMVCCSnapshot (helps Zipfian a bit)

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Stephen Frost <sfrost(at)snowman(dot)net>, Sokolov Yura <funny(dot)falcon(at)postgrespro(dot)ru>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, pgsql-hackers-owner(at)postgresql(dot)org
Subject: Re: [HACKERS] Lazy hash table for XidInMVCCSnapshot (helps Zipfian a bit)
Date: 2018-03-05 14:44:43
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers


I've done a bit of benchmarking on the last patch version (from 22/8),
using a simple workload:

1) 8 clients doing

SELECT SUM(abalance) FROM pgbench_accounts

with the table truncated to only 10k rows

2) variable number (8, 16, 32, ..., 512) of write clients, doing this

\set aid random(1, 10000 * :scale)
UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = :aid;
SELECT pg_sleep(0.005);

The idea is to measure tps of the read-only clients, with many throttled
write clients (idle in transaction for 5ms after each update). This
should generate snapshots with many XIDs. Scripts attached, of course.

The results look really promising (see the attached chart and .ods):

write clients master patched
8 7048 7089
16 6601 6614
32 5862 5944
64 5327 5349
128 4574 4952
256 4132 4956
512 2196 4930

That being said, I think Stephen is right that there's a bug in
CopySnapshot, and I agree with his other comments too. I think the patch
should have been in WoA, so I'll mark it like that.

simplehash is great, but I'm not sure it's a good fit for this use case.
Seems a bit overkill to me in this case, but maybe I'm wrong.

Snapshots are static (we don't really add new XIDs into existing ones,
right?), so why don't we simply sort the XIDs and then use bsearch to
lookup values? That should fix the linear search, without need for any
local hash table.


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
xid-select.sql application/sql 44 bytes
xid-update.sql application/sql 144 bytes application/x-shellscript 431 bytes
xid-hash.ods application/vnd.oasis.opendocument.spreadsheet 18.6 KB
tests.ods application/vnd.oasis.opendocument.spreadsheet 18.6 KB
image/png 13.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2018-03-05 14:47:37 Re: PATCH: psql tab completion for SELECT
Previous Message Tom Lane 2018-03-05 14:41:43 Re: PATCH: psql tab completion for SELECT