Checkpoint Duration

I have an IDS, 7.31.FD3 as a database server for Java GUI client application. There were many times that the database checkpoint duration was very long while concurrent users were not as many as usual (there were some working days that the users work load was low). As checked in the message log, there was no error report or anything. I tried to check if there was any abnormal session working on the database but the session activities found seemed to be the user normal processes. The only thing I found out was there were INSERT transactions processing while the onstat output header said that transactions was blocked by checkpoint request. Also, the client applications got slow responds too. At first I thought lots of INSERT transactions processing at the same time caused this to happened but the strange thing is there were many times that it does not happen when there were many concurrent users inserting large amount of data but when there were less concurrent users inserting smaller amount of data the problem occurs! I also checked with my engineer if there were failed disks and the answer I got was all disks are working just find too. Could you possibly give me an advice on how to figure out what causes the problem, please? onconfig <partial> ============== # Shared Memory Parameters LOCKS 1000000 # Maximum number of locks BUFFERS 320000 # Maximum number of shared buffers NUMAIOVPS 9 # Number of IO vps PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes)LOGSMAX 300 # Maximum number of logical log files CLEANERS 96 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 5120000 # initial virtual shared memory segment size SHMADD 16384 # Size of new shared memory segments SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 400 # Check point interval (in sec) LRUS 256 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 40 # Long transaction high water mark LTXEHWM 60 # Long transaction high water mark TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes)

Hi, try to identify the exact insert statements. Is always the same or only few tables affected ? Check these tables with oncheck -pt if there are huge indices on them (attached indices: much more used pages than data pages). In that case you may want do recreate the indices. Regards Andreas ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von PANJIE KITT Gesendet: Mittwoch, 8. April 2009 03:54 An: ids@iiug.org Betreff: Checkpoint Duration [15471] I have an IDS, 7.31.FD3 as a database server for Java GUI client application. There were many times that the database checkpoint duration was very long while concurrent users were not as many as usual (there were some working days that the users work load was low). As checked in the message log, there was no error report or anything. I tried to check if there was any abnormal session working on the database but the session activities found seemed to be the user normal processes. The only thing I found out was there were INSERT transactions processing while the onstat output header said that transactions was blocked by checkpoint request. Also, the client applications got slow responds too. At first I thought lots of INSERT transactions processing at the same time caused this to happened but the strange thing is there were many times that it does not happen when there were many concurrent users inserting large amount of data but when there were less concurrent users inserting smaller amount of data the problem occurs! I also checked with my engineer if there were failed disks and the answer I got was all disks are working just find too. Could you possibly give me an advice on how to figure out what causes the problem, please? onconfig <partial> ============== # Shared Memory Parameters LOCKS 1000000 # Maximum number of locks BUFFERS 320000 # Maximum number of shared buffers NUMAIOVPS 9 # Number of IO vps PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes)LOGSMAX 300 # Maximum number of logical log files CLEANERS 96 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address SHMVIRTSIZE 5120000 # initial virtual shared memory segment size SHMADD 16384 # Size of new shared memory segments SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 400 # Check point interval (in sec) LRUS 256 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit LTXHWM 40 # Long transaction high water mark LTXEHWM 60 # Long transaction high water mark TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You do not show the ONCONFIG setting for the CLEANERS parameter. If LRUS is set to 256, CLEANERS should be set to 256 also (but at least the greater of 128 or the number of chunks). With too few page CLEANERS the write operations during a checkpoint will take longer and the critical section blocks discussed below remain in place until all dirty buffers have been flushed to disk. Checkpoints will wait for any user session that is in a critical section. There are critical sections at various points within INSERT, UPDATE, and DELETE operations mostly so large numbers of INSERTs will increase the likelyhood that a checkpoint will have to wait for one or more sessions to exit their critical section code. Inversely, the beginning of a checkpoint will prevent any session from entereing a new critical section with a latch block that does not time out. You are seeing expected behavior. In later releases of IDS (9.40 and later) you would be able to set LRU_MIN/MAX_DIRTY to fractional values and reduce the number of dirty buffers which would improve the checkpoint duration once the critical sections had been cleared, but there's nothing to do about the critical section problem except to upgrade to IDS 11.50 which now has completely non-blocking checkpoints. Note that IDS 7.31 is out-of-support! Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Apr 7, 2009 at 9:54 PM, PANJIE KITT <panjiejump@gmail.com> wrote: > I have an IDS, 7.31.FD3 as a database server for Java GUI client > application. > There were many times that the database checkpoint duration was very long > while concurrent users were not as many as usual (there were some working > days > that the users work load was low). As checked in the message log, there was > no > error report or anything. I tried to check if there was any abnormal > session > working on the database but the session activities found seemed to be the > user > normal processes. The only thing I found out was there were INSERT > transactions processing while the onstat output header said that > transactions > was blocked by checkpoint request. Also, the client applications got slow > responds too. > > At first I thought lots of INSERT transactions processing at the same time > caused this to happened but the strange thing is there were many times that > it > does not happen when there were many concurrent users inserting large > amount > of data but when there were less concurrent users inserting smaller amount > of > data the problem occurs! > > I also checked with my engineer if there were failed disks and the answer I > got was all disks are working just find too. Could you possibly give me an > advice on how to figure out what causes the problem, please? > > onconfig <partial> > ============== > # Shared Memory Parameters > > LOCKS 1000000 # Maximum number of locks > BUFFERS 320000 # Maximum number of shared buffers > NUMAIOVPS 9 # Number of IO vps > PHYSBUFF 512 # Physical log buffer size (Kbytes) > LOGBUFF 512 # Logical log buffer size (Kbytes)> LOGSMAX 300 # Maximum number of logical log files > CLEANERS 96 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address > SHMVIRTSIZE 5120000 # initial virtual shared memory segment size > SHMADD 16384 # Size of new shared memory segments > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 400 # Check point interval (in sec) > LRUS 256 # Number of LRU queues > LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit > LTXHWM 40 # Long transaction high water mark > LTXEHWM 60 # Long transaction high water mark > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636920273882cc904670cc92f

