Hi
We are facing slowness in the database and when checked there were frequent checkpoint in the database. Not sure how the checkpoint occur so frequently. We have optimized BI cluster size as well as 3 APWs. Details are below
Progress Version 11.4
OS AIX
DB Size 84GB
DB Blocksize 8192
Before-image block size: 16384 bytes
Before-image cluster size: 16384 kb
Though there were no flush noted during checkpoint. Does this frequent checkpoint can slowdown DB performance?
Any idea what can be done to minimize the checkpoint frequency? I didn't find any active or long running transaction. Bi blocksize and cluster size is already optimized. Is there any way to find which process generating checkpoint ?
O/p from promon -> R&D -> 3. Other Displays ... -> 4. Checkpoints
10/05/17 Checkpoints 10:05:49 Ckpt ------ Database Writes ------ No. Time Len Freq Dirty CPT Q Scan APW Q Flushes Duration Sync Time 4745 10:05:46 3 0 74 106 1 0 0 2.17 2.12 4744 10:05:16 30 30 112 1924 27 1 0 0.63 0.56 4743 10:04:36 40 40 107 1794 71 1 0 3.09 2.93 4742 10:03:39 55 57 190 1615 123 13 0 4.49 4.28 4741 10:03:15 22 24 629 2356 21 3 0 4.54 1.84 4740 10:03:00 15 15 144 2014 9 0 1 2.05 1.60 4739 10:02:44 15 16 106 1731 17 1 0 1.70 1.61 4738 10:02:29 14 15 24 2220 3 2 0 1.75 1.54
10/05/17 Checkpoints 11:18:12 Ckpt ------ Database Writes ------ No. Time Len Freq Dirty CPT Q Scan APW Q Flushes Duration Sync Time 4840 11:18:11 1 0 1620 406 0 0 0 1.03 0.59 4839 11:17:52 18 19 906 1823 16 1 0 6.61 4.09 4838 11:17:35 17 17 1146 2063 15 0 2 3.40 2.36 4837 11:17:19 16 16 706 1586 16 0 12 5.66 3.57 4836 11:17:07 12 12 1183 2044 4 0 66 1.05 0.91 4835 11:16:42 25 25 597 1520 17 0 0 5.99 4.61 4834 11:16:24 18 18 1180 2069 16 2 33 2.36 1.74 4833 11:16:09 15 15 668 1595 24 0 0 2.61 1.16
> Does this frequent checkpoint can slowdown DB performance?
Yes, exactly by checkpoint's duration. And almost 100% of checkpoint's duration is sync time.
Thanks for your response George. Any idea what can be done to minimize the checkpoint frequency? I didn't find any active or long running transaction. Bi blocksize and cluster size is already optimized. Is there any way to find which process generating checkpoint ?
> what can be done to minimize the checkpoint frequency?
You can increase bi cluster size even more (by 2-4 times).
> Is there any way to find which process generating checkpoint ?
You can scan AI files. Process the scans by, for example, AiScanStat.p (check AiScanStat.Readme.txt):