Hi Water Marks

In Message-Id: <9407292254.AA18601@anubis.informix.com> you wrote: } >Date: Thu, 28 Jul 1994 15:16:11 -0500 } >From: cwakins@jabba.rmc.com (Clem W. Akins) } >Subject: FAQ } >X-Informix-List-Id: <list.4420> } > } >Regarding the FAQ, here are some criticisms and comments: } > } >In 6.1, Configuring OnLine: } >default Transaction high-water marks -- Should be less than 50%, to allow } > a roll-back. Have enough logs that whatever number you choose is } > enough for your largest long transaction. 70/80 is not nearly } > low enough. } } On what do you base this assertion? } } With a reasonable log configuration (say more than 4 logs), the 70/80 limit } is very unlikely to run you into trouble. Unless you are mass inserting or } deleting extremely small records (say 1-8 bytes, though I'm tempted to } suggest 1-4 bytes), it is very difficult to run into problems. I do not } recall seeing systems run into trouble with 80/90 figures which are the } default in tbconfig.std. I tend to leave them parameters at their default, } even on production machines, though I have also lowered it to 80/70 when } users request it. I suspect that there is some granularity built into the } testing of the log space left; if you only have 3 logs, then the logs are } only 67% full when you get to the last log, so maybe you could run into } problems with LTXHWM > 67. Ensuring you have enough log space in enough } logs (8-16 for a production system) means that one log represents } 6.25-12.5%, and gives ample safety margin with 70/80. } } Reducing these parameters to less than 50% is not something I'd recommend; } there is definitely an upper-limit on how much log space you can require to } do a rollback, because the CLM records (which are the cause of the trouble) } are of finite size (16 bytes, if memory serves me right), and there is at } most on CLM for each operation performed by the user, and each operation } has a 12 byte header plus some data, such as the whole record which is } inserted or deleted, or both the before and after image for an updated } record. Unless these records are tiny, the amount of space used to } generate the LTX dwarfs the amount of space needed to rollback. } } I suppose that if you have multiple users who regularly and simultaneously } start operations which become LTX transactions, then you may have problems. } But that is at least partly a question of education, and at least partly a } question of operational controls on the OnLine system. } } I consider that you have to be trying quite hard to run into trouble with } LTXHWM = 70, LTXEHWM = 80. I also admit that the problems which occur if } this is wrong are so severe that erring on the side of caution is } advisable. } } Yours, } Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> } It's been a while since I started this, and I just now got around to looking at it. Also, my net connection wouldnt let me out! I found a back door out which is not right but works for now. Sorry for the delay, but it's not really time sensitive... This is what I base these conclusions on: First, some background. We used to have long transaction situations fairly often. We would purge very large amounts of data in single SQL statements. Once we had tech support call in and apply tbzero, other times we just lost all since the last archive. We often watched the little dots roll by during "fast recovery" for hours. One late night, someone had the brilliant idea of building a purge program which would delete rows! Then we thought about how to ensure _no long transactions_ and wrote it to commit after every row. This worked so well that we wrote most of our insert programs to do the same thing. At the same time we got v5 of OnLine and read about LTXHWM. Tech support had told us this would solve our problems. It seemed to us that since a transaction took up "x" space to record, it would take up "x" space again to rollback. This means that you must have no more than half your logs filled with one transaction if you want to roll it back. *Less* than half if there are other things going on. The assumption here is that it takes as much log space to roll back as it does to record the change in the first place. I don't know what a CLM record is, or how the actual rollback is logged. I gather from your explanation that it takes much less space to roll back than to transact. This is the crux of the problem. Just how much log space does it take to roll back a transaction? Re-reading the manual (OnLine Admin Guide, v5.0) does not really say either way. It says "the transaction roll back itself generates logical log records, however, and as other processes continue writing to the logical log, the log continues to fill." The Managing Large Databases book (part no. 502-5-168-1-999999-1) v 07-93 tends to support our original assumption. On page 2-41 it says "... LTXHWM ...should be set much lower than the default; something like 50%. The parameter LTXEHWM...should also be set lower than the default; something like 60% or 70%." I've seen this sentiment echoed on the net from time to time, too.