IDS Forum

Re: Long Transaction aborted

Posted By: Jonathan Leffler
Date: Tuesday, 15 March 2011, at 9:55 a.m.

In Response To: Long Transaction aborted (WASEEM NOORI)

There was also a message from Waseem's colleague asking:

> We are using IDS 11.50.FC6 on Sun OS Sparc.
>
> We were executing alter table command (we were creating index using online
> command), but received Long Transactions Aborted message.
> There were more than 4 GB's logs were still available (that were un-used),
> but
> still we got this error.
>
> Is there any particular way out for this ?
>

On Tue, Mar 15, 2011 at 04:27, WASEEM NOORI <wnoori@i2cinc.com> wrote:

> Following is environment details.
>
> Informix version = IBM Informix Dynamic Server Version 11.50.FC6
> Table name = daily_records
> Total Columns = 210
> No of rows = 64 Millions
> Size of Logical Logs = 10 GB
> Transaction/minutes = 50
> Existing no of indexes = 24
> Reference/referenced tables = 0
>
> OnConfig Settings related to long transaction:
>
> LTXHWM 50
> LTXEHWM 60
>
> we were creating online index on two columns i-e date
> (datetime),status(char(1)). After 14 hours we got error long transaction
> aborted error. Here are my two questions
>
> 1. How to calculate INDEX size in informix? (if possible share any formula)
> 2. What steps should we do to avoid long transaction issue? Increase
> logical
> logs or modify some parameters of onconfig file?
> 3. Any other things we can look?
>

One part of the question says 'ALTER TABLE' but another mentions 'creating
online index'. There's a big difference between simply creating an index on
a table and altering a table wholesale. The latter requires all the indexes
to be built - in general. So, you need to be more precise about what you
were really doing.

You don't indicate the row size. This is a key factor in the size
calculations for ALTER TABLE. You also don't discuss the size of the
columns being indexed; that is a crucial factor in determining the size of
an index.

You have 24 indexes on the table already; that seems like an astronomical
number. If you are doing ALTER TABLE, then IDS might have to rebuild all 24
existing indexes -- it depends on the statement you were actually
executing. (Adding a constraint is one thing - and doesn't require a
rebuild.)

When calculating index size, you need to know the size of the key, the
amount of repetition in the key (is it almost unique, or are there 10
records per key value, on average, or 100, or 1000). There's a couple of
big fudge factors in the calculation, but number of rows * (key size +
overhead) * fudge-factor is the main component. The fudge-factor is larger
than 1; try 2 as a starting point. The overhead is about 12 bytes, I think
(slot = 4 bytes; plus the placement information for the data row). If you
are rebuilding all indexes at once, then you need this much space per index.

If you are simply adding one index, and the key size is, say, 20 bytes, then
you need 64M * (20+12) * 2 = 4096 M log space. That's a very crude
calculation; I'm not sure that I'd trust the fudge-factor all that much (it
could be an underestimate). It suggests that maybe you should not have run
out of space - but there are major guesses (key size, notably) in there.

If you were rebuilding the entire table, then you need vastly more log space
to build each of 24 indexes.

Since you have 10 GB log space and LTXEHWM at 60%, you should have gotten
the LTX when there was about 4 GB spare. That part of the system worked
perfectly.

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--0023545bdbbc0652a3049e85aff1

Messages In This Thread

• Long Transaction aborted
  WASEEM NOORI -- Tuesday, 15 March 2011, at 7:27 a.m.
  • Re: Long Transaction aborted
    Jonathan Leffler -- Tuesday, 15 March 2011, at 9:55 a.m.
  • Re: Long Transaction aborted
    Art Kagel -- Tuesday, 15 March 2011, at 11:30 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.