need quick way to find whom is causing Blocked:LON
A DBA on IDS 11.70 hit a Blocked:LONGTX that appeared to hang the database and asked for the fastest way to identify the offending session. Replies: use onstat -x to spot the thread whose begin_logpos is far behind current logpos (rb_time gives a rollback estimate), map it via onstat -u to a session, then onstat -g ses; sysmaster:systxptab allows scripting, and the online log names the culprit. Killing the session doesn't help since the undo daemon continues the rollback; instead widen the gap between LTXHWM and LTXEHWM, use dynamic logs, add log space, and alert on growing transactions before LONGTX triggers. The cause was a bulk update of nulls after a schema change; the poster accepted the tuning/monitoring advice.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
OS: HPUX 11.31 (11iv3) on BL870x IA server IDS: 11.70.FC4 Friday afternoon we had a Blocked:LONGTX that locked up the database for a while. I was informed while in a meeting that the "database is down" about 40 minutes after the Blocked:LONGTX started. It took me a way too much time to find the session that had the block and to kill that session; I was wonder what would have been the quickest way to find who had the block that was preventing the rollback. As I'm sure I chose a less than speedy way to do find it. John Adamski, Sr. Network Specialist Graceland University, 1 University Place, Lamoni, IA 50140 adamski@graceland.edu
There is probably a way to do it with the SMI tables, but quick and dirty
way using onstats would be to
onstat -x
to find the user thread that has a begin_logpos in a old logical log that is
causing the engine to block because of a long transaction
Transactions
est.
address flags userthread locks begin_logpos current
logpos isol rb_time retrys coord
ec979608 A-B-- e7122898 14 45053:0x39fd018
45053:0x3a01098 COMMIT 0:00 0
onstat -u | grep <userthread)
e7122898 Y-BP--- 163803105 informix 15 eba346a8 0
14 7 0
to find the session id associated with this thread (3rd column of onstat -u
output)
onstat -g ses <session id>
to find out what this session is doing.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Adamski
Sent: Monday, March 04, 2013 10:09 AM
To: ids@iiug.org
Subject: need quick way to find whom is causing Blocked.... [29670]
OS: HPUX 11.31 (11iv3) on BL870x IA server
IDS: 11.70.FC4
Friday afternoon we had a Blocked:LONGTX that locked up the database for a
while. I was informed while in a meeting that the "database is down" about
40 minutes after the Blocked:LONGTX started.
It took me a way too much time to find the session that had the block and to
kill that session; I was wonder what would have been the quickest way to
find who had the block that was preventing the rollback. As I'm sure I chose
a less than speedy way to do find it.
John Adamski, Sr. Network Specialist
Graceland University, 1 University Place, Lamoni, IA 50140
adamski@graceland.edu
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
John, In the online log for the instance should be the details of what session caused the long transaction. As for a block I am not sure it was blocked. As the instance was rolling back from the long transaction this can take quite a while. Sometimes hours. > To: ids@iiug.org > From: adamski@graceland.edu > Subject: need quick way to find whom is causing Blocked.... [29670] > Date: Mon, 4 Mar 2013 11:09:28 -0500 > > OS: HPUX 11.31 (11iv3) on BL870x IA server > IDS: 11.70.FC4 > > Friday afternoon we had a Blocked:LONGTX that locked up the database for a > while. I was informed while in a meeting that the "database is down" about 40 > minutes after the Blocked:LONGTX started. > > It took me a way too much time to find the session that had the block and to > kill that session; I was wonder what would have been the quickest way to find > who had the block that was preventing the rollback. As I'm sure I chose a less > than speedy way to do find it. > > John Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Notice in Andrew's post that the output shows both the current log position and the beginning log position for the current transaction. You can have a cron or Informix Task Manager task poll this data periodically and find sessions with more than a given gap between the two and report them to you via text or email BEFORE they begin a formal LONGTX rollback so you can force a manual rollback at an earlier stage of the process. This will take less time to rollback and will not block anything. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 11:09 AM, John Adamski <adamski@graceland.edu> wrote: > OS: HPUX 11.31 (11iv3) on BL870x IA server > IDS: 11.70.FC4 > > Friday afternoon we had a Blocked:LONGTX that locked up the database for a > while. I was informed while in a meeting that the "database is down" about > 40 > minutes after the Blocked:LONGTX started. > > It took me a way too much time to find the session that had the block and > to > kill that session; I was wonder what would have been the quickest way to > find > who had the block that was preventing the rollback. As I'm sure I chose a > less > than speedy way to do find it. > > John Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042d051ca99e0d04d71c7e5a
You might also want to modify the LTXHWM ONCONFIG from the default of 70% which can be kind of high depending on the amount of logical logs you have configured. For example, if you have 16GB of logical logs configured, do you really want a transaction to be able to span 11GB worth of logical logs before the engine identifies it as a long transaction and rolls it back? Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, March 04, 2013 11:15 AM To: ids@iiug.org Subject: Re: need quick way to find whom is causing Blo.... [29675] Notice in Andrew's post that the output shows both the current log position and the beginning log position for the current transaction. You can have a cron or Informix Task Manager task poll this data periodically and find sessions with more than a given gap between the two and report them to you via text or email BEFORE they begin a formal LONGTX rollback so you can force a manual rollback at an earlier stage of the process. This will take less time to rollback and will not block anything. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 11:09 AM, John Adamski <adamski@graceland.edu> wrote: > OS: HPUX 11.31 (11iv3) on BL870x IA server > IDS: 11.70.FC4 > > Friday afternoon we had a Blocked:LONGTX that locked up the database > for a while. I was informed while in a meeting that the "database is > down" about > 40 > minutes after the Blocked:LONGTX started. > > It took me a way too much time to find the session that had the block > and to kill that session; I was wonder what would have been the > quickest way to find who had the block that was preventing the > rollback. As I'm sure I chose a less than speedy way to do find it. > > John Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042d051ca99e0d04d71c7e5a **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Just a few points here.
Killing a session who is rolling back a long transaction will not help.
The servers undo daemon will assume the rollback for this session.
I believe what you want to look at is the difference between these two
parameters. The first parameter LTXHWM will automatically force
the session to start a rollback and NO other users will be effected by this
one persons rollback. LTXEHWM (the parameter with the "E")
is the threshold which provides EXCLUSIVE access to the logical logs and
block all users attempting to write data. If you encounter
this blockage my suggestion would be to increase the difference between
this to values. By default hey come set at 70% and 80%. My
suggestion would be to drop LTXHWM to 50%, What this means is any
transaction which consume more than 50% of the logical logs will
be forced to rollback and if it consume up to 80% of the logical logs it
will stop others from writing to the logical logs and provide this
transaction with exclusive resources.
# LTXHWM - The percentage of the logical logs that can be
# filled before a transaction is determined to be a
# long transaction and is rolled back
# LTXEHWM - The percentage of the logical logs that have been
# filled before the server suspends all other
# transactions so that the long transaction being
# rolled back has exclusive use of the logs
The other solution would be to utilize dynamic logs, which causes the
logical logs to grow upon hitting a long transaction.
My last point would be to let you know that onstat -x has been enhanced to
provide an estimation of how long it
will take to rollback transactions. The column in onstat -x is called
"rb_time".
If you want to look at this information programmatically look at the table
called sysmaster:systxptab
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/04/2013 08:09:28 AM:
> From: "John Adamski" <adamski@graceland.edu>
> To: ids@iiug.org,
> Date: 03/04/2013 08:21 AM
> Subject: need quick way to find whom is causing Blocked.... [29670]
> Sent by: ids-bounces@iiug.org
>
> OS: HPUX 11.31 (11iv3) on BL870x IA server
> IDS: 11.70.FC4
>
> Friday afternoon we had a Blocked:LONGTX that locked up the database for
a
> while. I was informed while in a meeting that the "database is down"about
40
> minutes after the Blocked:LONGTX started.
>
> It took me a way too much time to find the session that had the block and
to
> kill that session; I was wonder what would have been the quickest way to
find
> who had the block that was preventing the rollback. As I'm sure I
> chose a less
> than speedy way to do find it.
>
> John Adamski, Sr. Network Specialist
> Graceland University, 1 University Place, Lamoni, IA 50140
> adamski@graceland.edu
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
We have LTXHWM set to 50 and our logical logs are small compared to most Informix installs. So most of the time know one knows we experienced a rollback. This time the rollback got stopped and everything seems to have come to a screeching halt. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew Ford Sent: Monday, March 04, 2013 12:05 PM To: ids@iiug.org Subject: RE: need quick way to find whom is causing Blo.... [29677] You might also want to modify the LTXHWM ONCONFIG from the default of 70% which can be kind of high depending on the amount of logical logs you have configured. For example, if you have 16GB of logical logs configured, do you really want a transaction to be able to span 11GB worth of logical logs before the engine identifies it as a long transaction and rolls it back? Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, March 04, 2013 11:15 AM To: ids@iiug.org Subject: Re: need quick way to find whom is causing Blo.... [29675] Notice in Andrew's post that the output shows both the current log position and the beginning log position for the current transaction. You can have a cron or Informix Task Manager task poll this data periodically and find sessions with more than a given gap between the two and report them to you via text or email BEFORE they begin a formal LONGTX rollback so you can force a manual rollback at an earlier stage of the process. This will take less time to rollback and will not block anything. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 4, 2013 at 11:09 AM, John Adamski <adamski@graceland.edu> wrote: > OS: HPUX 11.31 (11iv3) on BL870x IA server > IDS: 11.70.FC4 > > Friday afternoon we had a Blocked:LONGTX that locked up the database > for a while. I was informed while in a meeting that the "database is > down" about > 40 > minutes after the Blocked:LONGTX started. > > It took me a way too much time to find the session that had the block > and to kill that session; I was wonder what would have been the > quickest way to find who had the block that was preventing the > rollback. As I'm sure I chose a less than speedy way to do find it. > > John Adamski, Sr. Network Specialist > Graceland University, 1 University Place, Lamoni, IA 50140 > adamski@graceland.edu > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042d051ca99e0d04d71c7e5a **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
My rule-of-thumb for logical logs is that you should have enough logical log space to last for 4 typical days worth of transactions so that if the log backups fail over a long weekend you don't have to worry about it until Tuesday when you get back to the office. ;-) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 5, 2013 at 9:00 AM, John Adamski <adamski@graceland.edu> wrote: > We have LTXHWM set to 50 and our logical logs are small compared to most > Informix installs. So most of the time know one knows we experienced a > rollback. This time the rollback got stopped and everything seems to have > come > to a screeching halt. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Andrew > Ford > Sent: Monday, March 04, 2013 12:05 PM > To: ids@iiug.org > Subject: RE: need quick way to find whom is causing Blo.... [29677] > > You might also want to modify the LTXHWM ONCONFIG from the default of 70% > which can be kind of high depending on the amount of logical logs you have > configured. > > For example, if you have 16GB of logical logs configured, do you really > want a > transaction to be able to span 11GB worth of logical logs before the engine > identifies it as a long transaction and rolls it back? > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Monday, March 04, 2013 11:15 AM > To: ids@iiug.org > Subject: Re: need quick way to find whom is causing Blo.... [29675] > > Notice in Andrew's post that the output shows both the current log position > and the beginning log position for the current transaction. You can have a > cron or Informix Task Manager task poll this data periodically and find > sessions with more than a given gap between the two and report them to you > via > text or email BEFORE they begin a formal LONGTX rollback so you can force a > manual rollback at an earlier stage of the process. This will take less > time > to rollback and will not block anything. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or > by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Mon, Mar 4, 2013 at 11:09 AM, John Adamski <adamski@graceland.edu> > wrote: > > > OS: HPUX 11.31 (11iv3) on BL870x IA server > > IDS: 11.70.FC4 > > > > Friday afternoon we had a Blocked:LONGTX that locked up the database > > for a while. I was informed while in a meeting that the "database is > > down" about > > 40 > > minutes after the Blocked:LONGTX started. > > > > It took me a way too much time to find the session that had the block > > and to kill that session; I was wonder what would have been the > > quickest way to find who had the block that was preventing the > > rollback. As I'm sure I chose a less than speedy way to do find it. > > > > John Adamski, Sr. Network Specialist > > Graceland University, 1 University Place, Lamoni, IA 50140 > > adamski@graceland.edu > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d042d051ca99e0d04d71c7e5a > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54b48d0e899ef04d72e0fc2
Thanks for the information,
I have LTXHWM set to 50 and LTXEHWM set to 60, sounds like I need to increase
the difference.
The cause seems to be a developer added a new field to a large table that
should have been defaulted to not null, when a cleanup process ran it found a
large number of nulls in the field and tried to change them to blank.
I think I need to get a script setup to help identify who is causing these
long transactions. Thanks everyone for your input.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Miller iii
Sent: Monday, March 04, 2013 12:19 PM
To: ids@iiug.org
Subject: Re: need quick way to find whom is causing Blo.... [29678]
Just a few points here.
Killing a session who is rolling back a long transaction will not help.
The servers undo daemon will assume the rollback for this session.
I believe what you want to look at is the difference between these two
parameters. The first parameter LTXHWM will automatically force the session to
start a rollback and NO other users will be effected by this one persons
rollback. LTXEHWM (the parameter with the "E") is the threshold which provides
EXCLUSIVE access to the logical logs and block all users attempting to write
data. If you encounter this blockage my suggestion would be to increase the
difference between this to values. By default hey come set at 70% and 80%. My
suggestion would be to drop LTXHWM to 50%, What this means is any transaction
which consume more than 50% of the logical logs will be forced to rollback and
if it consume up to 80% of the logical logs it will stop others from writing
to the logical logs and provide this transaction with exclusive resources.
# LTXHWM - The percentage of the logical logs that can be # filled before a
transaction is determined to be a # long transaction and is rolled back #
LTXEHWM - The percentage of the logical logs that have been # filled before
the server suspends all other # transactions so that the long transaction
being # rolled back has exclusive use of the logs
The other solution would be to utilize dynamic logs, which causes the logical
logs to grow upon hitting a long transaction.
My last point would be to let you know that onstat -x has been enhanced to
provide an estimation of how long it will take to rollback transactions. The
column in onstat -x is called "rb_time".
If you want to look at this information programmatically look at the table
called sysmaster:systxptab
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/04/2013 08:09:28 AM:
> From: "John Adamski" <adamski@graceland.edu>
> To: ids@iiug.org,
> Date: 03/04/2013 08:21 AM
> Subject: need quick way to find whom is causing Blocked.... [29670]
> Sent by: ids-bounces@iiug.org
>
> OS: HPUX 11.31 (11iv3) on BL870x IA server
> IDS: 11.70.FC4
>
> Friday afternoon we had a Blocked:LONGTX that locked up the database
> for
a
> while. I was informed while in a meeting that the "database is
> down"about
40
> minutes after the Blocked:LONGTX started.
>
> It took me a way too much time to find the session that had the block
> and
to
> kill that session; I was wonder what would have been the quickest way
> to
find
> who had the block that was preventing the rollback. As I'm sure I
> chose a less than speedy way to do find it.
>
> John Adamski, Sr. Network Specialist
> Graceland University, 1 University Place, Lamoni, IA 50140
> adamski@graceland.edu
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
My suggestion would be:
1) do you really want a transaction to do this? If you don´t have any related
foreign keys, for example, you could easily do this job without a transaction,
just output the sql command to a log, and watch for some errors
2) you could also reduce the transaction size, asking the developer to do it
by phases, separating the jobs through a primary key filter, avoiding LONGTX
(the best way I think)
3) as Art said, the most recommended situation would be an increase of your
logical-logs, to at least 4 days of full transaction loads. But this demands
disk space, so.... not always as easy as we would like.
Hope it helps.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Informix Senior DBA - Orizon Brasil
BRIUG website administrator
Informix independent consultant
> To: ids@iiug.org
> From: adamski@graceland.edu
> Subject: RE: need quick way to find whom is causing Blo.... [29697]
> Date: Tue, 5 Mar 2013 09:15:46 -0500
>
> Thanks for the information,
>
> I have LTXHWM set to 50 and LTXEHWM set to 60, sounds like I need to increase
> the difference.
>
> The cause seems to be a developer added a new field to a large table that
> should have been defaulted to not null, when a cleanup process ran it found a
> large number of nulls in the field and tried to change them to blank.
>
> I think I need to get a script setup to help identify who is causing these
> long transactions. Thanks everyone for your input.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
> Miller iii
> Sent: Monday, March 04, 2013 12:19 PM
> To: ids@iiug.org
> Subject: Re: need quick way to find whom is causing Blo.... [29678]
>
> Just a few points here.
>
> Killing a session who is rolling back a long transaction will not help.
> The servers undo daemon will assume the rollback for this session.
>
> I believe what you want to look at is the difference between these two
> parameters. The first parameter LTXHWM will automatically force the session
to
> start a rollback and NO other users will be effected by this one persons
> rollback. LTXEHWM (the parameter with the "E") is the threshold which
provides
> EXCLUSIVE access to the logical logs and block all users attempting to write
> data. If you encounter this blockage my suggestion would be to increase the
> difference between this to values. By default hey come set at 70% and 80%. My
> suggestion would be to drop LTXHWM to 50%, What this means is any transaction
> which consume more than 50% of the logical logs will be forced to rollback
and
> if it consume up to 80% of the logical logs it will stop others from writing
> to the logical logs and provide this transaction with exclusive resources.
>
> # LTXHWM - The percentage of the logical logs that can be # filled before a
> transaction is determined to be a # long transaction and is rolled back #
> LTXEHWM - The percentage of the logical logs that have been # filled before
> the server suspends all other # transactions so that the long transaction
> being # rolled back has exclusive use of the logs
>
> The other solution would be to utilize dynamic logs, which causes the logical
> logs to grow upon hitting a long transaction.
>
> My last point would be to let you know that onstat -x has been enhanced to
> provide an estimation of how long it will take to rollback transactions. The
> column in onstat -x is called "rb_time".
>
> If you want to look at this information programmatically look at the table
> called sysmaster:systxptab
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/04/2013 08:09:28 AM:
>
> > From: "John Adamski" <adamski@graceland.edu>
> > To: ids@iiug.org,
> > Date: 03/04/2013 08:21 AM
> > Subject: need quick way to find whom is causing Blocked.... [29670]
> > Sent by: ids-bounces@iiug.org
> >
> > OS: HPUX 11.31 (11iv3) on BL870x IA server
> > IDS: 11.70.FC4
> >
> > Friday afternoon we had a Blocked:LONGTX that locked up the database
> > for
> a
> > while. I was informed while in a meeting that the "database is
> > down"about
> 40
> > minutes after the Blocked:LONGTX started.
> >
> > It took me a way too much time to find the session that had the block
> > and
> to
> > kill that session; I was wonder what would have been the quickest way
> > to
> find
> > who had the block that was preventing the rollback. As I'm sure I
> > chose a less than speedy way to do find it.
> >
> > John Adamski, Sr. Network Specialist
> > Graceland University, 1 University Place, Lamoni, IA 50140
> > adamski@graceland.edu
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Art, I have it on my to-do list to increase the log space and number of logs, but my 20 other hats I wear here always seem to get more of my time than my DBA work. Since it's only been on the to-do list for 2 years I won't be until 2020 until I get to it. :-D I'll have to see if I can get this pushed a bit closer to the top. Thanks for the advice. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, March 05, 2013 8:13 AM To: ids@iiug.org Subject: Re: need quick way to find whom is causing Blo.... [29696] My rule-of-thumb for logical logs is that you should have enough logical log space to last for 4 typical days worth of transactions so that if the log backups fail over a long weekend you don't have to worry about it until Tuesday when you get back to the office. ;-) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 5, 2013 at 9:00 AM, John Adamski <adamski@graceland.edu> wrote: > We have LTXHWM set to 50 and our logical logs are small compared to > most Informix installs. So most of the time know one knows we > experienced a rollback. This time the rollback got stopped and > everything seems to have come to a screeching halt. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Andrew Ford > Sent: Monday, March 04, 2013 12:05 PM > To: ids@iiug.org > Subject: RE: need quick way to find whom is causing Blo.... [29677] > > You might also want to modify the LTXHWM ONCONFIG from the default of > 70% which can be kind of high depending on the amount of logical logs > you have configured. > > For example, if you have 16GB of logical logs configured, do you > really want a transaction to be able to span 11GB worth of logical > logs before the engine identifies it as a long transaction and rolls > it back? > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Monday, March 04, 2013 11:15 AM > To: ids@iiug.org > Subject: Re: need quick way to find whom is causing Blo.... [29675] > > Notice in Andrew's post that the output shows both the current log > position and the beginning log position for the current transaction. > You can have a cron or Informix Task Manager task poll this data > periodically and find sessions with more than a given gap between the > two and report them to you via text or email BEFORE they begin a > formal LONGTX rollback so you can force a manual rollback at an > earlier stage of the process. This will take less time to rollback and > will not block anything. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Advanced DataTools, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Mon, Mar 4, 2013 at 11:09 AM, John Adamski <adamski@graceland.edu> > wrote: > > > OS: HPUX 11.31 (11iv3) on BL870x IA server > > IDS: 11.70.FC4 > > > > Friday afternoon we had a Blocked:LONGTX that locked up the database > > for a while. I was informed while in a meeting that the "database is > > down" about > > 40 > > minutes after the Blocked:LONGTX started. > > > > It took me a way too much time to find the session that had the > > block and to kill that session; I was wonder what would have been > > the quickest way to find who had the block that was preventing the > > rollback. As I'm sure I chose a less than speedy way to do find it. > > > > John Adamski, Sr. Network Specialist Graceland University, 1 > > University Place, Lamoni, IA 50140 adamski@graceland.edu > > > > > > > > > > ********************************************************************** > ****** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d042d051ca99e0d04d71c7e5a > > > ********************************************************************** > ****** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54b48d0e899ef04d72e0fc2 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.