Re: Need enlightenment regarding long transactions

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