Re: Monitoring Checkpoint duration

Mike Segel (mikey@segel.com) wrote: : J. Keck wrote: : > : > I see the checkpoint durations in the log. What I would like to know : > is what duration(s) are ok and which are not?? I'm currently seeing 2 : > seconds. Don't know if it is good, bad or indifferent. How would you : > tune this?? : > : > Joe : You can tune the duration by controlling the number of logs, : the size of the logs, and the high watermark. No, this is about logical logs! : As to the length of 2 seconds, it depends on what the system does. : We have a system which has checkpoints lasting 10 seconds or so. : (Not the best scenario, but I didn't spec the system.) : Even with the long checkpoints, there does not seem to be : any problem with performance so far. If we can get more disk, and : memory, we would restructure the database and limit the length of : duration for the checkpoint. : -Mikey Checkpoint duration depends on several factors, the main are number of pages written during checkpoint, disk location strategies, number of page cleaners and for Dynamic Server number of AIO virtual processors. It depends also on other system and disk activities on the UNIX system side, but it is another problem. You can control number of pages written by many parameters: Ratio between LRU writes (and Idle Writes for Online 5.0x) and Chunk Writes: configuration parameters LRU_MAX_DIRTY LRU_MIN_DIRTYLRUS (only a little influence) Number of buffers: configuration parameter BUFFERS Frequency of checkpoint: CKPTINTVL configuration parameter and PHYSICAL LOG size CLEANERS - number of page cleaners - depends on number of disks and chunks location. For RAID or disks devided by Logical Volume Manager you can start different number of page cleaners as general rules says. For version 7.1x also number of AIO VP's (NUMAIOVPS) or UNIX system parameters for KIO performed by kernel and several other parameters. Checkpoint duration up to 2 seconds is enough good for OLTP, in batch processing you can have longer checkpoint times. Keep in mind that everything you change will have influence on other database system parts like read and write cache ratio and so on. For OnLine version 7.1x you can read checkpoint duration in message log, as mentioned in previous posts, for OnLine 5.0x I wrote simple shell script long time ago. Here it is: ----------------------- INFORMIXDIR=/usr/informix PATH=$INFORMIXDIR/bin:$PATH export PATH INFORMIXDIRTUNE=/tmp/tune old_t_ckpt=`tail -1 $TUNE/checkpoints_times|sed 's/.* //'` tbstat -m | grep Checkpoint | tail -1 | while read t_ckpt ckpt do if [ "$t_ckpt" != "$old_t_ckpt" ] then old_t_ckpt=t_ckpt tbcheck -pr | grep "Time of checkpoint" |\\ while read Time of checkpoint date t_ckpt_rp do echo "$t_ckpt_rp $t_ckpt" >> $TUNE/checkpoints_times done fi done ----------------------- I use it, when I tune OnLine 5.0x. I usually start it in my crontab with period closed to average period of checkpoints. At the first time you will get error message - $TUNE/checkpoints_times not found, but it works. The script reads message log and for last checkpoint it reads reserved pages with information on PAGE_CKPT . It is not the best, but it gives results. During OnLine archive or several other administrator utilities tbcheck -pr command could get into wait loop (waiting for a latch). Another possibility is to use tbstat -r 1 and grep CKPT in status line. Regards, Mirek -- ================================= Miroslav Lorenc Database Administrator Software Applications & Systems Internet: Miroslav.Lorenc@sasprg.cz Prague Czech Republic