Question on Checkpoint Tuning How To for IBM Infor

Hello Informix gurus, I got 2 questions relating to checkpoint. 1. I've heard long checkpoint is acceptable in a data warehouse environment, but as far as I'm concerned, it's not so ok, it's been affecting the performance. In this environment, we have load jobs going on, then report jobs at the same time -- with this reason, in my own interpretation, I believe checkpoint durations should be tuned down. 2. How to tune checkpoint? I know in versions, 9 or older, I look at LRU_MIN_DIRTY and LRU_MAX_DIRTY, CLEANERS, BUFFER, then PHYSFILE. I can fine tune the checkpoint with onstat -R and onstat -F What do we do differently with newer versions of Informix where related onconfig parameters are little bit different now? Thanks so much in advance. Kern -- 12/14/12 00:01:37 Checkpoint Completed: duration was 43 seconds. 12/14/12 00:01:47 Checkpoint Completed: duration was 9 seconds. 12/14/12 00:04:22 Checkpoint Completed: duration was 17 seconds. 12/14/12 00:11:14 Checkpoint Completed: duration was 108 seconds. 12/14/12 00:14:45 Checkpoint Completed: duration was 45 seconds. 12/14/12 00:20:20 Checkpoint Completed: duration was 310 seconds. 12/14/12 00:21:52 Checkpoint Completed: duration was 91 seconds. 12/14/12 00:22:59 Checkpoint Completed: duration was 66 seconds. 12/14/12 00:24:29 Checkpoint Completed: duration was 89 seconds. 12/14/12 00:26:43 Checkpoint Completed: duration was 133 seconds. 12/14/12 00:28:14 Checkpoint Completed: duration was 90 seconds. 12/14/12 00:31:07 Checkpoint Completed: duration was 8 seconds. 12/14/12 00:32:00 Checkpoint Completed: duration was 14 seconds. 12/14/12 00:32:31 Checkpoint Completed: duration was 8 seconds. 12/14/12 00:33:26 Checkpoint Completed: duration was 16 seconds. 12/14/12 00:34:52 Checkpoint Completed: duration was 45 seconds. 12/14/12 00:35:05 Checkpoint Completed: duration was 12 seconds.

The thing to look at in 11.xx, because we now have non-blocking checkpoints, is the checkpoint wait times. Run onstat -g ckp when you are running these load jobs and reporting and see what the max and average wait times are and how many sessions had to wait. Also look at the frequency and cause of the checkpoints. If the wait times are significant, your checkpoints are too frequent, or are being tripped by logical log buffer or physical log overflow issues, then there are tuning items you can address. The things you need to tune are mostly the same as in 9.xx except that the LRUS and LRU_MIN/MAX_DIRTY parameters were replaced by attributes of the BUFFERPOOL parameter settings and so are settable independently for each buffer pool. Onstat -R is still the way to monitor lru flush performance between checkpoints, but now it reports for each buffer pool's lrus list separately. PHYSFILE is still there but you cannot manually modify it, you have to use onparams to resize or relocate the physical log - which can now be done at runtime, you don't have to shutdown the instance. Note that to resize the physical log you need space for the new log in its target dbspace and for the existing old log where it resides until the new log becomes operational. If you are resizing it into the same dbspace and there isn't room for both lots concurrently, you may have to resize it twice. Once to a tiny log in another dbspace then again to the new larger size in the desired dbspace. You also now have to contend with the AUTO* parameters some of which will affect checkpoint frequency and duration. Note that in general, warehouse instances should have very long CKPTINTVL values set since during normal operations checkpoints are not important and during loads you want as few checkpoints as possible and will likely force one at the end of the load manually anyway. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Dec 14, 2012 at 1:25 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Hello Informix gurus, > I got 2 questions relating to checkpoint. > 1. I've heard long checkpoint is acceptable in a data warehouse > environment, > but as far as I'm concerned, it's not so ok, it's been affecting the > performance. In this environment, we have load jobs going on, then report > jobs > at the same time -- with this reason, in my own interpretation, I believe > checkpoint durations should be tuned down. > > 2. How to tune checkpoint? > I know in versions, 9 or older, I look at LRU_MIN_DIRTY and LRU_MAX_DIRTY, > CLEANERS, BUFFER, then PHYSFILE. I can fine tune the checkpoint with > onstat -R> and onstat -F > What do we do differently with newer versions of Informix where related > onconfig parameters are little bit different now? > > Thanks so much in advance. > Kern -- > > 12/14/12 00:01:37 Checkpoint Completed: duration was 43 seconds. > 12/14/12 00:01:47 Checkpoint Completed: duration was 9 seconds. > 12/14/12 00:04:22 Checkpoint Completed: duration was 17 seconds. > 12/14/12 00:11:14 Checkpoint Completed: duration was 108 seconds. > 12/14/12 00:14:45 Checkpoint Completed: duration was 45 seconds. > 12/14/12 00:20:20 Checkpoint Completed: duration was 310 seconds. > 12/14/12 00:21:52 Checkpoint Completed: duration was 91 seconds. > 12/14/12 00:22:59 Checkpoint Completed: duration was 66 seconds. > 12/14/12 00:24:29 Checkpoint Completed: duration was 89 seconds. > 12/14/12 00:26:43 Checkpoint Completed: duration was 133 seconds. > 12/14/12 00:28:14 Checkpoint Completed: duration was 90 seconds. > 12/14/12 00:31:07 Checkpoint Completed: duration was 8 seconds. > 12/14/12 00:32:00 Checkpoint Completed: duration was 14 seconds. > 12/14/12 00:32:31 Checkpoint Completed: duration was 8 seconds. > 12/14/12 00:33:26 Checkpoint Completed: duration was 16 seconds. > 12/14/12 00:34:52 Checkpoint Completed: duration was 45 seconds. > 12/14/12 00:35:05 Checkpoint Completed: duration was 12 seconds. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340ef1336ff304d0d48a94

