From: Chris Hoover <chrish@aweber.com>
To: Paul Barrett <tinojam@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Outage caused by query in IPC:MessageQueueReceive wait????
Date: Fri, 14 Jun 2024 15:47:22 -0400
Message-ID: <290BB1BE-4F67-4CE5-84B3-67DA8B43DAFA@aweber.com> (raw)
In-Reply-To: <CADRMVzdU_YF4cJk_Cj17A-+jGq57gZXP4dgBee02hGjvKRTqmg@mail.gmail.com>
References: <A2F12A32-848A-4540-A34B-0E2431ACC88A@aweber.com>
<33F68D47-1D2D-4DB8-B753-BE83A3FA653E@aweber.com>
<CAF5PJVzhHAz1F55-xW_rNUYOZCBr2TYUtt0xpYAeTd+q7wA09w@mail.gmail.com>
<464C0807-4299-42B4-81E8-AC8D016C3AC7@aweber.com>
<CADRMVzdU_YF4cJk_Cj17A-+jGq57gZXP4dgBee02hGjvKRTqmg@mail.gmail.com>
Unfortunately I don’t. I do snapshot pg_locks and pg_stat_statements every minute (along with several other catalogs). I was able to back trace to this single query in a transaction causing issue:
```
snapped_at | 2024-06-14 10:00:00.024303-04
pid | 13900
...
backend_start | 2024-06-14 09:59:09.604535-04
xact_start | 2024-06-14 09:59:50.026618-04
query_start | 2024-06-14 09:59:50.881825-04
state_change | 2024-06-14 09:59:50.881826-04
wait_event_type | IPC
wait_event | MessageQueueReceive
state | active
backend_xid | 2334413815
backend_xmin | 2334413815
...
```
Here are the minute by minute log showing the query stuck for 16 minutes:
```
select snapped_at, pid, wait_event_type, wait_event from pg_stat_activity_snapshot_table where snapped_at >= '2024-06-14 09:50' and snapped_at < '2024-06-14 10:20' and pid = 13900 order by snapped_at
;
snapped_at | pid | wait_event_type | wait_event
-------------------------------+-------+-----------------+---------------------
2024-06-14 10:00:00.024303-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:01:00.013122-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:02:00.011187-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:03:00.010872-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:04:00.013126-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:05:00.019508-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:06:00.010661-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:07:00.011247-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:08:00.010288-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:09:00.011029-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:10:00.021545-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:11:00.011503-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:12:00.010562-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:13:00.011171-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:14:00.01087-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:15:00.01752-04 | 13900 | IPC | MessageQueueReceive
2024-06-14 10:16:00.010518-04 | 13900 | IPC | MessageQueueReceive
(17 rows)
```
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
> On Jun 14, 2024, at 2:27 PM, Paul Barrett <tinojam@gmail.com> wrote:
>
> Hi Chris,
> Do you have log_lock_waits enabled? If yes, check the Postgres log to see what process was causing the block/lock. I hope this helps.
>
> Thanks,
> Paul B
>
> On Fri, Jun 14, 2024 at 2:19 PM Chris Hoover <chrish@aweber.com <mailto:chrish@aweber.com>> wrote:
>> Yes, it appeared to be in a transaction. The pg_stat_activity.xact_start was before the query_start. Unfortunately, I don’t have the other queries. (I snapshot pg_stat_activity every minute, so it missed any prior queries to the blocker).
>>
>> Thanks,
>>
>>
>> Chris Hoover
>> Senior DBA
>> AWeber.com
>> Cell: (803) 528-2269
>> Email: chrish@aweber.com <mailto:chrish@aweber.com>
>>
>>
>>
>>> On Jun 14, 2024, at 2:16 PM, Ozgur Kulu <kuluozgur@gmail.com <mailto:kuluozgur@gmail.com>> wrote:
>>>
>>> Hi chris,
>>>
>>> Is this query in transaction block ?
>>>
>>> 14 Haz 2024 Cum 20:03 tarihinde Chris Hoover <chrish@aweber.com <mailto:chrish@aweber.com>> şunu yazdı:
>>>> Forgot to mention, this is on PG 16.1
>>>>
>>>> Thanks,
>>>>
>>>>
>>>> Chris Hoover
>>>> Senior DBA
>>>> AWeber.com
>>>> Cell: (803) 528-2269
>>>> Email: chrish@aweber.com <mailto:chrish@aweber.com>
>>>>
>>>>
>>>>
>>>>> On Jun 14, 2024, at 12:52 PM, Chris Hoover <chrish@aweber.com <mailto:chrish@aweber.com>> wrote:
>>>>>
>>>>> All,
>>>>>
>>>>> We had an outage today that appears to have been caused by a query in "IPC:MessageQueueReceive” wait state blocking another query from completing a truncate.
>>>>>
>>>>> Is there anyway to figure out what exactly it was waiting on other than an IPC message? I’m trying to get to the true root cause of our outage.
>>>>>
>>>>> Thanks,
>>>>>
>>>>>
>>>>> Chris Hoover
>>>>> Senior DBA
>>>>> AWeber.com
>>>>> Cell: (803) 528-2269
>>>>> Email: chrish@aweber.com <mailto:chrish@aweber.com>
>>>>>
>>>>>
>>>>>
>>>>
>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: chrish@aweber.com, tinojam@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Outage caused by query in IPC:MessageQueueReceive wait????
In-Reply-To: <290BB1BE-4F67-4CE5-84B3-67DA8B43DAFA@aweber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox