Can someone enlighten me on long transactions? This is something
completely new to me as I've only been with Informix for 2 years. Any
information or examples would prove helpful. Thanks.
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

If there are open transactions in a logical log then it cannot be
reused/overwritten. This means that if there are many long running open
transactions, or one or more very large transactions that span many
logical log files, there will come a point at which the engine can no
longer log transactions to the logical logs because the logs (even if
they have been backed up) cannot be reused due to open transaction
data. Since a long running transaction can cause many other
transactions to encounter the same problem, when the engine fills a
percentage of the logs equivalent to the LTXHWM ONCONFIG setting it will
block new transactions from starting in the hope the one or more
existing transactions will complete freeing up log space. If space does
no free up before the LTXEHWM is reached then the oldest transactions
(ie the one that begins earliest in the existing logical logs) is forced
to rollback in order to free up log space for other transactions.

Art

Art S. Kagel
Oninit (www.oninit.com <http://www.oninit.com>)
IIUG Board of Directors (art at (no spam) iiug.org <mailto:art at (no spam) iiug.org>)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitly or implicitly.
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, Oct 20, 2009 at 11:11 AM, Jeremiah Leong
yogensha4ever at (no spam) gmail.com <mailto:yogensha4ever at (no spam) gmail.com>> wrote:

Can someone enlighten me on long transactions? This is something
completely new to me as I've only been with Informix for 2 years. Any
information or examples would prove helpful. Thanks.
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org <mailto:Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Can someone enlighten me on long transactions? This is something
completely new to me as I've only been with Informix for 2 years. Any
information or examples would prove helpful. Thanks.

Jeremiah's post:
Can someone enlighten me on long transactions? This is something
completely new to me as I've only been with Informix for 2 years. Any
information or examples would prove helpful. Thanks.

Have you actually suffered from some? The default settings in the
config file are notoriously small.

From: Jeremiah Leong <yogensha4ever at (no spam) gmail.com
Subject: Need enlightenment regarding long transactions
To: informix-list at (no spam) iiug.org
Date: Tuesday, October 20, 2009, 10:11 AM
Can someone enlighten me on long
transactions? This is something
completely new to me as I've only been with Informix for 2
years. Any
information or examples would prove helpful. Thanks.
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Warning: This goes waaaay beyond the scope of Jeremiah's question. Much
more enlightenment than originally requested.

Andrew Clarke wrote:

 >> Jeremiah's post:
 >> Can someone enlighten me on long transactions? This is something
 >> completely new to me as I've only been with Informix for 2 years. Any
 >> information or examples would prove helpful. Thanks.
 
 > Have you actually suffered from some? The default settings in the
 > config file are notoriously small.
 

Andrew,
Interesting you should say that.  I am accustomed to finding the default
settings at LTXHWM=70, LTXEHWM=80.  If you have several instances of a
rogue application, this is woefully high and is still likely to allow
the logs to fill while rolling back one or more of them, before any of
them reach the LTXEHWM.

When I taught the classes (and for every server I have ever configured)
I preached setting these parameters at 40 and 45%.  Kinda low, it seems..

My reasoning:
No matter how many rogue transactions you have, the logs will never fill
up. (Assuming you *are* backing up logs, of course.)  For suppose TX1
hits the HWM and starts to roll back, then TX2 hits the HWM while TX1 is
already rolling back.  Then TX2 will start rolling back as well.
However, while writing rollback records for both transactions, TX1 is
likely to hit the EHWM first. (It makes no difference which hits the
EHWM first.) Then the rollback for TX2 will be suspended (along with all
other transaction activity) while the TX1 rollback completes.  No matter
how many rogue transaction TX3-n are running, all will be suspended
while that TX1 rollback proceeds.

In the above scenario of many rogues, had the EHWM been set higher than
50%, a situation could occur (quite unlikely - famous last words) where
many rogues can gang up and fill the logs with their fresh (not
rollback) records before the next checkpoint as the rollback of TX1 is
being processed.

Even this is not foolproof, as the following war story shows:

I mention the checkpoint in this context because [I have empirical
reason to believe] the span of each transaction is detected at each
checkpoint.  Here's my experience with that:

My user had requested a 30-minute checkpoint interval because he would
be running massive loads.  I had configured a 2GB physical log for him,
as well as *my* standard 40/45 for LTXHWM/LTXEHWM.

15 minutes into the load, the 6GB of log space was full.

Wo' hoppen to my mathematically rigorous scheme?

Well, you see, there was no checkpoint in all the time the load was
running. Because the physical log was so huge, it did not hit the 75%
threshold to trigger a checkpoint.  Hence, the backed-up logs did not
get freed up, since that kind of behavior is executed at checkpoint time.

After Advanced Support dialed in and reset the logical logs, I set the
checkpoint interval down to 5 minutes and added a comment to keep it
that way.

-- Jacob S.

Andrew,
Interesting you should say that. I am accustomed to finding the default
settings at LTXHWM=70, LTXEHWM=80. If you have several instances of a
rogue application, this is woefully high and is still likely to allow
the logs to fill while rolling back one or more of them, before any of
them reach the LTXEHWM.

That plus the silly small values for number of logs and size of logs. On the

My user had requested a 30-minute checkpoint interval because he would
be running massive loads. I had configured a 2GB physical log for him,
as well as *my* standard 40/45 for LTXHWM/LTXEHWM.

[SNIP]

After Advanced Support dialed in and reset the logical logs, I set the
checkpoint interval down to 5 minutes and added a comment to keep it
that way.

yeah, fussing over checkpoints is almost always a waste of time, and the idea

Andrew,

Interesting you should say that. I am accustomed to finding the default

settings at LTXHWM=70, LTXEHWM=80. If you have several instances of a

rogue application, this is woefully high and is still likely to allow

the logs to fill while rolling back one or more of them, before any of

them reach the LTXEHWM.



That plus the silly small values for number of logs and size of logs. On
the first OLTP application I worked with in production, the previous people
had left the defaults, and even normal daily work was hitting the long
transaction limits. After getting fed up with a few wedged engines requiring
tech support intervention, we learned to radically improve those numbers.

Although newer engines have features that help reduce the pain and also a
better default config, my standard setup was to have approx 3:1 ratio
between logical and physical logs, size each logical to hold maybe 1/2 an
hour of normal activity, and enough logicals to last for a few days. This
was because of the risk of the tape-changing staff member being off sick
etc. So the numbers we shockingly large, but I never lost a night's sleep
again!

My user had requested a 30-minute checkpoint interval because he would

be running massive loads. I had configured a 2GB physical log for him,

as well as *my* standard 40/45 for LTXHWM/LTXEHWM.



[SNIP]



After Advanced Support dialed in and reset the logical logs, I set the

checkpoint interval down to 5 minutes and added a comment to keep it

that way.



yeah, fussing over checkpoints is almost always a waste of time, and the
idea that large loads are helped by avoiding checkpoints is flat-out wrong I
think. Nothing better than a checkpoint write for flushing all the stuff
written so far.

In fact when I'm loading en-masse, and if I have exclusive use of the box
at the time, I'll give 50% the available memory to buffers, 40% for PDQ
memory (and switch on PDQ to the max), set the LRU write percentages
insanely high to avoid LRU writes, and even size the physical log to exploit
it's 75% rule to trigger a checkpoint just about the time the LRU queues hit
their writing waterlines. Indexing an update stat performance has to be seen
to be believed.

But that's even more off the original topic.



_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Too frequent checkpoints do indeed slow down bulk loads, or at least they
did before non-blocking checkpoints. I had a system I ran once, the powers
that be had the server configured to check point every 10 seconds. The
first time we ran the monthly billing application the server was check
pointing every 10 seconds for 7 seconds each time and the app dragged for
several hours. Since the older system completed in under two hours on much
slower hardware, we killed the job and tuned the engine. I changed the
checkpoint time to every 15 minutes and WHOOOSSHHH!! everything completed
in 30 minutes.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art at (no spam) iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitly or implicitly.
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 Wed, Oct 21, 2009 at 7:16 PM, Andrew Clarke <aclarke at (no spam) civica.com.au>wrote:
Andrew,

Interesting you should say that. I am accustomed to finding the default

settings at LTXHWM=70, LTXEHWM=80. If you have several instances of a

rogue application, this is woefully high and is still likely to allow

the logs to fill while rolling back one or more of them, before any of

them reach the LTXEHWM.

That plus the silly small values for number of logs and size of logs. On
the first OLTP application I worked with in production, the previous
people had left the defaults, and even normal daily work was hitting the
long transaction limits. After getting fed up with a few wedged engines
requiring tech support intervention, we learned to radically improve
those numbers.

Although newer engines have features that help reduce the pain and also a
better default config, my standard setup was to have approx 3:1 ratio
between logical and physical logs, size each logical to hold maybe 1/2 an
hour of normal activity, and enough logicals to last for a few days. This
was because of the risk of the tape-changing staff member being off sick
etc. So the numbers we shockingly large, but I never lost a night's sleep
again!

My user had requested a 30-minute checkpoint interval because he would

be running massive loads. I had configured a 2GB physical log for him,

as well as *my* standard 40/45 for LTXHWM/LTXEHWM.



[SNIP]



After Advanced Support dialed in and reset the logical logs, I set the

checkpoint interval down to 5 minutes and added a comment to keep it

that way.

yeah, fussing over checkpoints is almost always a waste of time, and the
idea that large loads are helped by avoiding checkpoints is flat-out
wrong I think. Nothing better than a checkpoint write for flushing all
the stuff written so far.

In fact when I'm loading en-masse, and if I have exclusive use of the box
at the time, I'll give 50% the available memory to buffers, 40% for PDQ
memory (and switch on PDQ to the max), set the LRU write percentages
insanely high to avoid LRU writes, and even size the physical log to
exploit it's 75% rule to trigger a checkpoint just about the time the LRU
queues hit their writing waterlines. Indexing an update stat performance
has to be seen to be believed.

But that's even more off the original topic.



_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Too frequent checkpoints do indeed slow down bulk loads, or at least they
did before non-blocking checkpoints.  I had a system I ran once, the powers
that be had the server configured to check point every 10 seconds.  The
first time we ran the monthly billing application the server was check
pointing every 10 seconds for 7 seconds each time and the app dragged for
several hours.  Since the older system completed in under two hours on much
slower hardware, we killed the job and tuned the engine.  I changed the
checkpoint time to every 15 minutes and WHOOOSSHHH!! everything completed
in 30 minutes.

That sounds like a process-intensive load as opposed to a typical dbimport
type of operation. Regardless, 10 seconds per is nuts unless someone can prove
otherwise.

I never found LRU writes helped my timings for imports. When I was sitting
there bored looking for ways to improve the task, I noticed the load speed
drop when it reached the LRU percents, and that's when I started thinking
about just using checkpoints. At first I sat there watching the LRU %s and
manually triggering a checkpoint, but even that went away when I added huge
allocations of memory and set the physical log size to let it trigger the
check point.

This was on boxes from a few years ago now, and probably a single or limited
number of disk spindles didn't help. The LRU writes just interfered with other
IO activity. Things might be different these days, but we have people to do
that now.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (a... at (no spam) iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitly or implicitly..
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 Wed, Oct 21, 2009 at 7:16 PM, Andrew Clarke <acla... at (no spam) civica.com.au>wrote:
Andrew,

Interesting you should say that. I am accustomed to finding the default

settings at LTXHWM=70, LTXEHWM=80. If you have several instances of a

rogue application, this is woefully high and is still likely to allow

the logs to fill while rolling back one or more of them, before any of

them reach the LTXEHWM.

That plus the silly small values for number of logs and size of logs. On
the first OLTP application I worked with in production, the previous
people had left the defaults, and even normal daily work was hitting the
long transaction limits. After getting fed up with a few wedged engines
requiring tech support intervention, we learned to radically improve
those numbers.

Although newer engines have features that help reduce the pain and also a
better default config, my standard setup was to have approx 3:1 ratio
between logical and physical logs, size each logical to hold maybe 1/2 an
hour of normal activity, and enough logicals to last for a few days. This
was because of the risk of the tape-changing staff member being off sick
etc. So the numbers we shockingly large, but I never lost a night's sleep
again!

My user had requested a 30-minute checkpoint interval because he would

be running massive loads. I had configured a 2GB physical log for him,

as well as *my* standard 40/45 for LTXHWM/LTXEHWM.

[SNIP]

After Advanced Support dialed in and reset the logical logs, I set the

checkpoint interval down to 5 minutes and added a comment to keep it

that way.

yeah, fussing over checkpoints is almost always a waste of time, and the
idea that large loads are helped by avoiding checkpoints is flat-out
wrong I think. Nothing better than a checkpoint write for flushing all
the stuff written so far.

In fact when I'm loading en-masse, and if I have exclusive use of the box
at the time, I'll give 50% the available memory to buffers, 40% for PDQ
memory (and switch on PDQ to the max), set the LRU write percentages
insanely high to avoid LRU writes, and even size the physical log to
exploit it's 75% rule to trigger a checkpoint just about the time the LRU
queues hit their writing waterlines. Indexing an update stat performance
has to be seen to be believed.

But that's even more off the original topic.

_______________________________________________
Informix-list mailing list
Informix-l... at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list