Re: Too-frequent checkpoints; which parameters to check?

David Williams wrote: > > In article <331FF103.7F5C@lava.net>, Bob Cunningham <bob@lava.net> > writes > >I've got a situation with On-Line 7.22 (SPARC, Solaris 2.5.1) where > >we get flurries of checkpoints at short intervals, during > >some update periods that look like: > > > >23:35:07 Checkpoint Completed: duration was 2 seconds. > >23:35:14 Checkpoint Completed: duration was 1 seconds. > >23:35:22 Checkpoint Completed: duration was 1 seconds. > >23:35:30 Checkpoint Completed: duration was 1 seconds. > >23:35:38 Checkpoint Completed: duration was 1 seconds. > >23:35:46 Checkpoint Completed: duration was 2 seconds. > >23:35:53 Checkpoint Completed: duration was 1 seconds. > >... > >23:45:06 Checkpoint Completed: duration was 1 seconds. > >23:45:14 Checkpoint Completed: duration was 1 seconds.> > > >Needless to say, queries slow to a crawl when there's > >a checkpoint every few seconds. Who is working at midnight ? I think Informix slows down because the users are tired :) > >Log files aren't a problem, but I'm not sure exactly what is... > > > >Which tuning parameters should I be looking to change: > >PHYSFILE, the LRU* ones, or ??? > > Informix Online Dynamic Server Administrator's Guide, Volume 1 > Version 7.1 Page 12-52/3 : events that initiate a checkpoint. > > - The checkpoint interval, specified by the configuration parameter > CKPTINTVL, has elapsed and one or more modifications have occured > since the last checkpoint. > > ***CHECK CKPTNTVL*** > > - The phyiscal log on disk becomes 75 percent full. > > ***CHECK PHYSFILE*** And I thought, Informix prompts a warning when the physical log has a size smaller than 30% of the total logical log size. > - Online detects that the next logical log file to become current > contains the most recent checkpoint record. > > ***USE tbstat -l TO MONITOR LOGICAL LOG USAGE*** ***USE onstat -l ... > - The OnLine Administrator initiates a checkpoint from the OnMonitor, > Force-Ckpt menu of from the command line using onmode -c. > > ***CHECK CRON/AT JOBS FOR onmode COMMANDS*** > > - Certain Administrative tasks such as adding a chunk or a dbspace, > take place. > > ***UNLIKELY - MONITOR NUMBER OF CHUNKS WITH onstat -c *** Look at the number of Checkpoints! How many chunks is he going to add ? > -- > David Williams It looks as if someone is doing a mass media movement, like an insert with thousands of rows, an update or a delete. A good idea is creating an index in the late evening or clustering an index. Like David told you, look at the cron jobs. But in your header we can read "some update periods". If you know what you are doing and if you want to tune your system for that situation: Hm, it's hard to tune an OnLine instance for both, OLTP and batch operation. Okay, you can increase the Physical Log and this will result in less checkpoints, but the checkpoint duration will increase, unless you set your LRU_MAX_DIRTY to the correct value. BUFFERS * LRU_MAX_DIRTY * avg(disk speed per page) = checkpoint duration Hope, this will help. You are using version 7.1. If you migrate to version 7.2 then you might set the environment variable LIGHT_SCANS to 1 for the updating process. But I'm not quite sure if this setting avoids caching for an update process. Any comments ? Bye Stefan