Re: checkpoint interval

This is a multi-part message in MIME format. --------------F55B77F7AA2B03C5B6B13061 Content-Type: text/plain; charset=us-ascii Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Transfer-Encoding: 7bit Sahrul Hidayat wrote: > Dear All, > > I'm running INF 7.23UC1 with total database is 76 GB. > We are happy with our configuration, but since Last Monday, our online > > perfomance > going down and also interval of checkpoin sometime took so long, about > > 50-170 second. We have checked no big transaction, no long transaction > that > caused checkpoint interval like this. all transaction is on normal > condition. > > Could somebody give some suggestion, what are the parameter relation > with > checkpoint interval, what is exacly the problem, if we face problem > like > this ? There are two triggers of checkpoints: The checkpoint interval configuration parameter CKPINTVL, which has a default value of 300 seconds, and when the physical log file becomes 75 percent full. So if the interval between the checkpoints (not the duration time) are less than CKPINTVL, you may consider to enlarge the physical log file. There are several things that influence the checkpoint duration time. You say that you have 76 GB. So if these 76 GB are found on 20 4GB disk and you have split up every disk in say 3 chunks with a total of 60 chunks. During a checkpoint data OnLine uses chunk writes. That is pages in the buffer pool are sorted per chunk and then written down on disk. Chunk writes are done in chunk order. So if chunk number 1, 2 and 3 are on disk 1, chunk number 4, 5 and 6 are on disk 2 etc you will have 3 processes or threads competing on disk 1, and probably none writing to disk 4, 5, 6 etc. Alas, the best chunk layout would be chunk 1 on disk 1, chunk 2 on disk 2 ... chunk 20 on disk 20, chunk 21 on disk 1 and so on If your chunk layout is not the optimal, you can try to change the number of cleaners. Hopefully you are using raw devices and KAIO? To check run onstat -g ath|grep kaio. If not try to increase the numbers af AIO vps. You can add more aio vps on the fly with onmode -p +aio As a rule of thumb I usally say that LRU writes should be 10 times as many as chunk writes. Use onstat -F to check. You should also run onstat -Rr to check how full the lru queues are when you reach a checkpoint. This may give you an indication of the value the MIN and MAX DIRTY should be set to. > > > here is our online param : > > LRUS = 8 > CLEANER=12 > LRU_MAX_DIRTY=3 > LRU_MIN_DIRTY=1> > reply and suggestion are greatly appreciated...... > > rgds > sahrul best regards claus samuelsen --------------F55B77F7AA2B03C5B6B13061 Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Claus Samuelsen Content-Disposition: attachment; filename="vcard.vcf" begin: vcard fn: Claus Samuelsen n: Samuelsen;Claus org: Sysdeco Danmark A/S adr: Gydevang 22 B;;;DK-3450 Aller'd;;;Denmark email;internet: csa@sysdeco.dk title: Systems Manager tel;work: +45 4814 3000 tel;fax: +45 4814 3011 x-mozilla-cpt: ;0 x-mozilla-html: FALSE end: vcard --------------F55B77F7AA2B03C5B6B13061--