Re: Checkpoint take so long

Yuck! What are your CKPTINTVL, LRU_MAX_DIRTY and LRU_MIN_DIRTY settings in $ONCONFIG? This is tounge-in-cheek as this decision really needs to factor in your production environment, but try setting your cleaning limits really low. For example, set LRU_MAX_DIRTY to 2 and LRU_MIN_DIRTY to 1. Your onstat -p statistics may not look quite as good since chunk writes (which are very efficient as data is pre-sorted by chunk) will drop off and LRU writes will increase. Big deal. It will keep your page cleaners working for their pay, keep writes occuring between checkpoints, keep checkpoint durations low, keep those pesky users off your back, and keep your boss happy. Look in your Admin Guide and read up on shared memory for more info on the whys and hows. -Kevyn ;-{> On Thu, 25 Feb 1999 23:56:52 GMT, trieu@ti.L-3Com.com wrote: >Hi all, > Can some one please tell me how to reduce checkpoint time. >My checkpoint completed sometime go up to 80seconds. Please let know >how to turning this. Thanks > >onstat -m>14:59:00 Checkpoint Completed: duration was 29 seconds. >14:59:50 Logical Log 7927 Complete. >15:01:04 Logical Log 7928 Complete. >15:02:29 Logical Log 7929 Complete. >15:04:27 Checkpoint Completed: duration was 27 seconds. >15:08:16 Logical Log 7930 Complete. >15:08:54 Logical Log 7931 Complete. >15:09:38 Checkpoint Completed: duration was 10 seconds. >15:09:43 Logical Log 7932 Complete. >15:10:18 Logical Log 7933 Complete. >15:10:54 Logical Log 7934 Complete. >15:11:38 Logical Log 7935 Complete. >15:14:57 Checkpoint Completed: duration was 20 seconds. >15:19:59 Checkpoint Completed: duration was 1 seconds. >15:25:00 Checkpoint Completed: duration was 1 seconds. >15:30:03 Checkpoint Completed: duration was 3 seconds. >15:35:03 Checkpoint Completed: duration was 1 seconds. >15:40:04 Checkpoint Completed: duration was 0 seconds. >15:45:04 Checkpoint Completed: duration was 0 seconds. >15:50:04 Checkpoint Completed: duration was 0 seconds.>Wayne Trieu > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

What types of values should we be using for those parameters, and how frequently should we be checkpointing... I know you're going to tell me it's environment specific, but - I have an OLTP database behind a website. Lots of small inserts/updates. How big should the physical logs be and what are the tradeoffs involved as I increase it's size to spread out my checkpoints. Cheers Kevyn Schneider wrote: > > Yuck! What are your CKPTINTVL, LRU_MAX_DIRTY and LRU_MIN_DIRTY > settings in $ONCONFIG? This is tounge-in-cheek as this decision > really needs to factor in your production environment, but try setting > your cleaning limits really low. For example, set LRU_MAX_DIRTY to 2 > and LRU_MIN_DIRTY to 1. Your onstat -p statistics may not look quite > as good since chunk writes (which are very efficient as data is > pre-sorted by chunk) will drop off and LRU writes will increase. Big > deal. It will keep your page cleaners working for their pay, keep > writes occuring between checkpoints, keep checkpoint durations low, > keep those pesky users off your back, and keep your boss happy. > > Look in your Admin Guide and read up on shared memory for more info on > the whys and hows. > > -Kevyn ;-{> > > On Thu, 25 Feb 1999 23:56:52 GMT, trieu@ti.L-3Com.com wrote: > > >Hi all, > > Can some one please tell me how to reduce checkpoint time. > >My checkpoint completed sometime go up to 80seconds. Please let know > >how to turning this. Thanks > > > >onstat -m> >14:59:00 Checkpoint Completed: duration was 29 seconds. > >14:59:50 Logical Log 7927 Complete. > >15:01:04 Logical Log 7928 Complete. > >15:02:29 Logical Log 7929 Complete. > >15:04:27 Checkpoint Completed: duration was 27 seconds. > >15:08:16 Logical Log 7930 Complete. > >15:08:54 Logical Log 7931 Complete. > >15:09:38 Checkpoint Completed: duration was 10 seconds. > >15:09:43 Logical Log 7932 Complete. > >15:10:18 Logical Log 7933 Complete. > >15:10:54 Logical Log 7934 Complete. > >15:11:38 Logical Log 7935 Complete. > >15:14:57 Checkpoint Completed: duration was 20 seconds. > >15:19:59 Checkpoint Completed: duration was 1 seconds. > >15:25:00 Checkpoint Completed: duration was 1 seconds. > >15:30:03 Checkpoint Completed: duration was 3 seconds. > >15:35:03 Checkpoint Completed: duration was 1 seconds. > >15:40:04 Checkpoint Completed: duration was 0 seconds. > >15:45:04 Checkpoint Completed: duration was 0 seconds. > >15:50:04 Checkpoint Completed: duration was 0 seconds.> >Wayne Trieu > > > >-----------== Posted via Deja News, The Discussion Network ==---------- > >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

This really is disgusting behavour by your Engine - what is going on. Something is clearly doing very large updates on your system at the early stages (identify what!). Kevyn is right to question the settings that he does. Something also to consider is how many LRU queues do you have and how many shared memory buffers?? This might get contentious here, but the 'One LRU per CPU-VP(Min 4)' is not neccessarily the best strategy if you have a lot of shared memory on OLTP systems - your queues will end up too long. 2000 buffers per LRU (increase LRU's rather than decrease buffers) has proven quite successful at a few sites I deal with (credit goes to Informix & HP with this recommendation), all of these are high throughput OLTP sites rather than bulk loads though. Remember to increase Cleaners appropriately here as well. Also I can't emphasise enough ( if OLTP ) how much difference lowering LRU_MAX_DIRTY and LRU_MIN_DIRTY will make : either '2 and 1' or '4 and 1' or around this mark should make a difference. Jeff McFee In article <36d606e9.185738267@news.onramp.net>, kevyn_schneider@administaff.com (Kevyn Schneider) wrote: > Yuck! What are your CKPTINTVL, LRU_MAX_DIRTY and LRU_MIN_DIRTY > settings in $ONCONFIG? This is tounge-in-cheek as this decision > really needs to factor in your production environment, but try setting > your cleaning limits really low. For example, set LRU_MAX_DIRTY to 2 > and LRU_MIN_DIRTY to 1. Your onstat -p statistics may not look quite > as good since chunk writes (which are very efficient as data is > pre-sorted by chunk) will drop off and LRU writes will increase. Big > deal. It will keep your page cleaners working for their pay, keep > writes occuring between checkpoints, keep checkpoint durations low, > keep those pesky users off your back, and keep your boss happy. > > Look in your Admin Guide and read up on shared memory for more info on > the whys and hows. > > -Kevyn ;-{> > > On Thu, 25 Feb 1999 23:56:52 GMT, trieu@ti.L-3Com.com wrote: > > >Hi all, > > Can some one please tell me how to reduce checkpoint time. > >My checkpoint completed sometime go up to 80seconds. Please let know > >how to turning this. Thanks > > > >onstat -m> >14:59:00 Checkpoint Completed: duration was 29 seconds. > >14:59:50 Logical Log 7927 Complete. > >15:01:04 Logical Log 7928 Complete. > >15:02:29 Logical Log 7929 Complete. > >15:04:27 Checkpoint Completed: duration was 27 seconds. > >15:08:16 Logical Log 7930 Complete. > >15:08:54 Logical Log 7931 Complete. > >15:09:38 Checkpoint Completed: duration was 10 seconds. > >15:09:43 Logical Log 7932 Complete. > >15:10:18 Logical Log 7933 Complete. > >15:10:54 Logical Log 7934 Complete. > >15:11:38 Logical Log 7935 Complete. > >15:14:57 Checkpoint Completed: duration was 20 seconds. > >15:19:59 Checkpoint Completed: duration was 1 seconds. > >15:25:00 Checkpoint Completed: duration was 1 seconds. > >15:30:03 Checkpoint Completed: duration was 3 seconds. > >15:35:03 Checkpoint Completed: duration was 1 seconds. > >15:40:04 Checkpoint Completed: duration was 0 seconds. > >15:45:04 Checkpoint Completed: duration was 0 seconds. > >15:50:04 Checkpoint Completed: duration was 0 seconds.> >Wayne Trieu > > > >-----------== Posted via Deja News, The Discussion Network ==---------- > >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own