pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOutage caused by query in IPC:MessageQueueReceive wait????
4+ messages / 1 participants
[nested] [flat]
* Outage caused by query in IPC:MessageQueueReceive wait????
@ 2024-06-14 16:52 Chris Hoover <chrish@aweber.com>
2024-06-14 17:02 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Hoover @ 2024-06-14 16:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Outage caused by query in IPC:MessageQueueReceive wait????
2024-06-14 16:52 Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
@ 2024-06-14 17:02 ` Chris Hoover <chrish@aweber.com>
2024-06-14 18:19 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Hoover @ 2024-06-14 17:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Forgot to mention, this is on PG 16.1
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
> On Jun 14, 2024, at 12:52 PM, Chris Hoover <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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Outage caused by query in IPC:MessageQueueReceive wait????
2024-06-14 16:52 Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
2024-06-14 17:02 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
@ 2024-06-14 18:19 ` Chris Hoover <chrish@aweber.com>
2024-06-14 19:47 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Hoover @ 2024-06-14 18:19 UTC (permalink / raw)
To: Ozgur Kulu <kuluozgur@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
> On Jun 14, 2024, at 2:16 PM, Ozgur Kulu <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>
>>>
>>>
>>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Outage caused by query in IPC:MessageQueueReceive wait????
2024-06-14 16:52 Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
2024-06-14 17:02 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
2024-06-14 18:19 ` Re: Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
@ 2024-06-14 19:47 ` Chris Hoover <chrish@aweber.com>
0 siblings, 0 replies; 4+ messages in thread
From: Chris Hoover @ 2024-06-14 19:47 UTC (permalink / raw)
To: Paul Barrett <tinojam@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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>
>>>>>
>>>>>
>>>>>
>>>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-06-14 19:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-14 16:52 Outage caused by query in IPC:MessageQueueReceive wait???? Chris Hoover <chrish@aweber.com>
2024-06-14 17:02 ` Chris Hoover <chrish@aweber.com>
2024-06-14 18:19 ` Chris Hoover <chrish@aweber.com>
2024-06-14 19:47 ` Chris Hoover <chrish@aweber.com>
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