Re: Write Ahead Logging for Hash Indexes - Mailing list pgsql-hackers

On 23/08/16 15:24, Amit Kapila wrote:
>
> Thoughts?
>
> Note - To use this patch, first apply latest version of concurrent
> hash index patch [2].
>
> [1] - https://commitfest.postgresql.org/10/647/
> [2] - https://www.postgresql.org/message-id/CAA4eK1LkQ_Udism-Z2Dq6cUvjH3dB5FNFNnEzZBPsRjw0haFqA@mail.gmail.com
>
>

Firstly - really nice! Patches applied easily etc to latest version 10 
checkout.

I thought I'd test by initializing pgbench schema, adding a standby, 
then adding some hash indexes and running pgbench:

Looking on the standby:

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" PRIMARY KEY, btree (aid)    "pgbench_accounts_bid" hash (bid)    <====

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_bid" hash (bid)     <=====


they have been replicated there ok.

However I'm seeing a hang on the master after a while:

bench=# SELECT datname,application_name,state,now()-xact_start AS 
wait,query FROM pg_stat_activity ORDER BY wait DESC; datname | application_name | state  |      wait | query

---------+------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------------
       | walreceiver      | idle   |                 | bench   | pgbench          | active | 00:31:38.367467 | INSERT
INTO
 
pgbench_history (tid, bid, aid, delta, mtime) VALUES (921, 38, 251973, 
-3868, CURRENT_TIMESTAMP); bench   | pgbench          | active | 00:31:38.215378 | INSERT INTO 
pgbench_history (tid, bid, aid, delta, mtime) VALUES (280, 95, 3954814, 
2091, CURRENT_TIMESTAMP); bench   | pgbench          | active | 00:31:35.991056 | INSERT INTO 
pgbench_history (tid, bid, aid, delta, mtime) VALUES (447, 33, 8355237, 
3438, CURRENT_TIMESTAMP); bench   | pgbench          | active | 00:31:35.619798 | INSERT INTO 
pgbench_history (tid, bid, aid, delta, mtime) VALUES (134, 16, 4839994, 
-2443, CURRENT_TIMESTAMP); bench   | pgbench          | active | 00:31:35.544196 | INSERT INTO 
pgbench_history (tid, bid, aid, delta, mtime) VALUES (37, 73, 9620119, 
4053, CURRENT_TIMESTAMP); bench   | pgbench          | active | 00:31:35.334504 | UPDATE 
pgbench_branches SET bbalance = bbalance + -2954 WHERE bid = 33; bench   | pgbench          | active | 00:31:35.234112
|UPDATE 
 
pgbench_branches SET bbalance = bbalance + -713 WHERE bid = 38; bench   | pgbench          | active | 00:31:34.434676 |
UPDATE
 
pgbench_branches SET bbalance = bbalance + -921 WHERE bid = 33; bench   | psql             | active | 00:00:00        |
SELECT
 
datname,application_name,state,now()-xact_start AS wait,query FROM 
pg_stat_activity ORDER BY wait DESC;
(10 rows)

but no errors in the logs, any thoughts?

Cheers

Mark