From: | khan Affan <bawag773(at)gmail(dot)com> |
---|---|
To: | Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Queries waiting on SyncRep even though synchronous_standby_names is absent |
Date: | 2024-07-18 09:20:51 |
Message-ID: | CAF4emOmFwBMtYnzGf7gw-tFvr6szJd96CVygTnRRQZNAd2mnrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi
Check the stuck transactions by
SELECT pid, username, state, query, xact_start
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL;
Forcefully truncate long or stuck transactions; if it still doesn't help,
restart the instance.
Thanks
*Muhammad Affan (*아판*)*
*PostgreSQL Technical Support Engineer** / Pakistan R&D*
Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan
On Thu, Jul 18, 2024 at 1:43 PM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
wrote:
> Hi Team,
>
> PostgreSQL : 13.6
>
> I saw a strange behavior today. All write queries were stuck on the
> 'SyncRep' wait event so I reset the synchronous_standby_names.
>
> postgres=# show synchronous_standby_names ;
>> synchronous_standby_names
>> ---------------------------
>>
>> (1 row)
>
>
> We still saw many queries waiting on SyncRep
>
> datid | datname | pid | leader_pid | usesysid | usename |
>> application_name | client_addr | client_hostname | client_port | backend_start
>> | xact_start | query_start
>> | state_change | wait_event_type | wait_event |
>> state | backend_xid | backend_xmin |
>> query | backend_type
>>
>> -------+----------+-------+------------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+------------------------
>>
>> -------+-------------------------------+-----------------+------------+--------+-------------+--------------+-------------------------------------------------------------------------------------+----------------
>> 16401 | repmgr | 24344 | | 16385 | postgres | psql
>> | | | -1 | 2024-07-14
>> 09:55:09.615682+00 | 2024-07-14 09:55:09.622254+00 | 2024-07-14 09:55:09.622
>> 254+00 | 2024-07-14 09:55:09.622255+00 | IPC | SyncRep |
>> active | 343975184 | 343974710 | UPDATE repmgr.sync_check SET
>> row_update_time = (select timezone('UTC', now())) | client backend
>> (1 row)
>
>
>
> Has anyone faced this issue or have any inputs
>
>
> Thank you,
>
> Nikhil
>
From | Date | Subject | |
---|---|---|---|
Next Message | khan Affan | 2024-07-18 09:32:29 | Re: Queries waiting on SyncRep even though synchronous_standby_names is absent |
Previous Message | Nikhil Shetty | 2024-07-18 08:43:29 | Queries waiting on SyncRep even though synchronous_standby_names is absent |