Skip site navigation (1) Skip section navigation (2)

hash index concurrency

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: hash index concurrency
Date: 2012-05-30 00:19:31
Message-ID: CA+TgmoaF_tR81s1CtCBVhQjSxCS769hVBUnnOfvd_B7ZJB74=w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Sergey KoposovDate: 2012-05-30 00:40:01
Subject: slow dropping of tables, DropRelFileNodeBuffers, tas
Previous:From: Jim NasbyDate: 2012-05-29 23:52:25
Subject: Re: temporal support patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group