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