Re: Long Checkpoints

Ulrich Eckhardt wrote: > > "Mark D. Stock" wrote: > > > > Unfortunately the original message from Ulrich Eckhardt has not come > > through to me yet, so I hope you are reading Ulrich. :-) > > > [..] > > > > > > Say you get your checkpoints down to 1 second. If the physical log > > > fills every 30 seconds, in a 5 minute interval, you would be locking the > > > database for 10 seconds total (10 checkpoints at 1 second each). If you > > > can get the physical log to fill at 5 minutes, the you only are going to > > > be locking the database for 1 second in a 5 minute interval. > > > > > > In that part, I guess I was looking at overall checkpoint waits, not > > > just one specific checkpoint. > > > > I don't subscribe to the idea of matching your physical log size to your > > average work load. What happens when your work load is a bit high? You > > have performance problems. > > > > I always create my physical log too big so that an increase in work > > shouldn't increase the frequency of checkpoints. So I waste a bit of > > disk, but disk is cheap compared to performance. But then I do have to > > tune for users who love to mix OLTP access with batch jobs. Then they > > wonder why there is a performance problem. x-) > > > > The key here is how frequent are your checkpoints, and what is disk > > activity like. It is probably that you have a disk contention or > > throughput problem. Have you tuned the number of page cleaners in line > > with the number of LRU queues and disks? Are your chunks created across > > disks and not down each disk in turn? Do you have high LRU contention? > > > > Cheers, > > -- > > Mark. > > > > +----------------------------------------------------------+-----------+ > > | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| > > | http://www.informix.com http://www.informixhandbook.com |///// / //| > > | http://www.iiug.org +-----------------------------------+//// / ///| > > | |What year 2000 bug? year 2000 bug? |/// / ////| > > | |year 2000 bug? year 2000 bug? year |// / /////| > > | |2000 bug? year 2000 bug? year 1900 |/ ////////| > > +----------------------+-----------------------------------+-----------+ > > Hi, > > i don't think that the disks are the problem. It's all on a single > raid 5 array (one physical disk but with fast harddrives). I presume you mean one logical disk. :-) The RAID 5 configuration could well be your problem, unless you have lots of cache. > Sar gives the following output during a period of high checkpoint > intervalls : > gretel:/var/log/sa # sar -b -s 08:41:00 -e 08:55:00 -f sa02 > Linux 2.2.14 (gretel) 03/02/00 > > 08:41:38 tps rtps wtps bread/s bwrtn/s > 08:43:38 6.65 1.43 5.22 7.81 14.55 > 08:45:38 34.86 19.33 15.53 152.61 98.66 > 08:47:38 75.85 26.95 48.89 215.41 377.38 > 08:49:38 92.80 54.91 37.88 425.28 279.51 > 08:51:38 55.11 23.50 31.60 161.11 218.66 > 08:53:38 11.92 2.98 8.94 21.31 28.18 > 08:55:38 13.34 3.68 9.65 24.56 32.31 > Average: 46.20 21.52 24.68 163.92 169.49> > Which should be OK for our harddrives. Well it doesn't look very balanced to me, or is that typical RAID 5 activity? I don't use RAID 5 with databases myself. > So i will play a bit with the LRU cleaners and the checkpoint > intervall (currently set to 10 Minutes). > > Does the number of LRU queues affects the performance on a > single processor system ? Sure. You have to get the balance between number of queues, length of queues, number of page cleaners and number of disks & controllers. Generally, reducing your LRU_MIN & MAX parameters will reduce your checkpoint times. You can take these parameters down to 0 & 1 respectively if you have to. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+