Thread: transaction blocking on COMMIT
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
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@gmail.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
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.github@gmail.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@gmail.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 >> >>
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@gmail.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.github@gmail.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@gmail.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
>>
>>
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.github@gmail.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@gmail.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.github@gmail.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@gmail.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 >> >> >> >>
> On May 24, 2021, at 09:22, Bob Jolliffe <bobjolliffe@gmail.com> wrote: > > 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. One thing you can check is to turn off synchronous_commit (understanding the possibility of "time loss" in the event of asystem crash). If that mitigates the problem, the issue is likely the I/O subsystem blocking during the fsync() operation.
On 05/24/21 19:24, Christophe Pettus wrote: > >> On May 24, 2021, at 09:22, Bob Jolliffe <bobjolliffe@gmail.com> wrote: >> >> 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. > One thing you can check is to turn off synchronous_commit (understanding the possibility of "time loss" in the event ofa system crash). If that mitigates the problem, the issue is likely the I/O subsystem blocking during the fsync() operation. > > Just a question. Is there a btrfs(with compression maybe) around? 30 seconds is a commit(file system) timeout for btrfs. Some processes like btrfs cleaner/allocate/worker on top of CPU/io use?
No brtfs. We are going to try turning off synchronous_commit temporarily to see if there are underlying I/O issues. On Mon, 24 May 2021 at 22:59, Alexey M Boltenkov <padrebolt@yandex.ru> wrote: > > On 05/24/21 19:24, Christophe Pettus wrote: > > > >> On May 24, 2021, at 09:22, Bob Jolliffe <bobjolliffe@gmail.com> wrote: > >> > >> 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. > > One thing you can check is to turn off synchronous_commit (understanding the possibility of "time loss" in the eventof a system crash). If that mitigates the problem, the issue is likely the I/O subsystem blocking during the fsync()operation. > > > > > Just a question. Is there a btrfs(with compression maybe) around? 30 > seconds is a commit(file system) timeout for btrfs. Some processes like > btrfs cleaner/allocate/worker on top of CPU/io use? >