Re: Long transaction

Andy Kent (akent@cix.compulink.co.uk) wrote: : > On an Informix Online Administrator course Informix said that Support : > can dial-in and add another logical log - obviating a restore : > Hope we never need to do this. : > We have the Low and High Watermark levels set at 85% and 90%. : > I wonder if we should set hem much lower (like 1% - we have 6 logs : > files)? : > -- : > Tony McCartan : > Tony Mc (UK) : 1% wouldn't make much sense. Anything but the tiniest transaction would : keep getting blown away. If it's usually a single process that's filling : the logs, how about 45% and 48%. Even this is overkill. The only way you could need it as low as 50% is if all you were logging was EXTREMELY short log records. You need to log one CLR (Compensation Log Record) for each record logged during the transaction. CLR's are (I think) 12 bytes. Almost every other log record is longer, and most are much longer: INSERTs, for example, contain the row inserted, in addition to a 12 or 16 byte header. So if you're just inserting rows of length 1, and the transaction started at the very beginning of the log (because the whole log counts against you toward LTXHWM, but you don't have to roll back anything that isn't the long transaction) and this was the only user on the system, THEN, you might need close to 50% to roll back. But then you wouldn't need LTXEHWM as low as 48%. If you are INCREDIBLY paranoid (I am sometimes), set them to something like 50% and 65%. But just as important, adjust your total log space so that now 50% of your logs is adequate to COMPLETE your transactions. No point in having everything roll back safely if nothing completes. Tbzero has been beaten to death, but I'll proceed to beat it a little more: EVEN when it's Informix staff who dial up and run tbzero on your system, there is NO GUARANTEE that everything is squeaky clean afterwards. Tbzero cuts off your transactions at the most recent checkpoint, neither completing them, nor rolling them back. So if your transaction was to insert 100 rows, and only 30 of them had inserted by the checkpoint, only 30 are in the table, EVEN IF the transaction was committed before the system failure. Even better, suppose your table had 3 indexes on it, and the checkpoint occurred when the row had been inserted but the indexes hadn't been updated. Now you have 3 corrupt indexes. (I'm on a roll now.) What if the inserts had required another extent to be added to your table? That's two log records: CHALLOC and PTEXTEND. What if the checkpoint came in between? (I'm not sure this can happen, but this is just one example.) Now you have a corrupt chunk free list. I could go on and on... Our Informix staff, wonderful as I think they are, do not check every single possibility out for you before they tbzero you. (If they did, you'd probably find it faster to restore an archive.) That's why they SHOULD warn you before they do it, and why you should always tbcheck EVERYTHING afterwards. And why they don't let you do it to yourself. One more thing, if you SHOULD happen to find a copy, and it doesn't have password protection (this should NEVER happen, but if it does...), and, despite everything that everyone has said here, you decide you're going to use it, you'd better be sure that: - it was compiled against the same OS & version of OnLine, and - you can find another job, because if something goes wrong, Informix won't help you. Sorry to harp on this at such great length, but people have lost their jobs over this (customers, I mean). It's not pretty. June ---- June Tong Informix Asia/Pacific ---- ---- On-Loan Engineer Singapore ---- ---- Location-du-jour: Auckland, NZ (home of the America's Cup) ---- ---- junet@informix.com (65) 298-1716 ----