Re: long checkpoints

Thanks for all the help. But we have lowered our LRU_MAX_DIRTY/MIN_DIRTY from 30/25 to 4/2 and still receive checkpoints in the 25-50 second range - way too long. We notice that checkpoints still seem to occur at the 5 minute CKPTINTVL interval (rather than the % MIN/MAX), which is surprising to me. We are thinking now about concentrating on whether we have too many buffers or if it would help to lower our CKPTINTVL setting to something like 2 minutes. Any ideas? Bill Dare <dareb@jevic.com> wrote in message news:<bbqi1u$ai9$1@terabinaries.xmission.com>... > From the shell run the following when the long checkpoint is occurring: > > jevc2:prod:[/v04/informix]> while [ TRUE ]; do > > echo 'select username,sid,pid,is_incrit from syssessions where is_incrit = > 1' | dbaccess sysmaster > > sleep 1 > > done > > You will get the session id, process id and user name for the offending > user. > > Regards, > Bill Dare > > > > > -----Original Message----- > > From: schmotom@yahoo.com [SMTP:schmotom@yahoo.com] > > Sent: Friday, June 06, 2003 10:20 AM > > To: informix-list@iiug.org > > Subject: Ree: long checkpoints > > > > thanks for the reply..i am fairly new to informix....do you have some > > techniques to do this? > > > > > > > > "Murray Wood" <murray@quanta.co.nz> wrote in message > > news:<bbogst$qpr$1@terabinaries.xmission.com>... > > > This is often someone running an sql that spends a long time in critical > - > > > the checkpoint must wait until complete. This can be administrative or > > > could be in the application. > > > > > > You need to locate either what is happening at the time the checkpoint > > > strikes, or the disk activity at the time of the checkpoint. > > > > > > MW > > > > > > > -----Original Message----- > > > > From: owner-informix-list@iiug.org > > > > [mailto:owner-informix-list@iiug.org]On Behalf Of tomL > > > > Sent: Friday, 6 June 2003 3:53 a.m. > > > > To: informix-list@iiug.org > > > > Subject: long checkpoints > > > > > > > > > > > > hello all, > > > > we have recently experienced checkpoints that are lasting 20-30 > > > > seconds that were previously 5-8 seconds. > > > > I am looking at the CONFIG file to see if something could be improved. > > > > We are on HP-UNIX box (11.00)(2 proc box) with Informix 9.21 HC-5. > > > > > > > > BUFFERS 250000 # Maximum number of shared buffers > > > > NUMAIOVPS 2 # Number of IO vps > > > > PHYSBUFF 32 # Physical log buffer size (Kbytes) > > > > LOGBUFF 32 # Logical log buffer size (Kbytes)> > > > LOGSMAX 40 # Maximum number of logical log files > > > > CLEANERS 50 # Number of buffer cleaner processes > > > > LRUS 50 # Number of LRU queues > > > > LRU_MAX_DIRTY 30 # LRU percent dirty begin cleaning> > > > limit > > > > LRU_MIN_DIRTY 25 # LRU percent dirty end cleaning limit > > > > CKPTINTVL 300 # Check point interval (in sec) > > > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> > > > multi-processor > > > > NUMCPUVPS 2 # Number of user (cpu) vps > > > > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> > > > to one > > > > LTXHWM 50 # Long transaction high water mark> > > > percentage > > > > LTXEHWM 60 # Long transaction high water mark > > > > (exclusive)> > > > > > > > KAIO is enabled. > > > > > > > > thanks for any help......

tomL wrote: > Thanks for all the help. But we have lowered our > LRU_MAX_DIRTY/MIN_DIRTY from 30/25 to 4/2 and still receive > checkpoints in the 25-50 second range - way too long. We notice that > checkpoints still seem to occur at the 5 minute CKPTINTVL interval > (rather than the % MIN/MAX), which is surprising to me. We are > thinking now about concentrating on whether we have too many buffers > or if it would help to lower our CKPTINTVL setting to something like 2 > minutes. Any ideas? > > It is better to tune the time between checkpoints by tinkering with the physical log size. The LRU_MIN_DIRTY/LRU_MAX_DIRTY parameters cause LRU writes which occur in the background. If you look at onstat -F then you wlil see FG writes (there were no clean buffers when a clean buffer was needed), LRU writes (caused my min/max dirty), and chunk writes (occurs at checkpoint time). LRU writes should be higher with the lower min/max - unless something else is going on. A very good discussion about checkpoint tuning can be found at: http://www.smooth1.demon.co.uk/ifaq06.htm#6.23