long transaction, onstat -l, flag A----
User on IDS 10 hit a long-transaction abort even after adding logical logs, and was puzzled that onstat -l showed ~216 of 400 logs still flagged A-- (newly added, never used). Replies explained the flags (U-- = used, awaiting backup; A-- = added but unused) and that long transactions are governed by LTXHWM/LTXEHWM, which were set to ~30%/40% — so a transaction aborts after consuming only ~30% of log space, making 216 unused logs expected. Advice: raise LTXHWM/LTXEHWM (e.g. 50/60, or 80/90 with DYNAMIC_LOGS=2 and spare dbspace). No confirmation of the outcome was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Recently, we added logical logs because we were performing alters/
updates to big tables. But even after that we got long transaction.
When I look at onstat -l it shows at least 216 logical logs having
flag U-- and A--. A-- I think means have never been used by informix.
So my question is:
1. Does it mean that informix has never used these logs
2. Why has informix not used these logs, why are they still marked as
A--. Shouldn't have informix used these logs before throwing long
transaction ?
mohitanchlia@gmail.com wrote:
> Recently, we added logical logs because we were performing alters/
> updates to big tables. But even after that we got long transaction.
> When I look at onstat -l it shows at least 216 logical logs having
> flag U-- and A--. A-- I think means have never been used by informix.
> So my question is:
>
> 1. Does it mean that informix has never used these logs
> 2. Why has informix not used these logs, why are they still marked as
> A--. Shouldn't have informix used these logs before throwing long
> transaction ?
>
IDS version ???
The U-- flag indicates that the logical log is used and will not be
rused before backed up (B--).
The A-- flag indicates a newly added log file that has not yet been used.
Long transactions are controlled by LTXHWN (and LTXEHWM) configuration
parameter. Depending on your IDS version you may either increase the
total size of logical log files or increase the value of LTXHWM/LTXEHWM
parameters (IDS 11).
On Jul 30, 12:51 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
> mohitanch...@gmail.com wrote:
> > Recently, we added logical logs because we were performing alters/
> > updates to big tables. But even after that we gotlongtransaction.
> > When I look at onstat -l it shows at least 216 logical logs having
> > flag U-- and A--. A-- I think means have never been used by informix.
> > So my question is:
>
> > 1. Does it mean that informix has never used these logs
> > 2. Why has informix not used these logs, why are they still marked as
> > A--. Shouldn't have informix used these logs before throwinglong
> >transaction?
>
> IDS version ???
>
> The U-- flag indicates that the logical log is used and will not be
> rused before backed up (B--).
> The A-- flag indicates a newly added log file that has not yet been used.
>
> Longtransactions are controlled by LTXHWN (and LTXEHWM) configuration
> parameter. Depending on your IDS version you may either increase the
> total size of logical log files or increase the value of LTXHWM/LTXEHWM
> parameters (IDS 11).
Version: IDS 10
My question is why hasn't informix used these logical logs, shouldn't
have informix used these logical logs before throwing long
transaction ? Our DYNAMIC_LOGS is set to 0, and our total LOGFILE
parameter 400 out of which 216 show as A--. All the configuration
parameter look correct, but I am not sure why informix hasn't used
these logs. Our LTXEHWM/LTXHWM paramaters are set to low 30s and 40%.
On Mon, 2007-07-30 at 07:38 -0700, mohitanchlia@gmail.com wrote: > My question is why hasn't informix used these logical logs, shouldn't > have informix used these logical logs before throwing long > transaction ? Our DYNAMIC_LOGS is set to 0, and our total LOGFILE > parameter 400 out of which 216 show as A--. All the configuration > parameter look correct, but I am not sure why informix hasn't used > these logs. Our LTXEHWM/LTXHWM paramaters are set to low 30s and 40%. Those parameters tell IDS what percentage of total log space to use for a single transaction before giving it up as a long transaction. 216 unused logs out of 400 total available is entirely consistent with a 40% HWM, especially if the system is otherwise idle and those logs have just been added. Is this related to your previous questions about altering a table with VARCHAR columns, or did you finally figure that one out? -- Carsten Haese http://informixdb.sourceforge.net
mohitanchlia@gmail.com wrote:
> On Jul 30, 12:51 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
>> mohitanch...@gmail.com wrote:
>>> Recently, we added logical logs because we were performing alters/
>>> updates to big tables. But even after that we gotlongtransaction.
>>> When I look at onstat -l it shows at least 216 logical logs having
>>> flag U-- and A--. A-- I think means have never been used by informix.
>>> So my question is:
>>> 1. Does it mean that informix has never used these logs
>>> 2. Why has informix not used these logs, why are they still marked as
>>> A--. Shouldn't have informix used these logs before throwinglong
>>> transaction?
>> IDS version ???
>>
>> The U-- flag indicates that the logical log is used and will not be
>> rused before backed up (B--).
>> The A-- flag indicates a newly added log file that has not yet been used.
>>
>> Longtransactions are controlled by LTXHWN (and LTXEHWM) configuration
>> parameter. Depending on your IDS version you may either increase the
>> total size of logical log files or increase the value of LTXHWM/LTXEHWM
>> parameters (IDS 11).
>
> Version: IDS 10
>
> My question is why hasn't informix used these logical logs, shouldn't
> have informix used these logical logs before throwing long
> transaction ? Our DYNAMIC_LOGS is set to 0, and our total LOGFILE
> parameter 400 out of which 216 show as A--. All the configuration
> parameter look correct, but I am not sure why informix hasn't used
> these logs. Our LTXEHWM/LTXHWM paramaters are set to low 30s and 40%.
>
>
With LTXHWM = 30 (pct of logfiles) will give you a long transaction when
a transaction spans 30 pct of the logfiles. When the transaction is
rolled back extra logfiles are used. With a total of 400 logfiles
(assuming all with same size) and LTXHWM = 30 216 not used looks very
much what I would expect.
Increase LTXHWM to 50 and LTXEHWM to 60 - that should be safe. If you
have unused dbspace you can set DYNAMIC_LOGS=2 and increase
LTXHWM/LTXEHWM up to 80/90
On Jul 30, 8:11 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
> mohitanch...@gmail.com wrote:
> > On Jul 30, 12:51 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
> >> mohitanch...@gmail.com wrote:
> >>> Recently, we added logical logs because we were performing alters/
> >>> updates to big tables. But even after that we gotlongtransaction.
> >>> When I look at onstat -l it shows at least 216 logical logs having
> >>> flag U-- and A--. A-- I think means have never been used by informix.
> >>> So my question is:
> >>> 1. Does it mean that informix has never used these logs
> >>> 2. Why has informix not used these logs, why are they still marked as
> >>> A--. Shouldn't have informix used these logs before throwinglong
> >>>transaction?
> >> IDS version ???
>
> >> The U-- flag indicates that the logical log is used and will not be
> >> rused before backed up (B--).
> >> The A-- flag indicates a newly added log file that has not yet been used.
>
> >> Longtransactions are controlled by LTXHWN (and LTXEHWM) configuration
> >> parameter. Depending on your IDS version you may either increase the
> >> total size of logical log files or increase the value of LTXHWM/LTXEHWM
> >> parameters (IDS 11).
>
> > Version: IDS 10
>
> > My question is why hasn't informix used these logical logs, shouldn't
> > have informix used these logical logs before throwinglong
> >transaction? Our DYNAMIC_LOGS is set to 0, and our total LOGFILE
> > parameter 400 out of which 216 show as A--. All the configuration
> > parameter look correct, but I am not sure why informix hasn't used
> > these logs. Our LTXEHWM/LTXHWM paramaters are set to low 30s and 40%.
>
> With LTXHWM = 30 (pct of logfiles) will give you alongtransactionwhen
> atransactionspans 30 pct of the logfiles. When thetransactionis
> rolled back extra logfiles are used. With a total of 400 logfiles
> (assuming all with same size) and LTXHWM = 30 216 not used looks very
> much what I would expect.
>
> Increase LTXHWM to 50 and LTXEHWM to 60 - that should be safe. If you
> have unused dbspace you can set DYNAMIC_LOGS=2 and increase
> LTXHWM/LTXEHWM up to 80/90- Hide quoted text -
>
> - Show quoted text -
Yes it's related to my earlier post of alter table in-place algorithm.
I read more about LTXHWM/LTXEHVM , high watermark parameter and
DYNAMIC_LOG, and got me to thinking if we can use this more
effectively and that's where I posted this question.