Re: Need enlightenment regarding long transactions
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@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@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@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >