RE: Physical Log size

Yuri, see below. > -----Original Message----- > From: Yuri Dovgart [SMTP:y_dovgart@tci.ukrtel.net] > Sent: Friday, October 08, 1999 6:19 AM > To: informix-list@iiug.org > Subject: Re: Physical Log size > > Link, David A. <DALink@West.com> wrote in message > news:7tjlri$ra$1@news.xmission.com... > > > > I have an OLTP database which I need to keep the checkpoints below 5 > > seconds, however, my users need to load millions of records > periodically. > > The database needs to be available 7x24. I have LRUMIN=0 and LRUMAX=1. > My > > checkpoints still jump up during a load. My current physical log is 80 > meg > > in size. Are there any caveats to making this 500 meg, so I can avoid > > reaching the 75% thresh hold? > > First of all, you must determine what causes your checkpoints - CKPTINTVL > time or your phys. log becomes 75 % full. Respectively, increase your > checkpoint interval or size of your physical log. With big phys. log you > can > perform checkpoint right after big load - when checkpoint duration not so > critical. Of course, do not forget to perform checkpoint _BEFORE_ big > load -this is critical for performance. [Link, David A.] My checkpoints fire as a result of the physical log filling up to 75%. > > > > Addition information is LRUS=24,CLEANERS=24. > > Depending on number of your CPUs and cleaners monitoring you can try to > increase your CLEANERS value. Also, LRUS = 24 for what number of buffers ? > Probably you must increase your LRUS. [Link, David A.] BUFFERS=32000 my %cached bufreads is 97.59% > > > > Is there anything else which I should try? > > > > My data is striped accross 8 drives and mirrored. When the dirty buffers > are > > flushed how is this handled? I have 1 dbspace and I am wondering if that > is > > making the flushing single threaded. > > No. During checkpoint Informix performs 'sorted' writes - dirty pages are > sorted for every chunk and then written to disk in parallel. That's why > you > can try to set your CLEANERS equal to number of chunks in system if number > of chunks is higher then 24 for your system. > [Link, David A.] I currently have 22 chunks. > > > > Is there anything else I should be looking at? > > Is your box NT or Unix ? Raw devices ? KAIO on or off ? [Link, David A.] IBM AIX, Raw, KAIO on. > > > > iostat shows no hot disks. > > What about onstat -D, onstat -g iof, monitoring cleaners state during > checkpoint ? [Link, David A.] How do you monitor the cleaners state during a checkpoint? > ---------------------------------------------------- > With best regards, Yuri Dovgart > SAP R/3, Informix technical consultant, > Senior System Consultant > System Architecture and High Availability Systems, > 'Telecominvest' company > Email y_dovgart@tci.ukrtel.net > ICQ 39284285 >

----- Original Message ----- From: Link, David A. <DALink@West.com> To: 'Yuri Dovgart' <y_dovgart@tci.ukrtel.net>; <informix-list@iiug.org> Sent: Friday, October 08, 1999 9:15 PM Subject: RE: Physical Log size > Yuri, > see below. > > > -----Original Message----- > > From: Yuri Dovgart [SMTP:y_dovgart@tci.ukrtel.net] > > Sent: Friday, October 08, 1999 6:19 AM > > To: informix-list@iiug.org > > Subject: Re: Physical Log size > > > > Link, David A. <DALink@West.com> wrote in message > > news:7tjlri$ra$1@news.xmission.com... > > > > > > I have an OLTP database which I need to keep the checkpoints below 5 > > > seconds, however, my users need to load millions of records > > periodically. > > > The database needs to be available 7x24. I have LRUMIN=0 and LRUMAX=1. > > My > > > checkpoints still jump up during a load. My current physical log is 80 > > meg > > > in size. Are there any caveats to making this 500 meg, so I can avoid > > > reaching the 75% thresh hold? > > > > First of all, you must determine what causes your checkpoints - CKPTINTVL> > time or your phys. log becomes 75 % full. Respectively, increase your > > checkpoint interval or size of your physical log. With big phys. log you > > can > > perform checkpoint right after big load - when checkpoint duration not so > > critical. Of course, do not forget to perform checkpoint _BEFORE_ big > > load -this is critical for performance. > [Link, David A.] > My checkpoints fire as a result of the physical log filling up to > 75%. So, increase your phys.log size to desired value (start with 300 Megs) and try to process as I told you above - checkpoint before and right after big load. > > > > > > > Addition information is LRUS=24,CLEANERS=24. > > > > Depending on number of your CPUs and cleaners monitoring you can try to > > increase your CLEANERS value. Also, LRUS = 24 for what number of buffers ? > > Probably you must increase your LRUS. > [Link, David A.] > BUFFERS=32000 Looks like it's OK with LRUS. But look at your 'bufwaits' value in the 'onstat -p' output - this must be not too high (remember that read aheads has impact on this value). > my %cached bufreads is 97.59% It's OK for OLTP. > > > > > > > > Is there anything else which I should try? > > > > > > My data is striped accross 8 drives and mirrored. When the dirty buffers > > are > > > flushed how is this handled? I have 1 dbspace and I am wondering if that > > is > > > making the flushing single threaded. > > > > No. During checkpoint Informix performs 'sorted' writes - dirty pages are > > sorted for every chunk and then written to disk in parallel. That's why > > you > > can try to set your CLEANERS equal to number of chunks in system if number > > of chunks is higher then 24 for your system. > > > [Link, David A.] > I currently have 22 chunks. Look at your cleaners during checkpoints - are they all busy ? > > > > > > Is there anything else I should be looking at? > > > > Is your box NT or Unix ? Raw devices ? KAIO on or off ? > [Link, David A.] > IBM AIX, Raw, KAIO on. How much CPUs and configured CPU VPs ? KAIO threads depends on the CPU VPs. Control your kaio threads load with 'onstat -g ioq'. > > > > > > > iostat shows no hot disks. > > > > What about onstat -D, onstat -g iof, monitoring cleaners state during > > checkpoint ? > [Link, David A.] > How do you monitor the cleaners state during a checkpoint? 'onstat -F' - see there info for each cleaner and it's state. ---------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285