From: | Bob Jolliffe <bobjolliffe(at)gmail(dot)com> |
---|---|
To: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> |
Cc: | "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: transaction blocking on COMMIT |
Date: | 2021-05-24 16:22:28 |
Message-ID: | CACd=f9dZW+0mLXnxRjxmxu9UZgGGEWKrMGKG2ZCjGpgdmTfSBw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
It is hard to say as it only happens for 30s couple of times per day.
Everything does return to normal after the blocking transaction is
committed. It could be a disk thing or even a network issue (the java
app is on a different machine to the db). But I never saw
transactions blocked in commit before so was wondering if there is any
rational set of reasons why it might do that.
On Mon, 24 May 2021 at 15:09, Vijaykumar Jain
<vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>
> No worries,
>
> There were some threads earlier which mentioned some automated changes to disk by the provider that resulted in some slowness.
>
> But otherwise also, do you query system, disk metrics.
>
> Do you see any anomaly in disk io (wait) when you saw blocking?
> If it did, did the io return to normal when blocks were cleared ?
>
>
>
> On Mon, May 24, 2021, 7:23 PM Bob Jolliffe <bobjolliffe(at)gmail(dot)com> wrote:
>>
>> Hello Jain
>>
>> Sorry forgot to indicate: it is running the ubuntu packaged version
>> 13.3 on ubuntu 20.04.
>>
>> It is not in the cloud, but is a VM in a government datacentre. I am
>> not sure of the underlying hyperviser. I could find out.
>>
>> Regards
>> Bob
>>
>>
>> On Mon, 24 May 2021 at 12:35, Vijaykumar Jain
>> <vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>> >
>> > I think there have been similar issues reported earlier as well. But it would be too early to generalize.
>> >
>> >
>> > Where is the db server running? Cloud?
>> >
>> > Also what is the version ?
>> >
>> >
>> > On Mon, May 24, 2021, 5:00 PM Bob Jolliffe <bobjolliffe(at)gmail(dot)com> wrote:
>> >>
>> >> I am seeing a strange issue on a database using jdbc. Regularly, 4 or
>> >> 5 times a day, I see something like a "stutter", where a bundle of
>> >> maybe 30 transactions suddenly finish at the same time. It looks like
>> >> (it is quite hard to catch this exactly) that the lead transaction
>> >> which has been blocking the rest has been blocked in COMMIT. In each
>> >> case it blocks for almost exactly 30s, just over, and once it goes
>> >> through, releases locks, and the others clear behind it.
>> >>
>> >> My question: what are the range of possibilities that might cause a
>> >> COMMIT to block? I haven't seen this before. Is there anything
>> >> suspicious about the regular 30s? Occasionally we see 60s, which
>> >> seems likely to be two sets of 30.
>> >>
>> >> Regards
>> >> Bob
>> >>
>> >>
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2021-05-24 16:24:09 | Re: transaction blocking on COMMIT |
Previous Message | Tom Lane | 2021-05-24 14:11:39 | Re: Optimising outer joins in the presence of non-nullable references |