Thank you very much for the advice Art!! ________________________________ From: Art Kagel <art.kagel@gmail.com> To: ids@iiug.org Sent: Friday, December 14, 2012 12:53 PM Subject: Re: Question on Checkpoint Tuning How To for I.... [29095] The thing to look at in 11.xx, because we now have non-blocking checkpoints, is the checkpoint wait times. Run onstat -g ckp when you are running these load jobs and reporting and see what the max and average wait times are and how many sessions had to wait. Also look at the frequency and cause of the checkpoints. If the wait times are significant, your checkpoints are too frequent, or are being tripped by logical log buffer or physical log overflow issues, then there are tuning items you can address. The things you need to tune are mostly the same as in 9.xx except that the LRUS and LRU_MIN/MAX_DIRTY parameters were replaced by attributes of the BUFFERPOOL parameter settings and so are settable independently for each buffer pool. Onstat -R is still the way to monitor lru flush performance between checkpoints, but now it reports for each buffer pool's lrus list separately. PHYSFILE is still there but you cannot manually modify it, you have to use onparams to resize or relocate the physical log - which can now be done at runtime, you don't have to shutdown the instance. Note that to resize the physical log you need space for the new log in its target dbspace and for the existing old log where it resides until the new log becomes operational. If you are resizing it into the same dbspace and there isn't room for both lots concurrently, you may have to resize it twice. Once to a tiny log in another dbspace then again to the new larger size in the desired dbspace. You also now have to contend with the AUTO* parameters some of which will affect checkpoint frequency and duration. Note that in general, warehouse instances should have very long CKPTINTVL values set since during normal operations checkpoints are not important and during loads you want as few checkpoints as possible and will likely force one at the end of the load manually anyway. Art Art S. Kagel Advanced DataTools (http://www.advancedatatools.com/) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Dec 14, 2012 at 1:25 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Hello Informix gurus, > I got 2 questions relating to checkpoint. > 1. I've heard long checkpoint is acceptable in a data warehouse > environment, > but as far as I'm concerned, it's not so ok, it's been affecting the > performance. In this environment, we have load jobs going on, then report > jobs > at the same time -- with this reason, in my own interpretation, I believe > checkpoint durations should be tuned down. > > 2. How to tune checkpoint? > I know in versions, 9 or older, I look at LRU_MIN_DIRTY and LRU_MAX_DIRTY, > CLEANERS, BUFFER, then PHYSFILE. I can fine tune the checkpoint with > onstat -R> and onstat -F > What do we do differently with newer versions of Informix where related > onconfig parameters are little bit different now? > > Thanks so much in advance. > Kern -- > > 12/14/12 00:01:37 Checkpoint Completed: duration was 43 seconds. > 12/14/12 00:01:47 Checkpoint Completed: duration was 9 seconds. > 12/14/12 00:04:22 Checkpoint Completed: duration was 17 seconds. > 12/14/12 00:11:14 Checkpoint Completed: duration was 108 seconds. > 12/14/12 00:14:45 Checkpoint Completed: duration was 45 seconds. > 12/14/12 00:20:20 Checkpoint Completed: duration was 310 seconds. > 12/14/12 00:21:52 Checkpoint Completed: duration was 91 seconds. > 12/14/12 00:22:59 Checkpoint Completed: duration was 66 seconds. > 12/14/12 00:24:29 Checkpoint Completed: duration was 89 seconds. > 12/14/12 00:26:43 Checkpoint Completed: duration was 133 seconds. > 12/14/12 00:28:14 Checkpoint Completed: duration was 90 seconds. > 12/14/12 00:31:07 Checkpoint Completed: duration was 8 seconds. > 12/14/12 00:32:00 Checkpoint Completed: duration was 14 seconds. > 12/14/12 00:32:31 Checkpoint Completed: duration was 8 seconds. > 12/14/12 00:33:26 Checkpoint Completed: duration was 16 seconds. > 12/14/12 00:34:52 Checkpoint Completed: duration was 45 seconds. > 12/14/12 00:35:05 Checkpoint Completed: duration was 12 seconds. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340ef1336ff304d0d48a94 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.