RE: Logical logs
A DBA hitting occasional long-transaction aborts (80 logs of 2048KB, LTXHWM/LTXEHWM 50/60) asked whether simply adding more logical logs (to 120-160) has drawbacks. Consensus: more logs is fine and harmless, though fewer/larger logs (e.g. 30 x 8MB) is easier to administer; but also find the cause - break big purges/updates into smaller committed chunks via a cursor, and avoid locks held during user input. One poster's suggestion to raise the watermarks to 80/90 was strongly challenged: that risks running out of log space mid-rollback and hanging the instance (the reason Informix lowered the defaults to 50/60). Recommended approach: increase log space and fix the application code.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I do not feel their is anything wrong with having that many logical logs.
One thing you might want to establish is why you are having long transactions
and whether or not what is being done is required to be one transaction.
If the cause is a process which is required to be updated in one transaction
you have no choice but to increase the number of logs.
If the cause of the long transactions is a purge or something of that nature,
which can be done in smaller increments without effecting the validity of the
data, you might want to look into making the purge use a select cursor
and deleting each record and committing periodically.(If their is a better way
to do this I would love to hear it.) If you just increase the number of logs,
as
the amount of data purged grows, the likelihood of hitting a long transaction
again grows. A program which commits at regular intervals bypasses
these emergencies.
If the cause of the long transactions is a lock held for an inordinate period
of time, it might be worth implementing some sort of timing mechanism
to cause the user to abort after holding a lock for some period of time,
or rewriting the application to not hold locks while waiting for user input.
Hope this helps,
Will
>===== Original Message From "Vinod Bhansali" <iiug@hotmail.com> =====
>In our system, I am noticing Long Transactions being caused sometimes. We
>have 80 log files, each of size 2048. LTXHWM & LTXEHWM are 50 & 60. I don't
>want to increase them. I am planning to increase the no. of log files to 120
>or to 160. Are there any pros and cons of having more no. of logical log
>files. We have continuous logical log backup running and we take zero level
>db backup daily using ontape.
>
>Thanks in advance
>Vinod Bhansali
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit us at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------
HI Vinodh,
Nothing wrong with more logical logs except that is is inefficient from
an administrative point of view.
Instead of having 120 logical logs of 2MB each you can have 30 logs of
8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90
respectively.We have overcome the long transaction problem here by
deviating from Informix suggested values of 50 and 60.
Also , as William suggested, you need to have a look at the code as
well , as no amount of tuning can help if the code is inefficient.
In article <8ge25v$s64$1@news.xmission.com>,
William Rice <ricew@operamail.com> wrote:
>
> I do not feel their is anything wrong with having that many logical
logs.
> One thing you might want to establish is why you are having long
transactions
> and whether or not what is being done is required to be one
transaction.
>
> If the cause is a process which is required to be updated in one
transaction
> you have no choice but to increase the number of logs.
>
> If the cause of the long transactions is a purge or something of that
nature,
> which can be done in smaller increments without effecting the
validity of the
> data, you might want to look into making the purge use a select cursor
> and deleting each record and committing periodically.(If their is a
better way
> to do this I would love to hear it.) If you just increase the number
of logs,
> as
> the amount of data purged grows, the likelihood of hitting a long
transaction
> again grows. A program which commits at regular intervals bypasses
> these emergencies.
>
> If the cause of the long transactions is a lock held for an
inordinate period
> of time, it might be worth implementing some sort of timing mechanism
> to cause the user to abort after holding a lock for some period of
time,
> or rewriting the application to not hold locks while waiting for user
input.
>
> Hope this helps,
> Will
> >===== Original Message From "Vinod Bhansali" <iiug@hotmail.com> =====
> >In our system, I am noticing Long Transactions being caused
sometimes. We
> >have 80 log files, each of size 2048. LTXHWM & LTXEHWM are 50 & 60.
I don't
> >want to increase them. I am planning to increase the no. of log
files to 120
> >or to 160. Are there any pros and cons of having more no. of logical
log
> >files. We have continuous logical log backup running and we take
zero level
> >db backup daily using ontape.
> >
> >Thanks in advance
> >Vinod Bhansali
>
>_______________________________________________________________________
_
> >Get Your Private, Free E-mail from MSN Hotmail at
http://www.hotmail.com
>
> ------------------------------------------------------------
> This e-mail has been sent to you courtesy of OperaMail, as a free
service from
> Opera Software, makers of the award-winning Web Browser, Opera.
Visit us at
> http://www.opera.com/ or our portal at: http://www.myopera.com/ Your
free e-mail
> account is waiting at: http://www.operamail.com/
> ------------------------------------------------------------
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Our SAP / Informix PRD system has 200 logical logs @ 10 MG each and we still
encounter long transactions from time to time. Our onconfig has 50 and 70
for the watermarks. We tend to investigate programs which cause long
transactions to see if it was performing sequential reads, etc. We have
400+ users and have a rather large database (400+ GB).
Take care.
Clifton Bean
"Girish Punjabi" <girishpunjabi@my-deja.com> wrote in message
news:8gf5hh$8sd$1@nnrp1.deja.com...
> HI Vinodh,
>
> Nothing wrong with more logical logs except that is is inefficient from
> an administrative point of view.
> Instead of having 120 logical logs of 2MB each you can have 30 logs of
> 8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90
> respectively.We have overcome the long transaction problem here by
> deviating from Informix suggested values of 50 and 60.
> Also , as William suggested, you need to have a look at the code as
> well , as no amount of tuning can help if the code is inefficient.
>
>
> In article <8ge25v$s64$1@news.xmission.com>,
> William Rice <ricew@operamail.com> wrote:
> >
> > I do not feel their is anything wrong with having that many logical
> logs.
> > One thing you might want to establish is why you are having long
> transactions
> > and whether or not what is being done is required to be one
> transaction.
> >
> > If the cause is a process which is required to be updated in one
> transaction
> > you have no choice but to increase the number of logs.
> >
> > If the cause of the long transactions is a purge or something of that
> nature,
> > which can be done in smaller increments without effecting the
> validity of the
> > data, you might want to look into making the purge use a select cursor
> > and deleting each record and committing periodically.(If their is a
> better way
> > to do this I would love to hear it.) If you just increase the number
> of logs,
> > as
> > the amount of data purged grows, the likelihood of hitting a long
> transaction
> > again grows. A program which commits at regular intervals bypasses
> > these emergencies.
> >
> > If the cause of the long transactions is a lock held for an
> inordinate period
> > of time, it might be worth implementing some sort of timing mechanism
> > to cause the user to abort after holding a lock for some period of
> time,
> > or rewriting the application to not hold locks while waiting for user
> input.
> >
> > Hope this helps,
> > Will
> > >===== Original Message From "Vinod Bhansali" <iiug@hotmail.com> =====
> > >In our system, I am noticing Long Transactions being caused
> sometimes. We
> > >have 80 log files, each of size 2048. LTXHWM & LTXEHWM are 50 & 60.
> I don't
> > >want to increase them. I am planning to increase the no. of log
> files to 120
> > >or to 160. Are there any pros and cons of having more no. of logical
> log
> > >files. We have continuous logical log backup running and we take
> zero level
> > >db backup daily using ontape.
> > >
> > >Thanks in advance
> > >Vinod Bhansali
> >
> >_______________________________________________________________________
> _
> > >Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
> >
> > ------------------------------------------------------------
> > This e-mail has been sent to you courtesy of OperaMail, as a free
> service from
> > Opera Software, makers of the award-winning Web Browser, Opera.
> Visit us at
> > http://www.opera.com/ or our portal at: http://www.myopera.com/ Your
> free e-mail
> > account is waiting at: http://www.operamail.com/
> > ------------------------------------------------------------
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Girish Punjabi wrote: > HI Vinodh, > > Nothing wrong with more logical logs except that is is inefficient from > an administrative point of view. > Instead of having 120 logical logs of 2MB each you can have 30 logs of > 8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90 > respectively.We have overcome the long transaction problem here by > deviating from Informix suggested values of 50 and 60. This is dangerous! You could run out of log space when rolling back a long transaction. It is far preferable to increase log space to avoid long transactions. Rudy
BAD IDEA to modify LTXHWM and LTXEHWM unless you also have LBU_PRESERVE and
a VERY large total logical log size. Otherwise instead of more frequent
long transaction rollbacks you may find your engine hung by an unusually
long transaction rollback that fills the remaining logical logs EVEN after
pausing all other transactions. Informix originally used 80 & 90 as the
defaults but they were getting so many tech calls to help recover from
hung instances with not enough log space left to even archive the logs that
they changed the default and recommended values to 50 & 60.
Art S. Kagel
Girish Punjabi wrote:
>
> HI Vinodh,
>
> Nothing wrong with more logical logs except that is is inefficient from
> an administrative point of view.
> Instead of having 120 logical logs of 2MB each you can have 30 logs of
> 8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90
> respectively.We have overcome the long transaction problem here by
> deviating from Informix suggested values of 50 and 60.
> Also , as William suggested, you need to have a look at the code as
> well , as no amount of tuning can help if the code is inefficient.
>
> In article <8ge25v$s64$1@news.xmission.com>,
> William Rice <ricew@operamail.com> wrote:
> >
> > I do not feel their is anything wrong with having that many logical
> logs.
> > One thing you might want to establish is why you are having long
> transactions
> > and whether or not what is being done is required to be one
> transaction.
> >
> > If the cause is a process which is required to be updated in one
> transaction
> > you have no choice but to increase the number of logs.
> >
> > If the cause of the long transactions is a purge or something of that
> nature,
> > which can be done in smaller increments without effecting the
> validity of the
> > data, you might want to look into making the purge use a select cursor
> > and deleting each record and committing periodically.(If their is a
> better way
> > to do this I would love to hear it.) If you just increase the number
> of logs,
> > as
> > the amount of data purged grows, the likelihood of hitting a long
> transaction
> > again grows. A program which commits at regular intervals bypasses
> > these emergencies.
> >
> > If the cause of the long transactions is a lock held for an
> inordinate period
> > of time, it might be worth implementing some sort of timing mechanism
> > to cause the user to abort after holding a lock for some period of
> time,
> > or rewriting the application to not hold locks while waiting for user
> input.
> >
> > Hope this helps,
> > Will
> > >===== Original Message From "Vinod Bhansali" <iiug@hotmail.com> =====
> > >In our system, I am noticing Long Transactions being caused
> sometimes. We
> > >have 80 log files, each of size 2048. LTXHWM & LTXEHWM are 50 & 60.
> I don't
> > >want to increase them. I am planning to increase the no. of log
> files to 120
> > >or to 160. Are there any pros and cons of having more no. of logical
> log
> > >files. We have continuous logical log backup running and we take
> zero level
> > >db backup daily using ontape.
> > >
> > >Thanks in advance
> > >Vinod Bhansali
> >
> >_______________________________________________________________________
> _
> > >Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
> >
> > ------------------------------------------------------------
> > This e-mail has been sent to you courtesy of OperaMail, as a free
> service from
> > Opera Software, makers of the award-winning Web Browser, Opera.
> Visit us at
> > http://www.opera.com/ or our portal at: http://www.myopera.com/ Your
> free e-mail
> > account is waiting at: http://www.operamail.com/
> > ------------------------------------------------------------
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Rudy, In this example we are talking about a transaction taking 24 MB ( for 90 %) and a part of 24MB ( for 80 %) for rollback. This is a lot of space and only if the application really needs that , should we go for an increase in logical log space. Else we need to look at the code. Obviously, the cost/benefit ratio tilts heavily in favour of increase in logical log space. However, as a DBA, I would still want the code to be streamlined if it is the culprit. This is one factor we should always consider before we move a new piece of code to Production. Cheers , Girish In article <392BCD54.E5254DF@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > Girish Punjabi wrote: > > > HI Vinodh, > > > > Nothing wrong with more logical logs except that is is inefficient from > > an administrative point of view. > > Instead of having 120 logical logs of 2MB each you can have 30 logs of > > 8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90 > > respectively.We have overcome the long transaction problem here by > > deviating from Informix suggested values of 50 and 60. > > This is dangerous! You could run out of log space when rolling back a long > transaction. It is far preferable to increase log space to avoid long > transactions. > > Rudy > > -- Cheers, Girish Sent via Deja.com http://www.deja.com/ Before you buy.
Do both. Increasing logical logs is painless (though sometimes expensive). You can often, though not always, break a job into separate transactions and thereby reduce the logical log requirements of the process. Art S. Kagel Girish Punjabi wrote: > > Rudy, > > In this example we are talking about a transaction taking 24 MB ( for > 90 %) and a part of 24MB ( for 80 %) for rollback. This is a lot of > space and only if the application really needs that , should we go for > an increase in logical log space. Else we need to look at the code. > Obviously, the cost/benefit ratio tilts heavily in favour of increase > in logical log space. However, as a DBA, I would still want the code to > be streamlined if it is the culprit. > This is one factor we should always consider before we move a new piece > of code to Production. > > Cheers , Girish > > In article <392BCD54.E5254DF@americasm01.nt.com>, > Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > > Girish Punjabi wrote: > > > > > HI Vinodh, > > > > > > Nothing wrong with more logical logs except that is is inefficient > from > > > an administrative point of view. > > > Instead of having 120 logical logs of 2MB each you can have 30 logs > of > > > 8MB each.You can also set LTXHWM & LTXEHWM are 80 and 90 > > > respectively.We have overcome the long transaction problem here by > > > deviating from Informix suggested values of 50 and 60. > > > > This is dangerous! You could run out of log space when rolling back a > long > > transaction. It is far preferable to increase log space to avoid long > > transactions. > > > > Rudy > > > > > > -- > Cheers, Girish > > Sent via Deja.com http://www.deja.com/ > Before you buy.