I ran a SELECT-only pgbench test today on the IBM POWER7 box with 64
concurrent clients and got roughly 305,000 tps. Then, I created a
hash index on pgbench_accounts (aid), dropped the primary key, and
reran the test. I got roughly 104,000 tps. 'perf -g -e cs' suggested
lock contention in _hash_first(), so I whacked out the calls to
_hash_getlock(rel, 0, HASH_SHARE) and _hash_droplock(rel, 0,
HASH_SHARE). With that change, I got roughly 270,000 TPS. Taking a
little further, I then changed the definition of USELOCKING() to 0,
effectively removing all the heavyweight page locks. With that
change, I got 324,000 tps. Also, with this change, the CPU is fully
saturated - about 77% user time, 23% system time.
I don't immediately have a proposal to deal with this, but it seems
that if we want good hash index performance under high concurrency, we
need to work a bit harder.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company