Re: Write Ahead Logging for Hash Indexes

On 09/09/16 14:50, Mark Kirkwood wrote:
>
> Yeah, good suggestion about replacing (essentially) all the indexes
> with hash ones and testing. I did some short runs with this type of
> schema yesterday (actually to get a feel for if hash performance vs
> btree was compareable - does seem tp be) - but probably longer ones
> with higher concurrency (as high as I can manage on a single socket i7
> anyway) is a good plan. If Ashutosh has access to seriously large
> numbers of cores then that is even better :-)
>
>

I managed to find a slightly bigger server (used our openstack cloud to
run a 16 cpu vm). With the schema modified as follows:

bench=# \d pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Modifiers
----------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
Indexes:
"pgbench_accounts_pkey" hash (aid)

bench=# \d pgbench_branches
Table "public.pgbench_branches"
Column | Type | Modifiers
----------+---------------+-----------
bid | integer | not null
bbalance | integer |
filler | character(88) |
Indexes:
"pgbench_branches_pkey" hash (bid)

bench=# \d pgbench_tellers
Table "public.pgbench_tellers"
Column | Type | Modifiers
----------+---------------+-----------
tid | integer | not null
bid | integer |
tbalance | integer |
filler | character(84) |
Indexes:
"pgbench_tellers_pkey" hash (tid)

bench=# \d pgbench_history
Table "public.pgbench_history"
Column | Type | Modifiers
--------+-----------------------------+-----------
tid | integer |
bid | integer |
aid | integer |
delta | integer |
mtime | timestamp without time zone |
filler | character(22) |
Indexes:
"pgbench_history_pkey" hash (bid)

performed several 10 hour runs on size 100 database using 32 and 64
clients. For the last run I rebuilt with assertions enabled. No hangs or
assertion failures.

regards

Mark