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. > 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 (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

On 22 Oct, 01:00, Andrew Clarke <acla...@civica.com.au> wrote: > > 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...@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...@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...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list The other effect is that at checkpoint time ONE page cleaner cleans each chunk - spread the load across chunks to activate more page cleaners and get greater parallelism for the writes. David.