Check the tail of onstat -R Is your total count of dirty buffers staying below 6400? If it significantly exceeds the designated 2% max of 320000 buffers, then your LRUs aren't flushing fast enough and the checkpoints have more work. If you see this, bump your LRUs to 512. Raising CLEANERS from 96 to 128(max) could help too. Are you using AIO or KAIO? If using AIO, you may need to add a few AIOVPs. Dave Griffen

Dear, Andreas: Thank you for the reply. The indexes of the affected tables were recently recreated and also one of those table containing 90 million records was rebuild.But unfortunately, the problem still occurs. Best regards, Panjie Kitt

Here is onstat -R tail: 360 dirty, 319885 queued, 320000 total, 524288 hash buckets, 2048 buffer size start clean at 2% (of pair total) dirty, or 25 buffs dirty, stop at 1% 0 priority downgrades, 0 priority upgrades If I increase CLEANERS from 96 to 128, will there be any effect besides faster checkpoint operation?

thank you for the response. If I increase CLEANERS from 96 to 128, will there be any effect besides faster checkpoint operation?

Are there possibility that, while process lots of transaction, the database server process and response slowly but the checkpoint duration are not long at all?(2-4 seconds on average) Thank you. Panjie Kitt

Yes. One example: Think of CPU-intensive transaction processing - like doing single updates on large tables without appropriate index and quite big buffer cache : IDS needs a lot of CPU to scan all the pages (in memory) - but it only changes a few pages - not much to do at checkpoint. Regards, Andreas ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von PANJIE KITT Gesendet: Donnerstag, 9. April 2009 08:21 An: ids@iiug.org Betreff: Re: Checkpoint Duration [15498] Are there possibility that, while process lots of transaction, the database server process and response slowly but the checkpoint duration are not long at all?(2-4 seconds on average) Thank you. Panjie Kitt ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Well, when I used 'top' command to monitor server utilization, CPU idle went down to 2.8% and it kept swinging up and down. I thought maybe it was because the transactions were overloaded but then, I have no idea what caused that to happen because the transaction process was as same as usual and no one had modified any database configuration or anything. I can't run the oncheck command now because users are still working. Is there anything else I should do to collect more clues?