Re: Write Ahead Logging for Hash Indexes

On 24/08/16 15:36, Amit Kapila wrote:
> On Wed, Aug 24, 2016 at 8:54 AM, Mark Kirkwood
> <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
>> On 24/08/16 12:09, Mark Kirkwood wrote:
>>> 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?
> Can you get the call stacks?
>

For every stuck backend? (just double checking)...