Hi All,
OE 10.2BSp07 32-Bit on Wndows Server 2008 64-bit.
We seem to be having some issues with Checkpointing. The issue is much much worse during online backups but during the day we are experiencing the issues too. I've been looking into this and the issues seem to tie in with checkpoints.
Although the singular APW seems to be doing a decent job, i'm in the process of adding another just to help out. Our disks are maintained by a 3rd party and i'm in the process of retrieving information regarding the make-up of the disks to understand if we can separate the bi and db files. Other than that i'm a little stuck. I feel the cluster size could be too large.
I have a 24hour recording of Checkpoints and APW usage and the only time we flush is at the beginning of the backup and APW writes % is rarely below 80%.
We have 2 extents for the bi; 1 at 4gb and a variable.
Any information would be greatly appreciated.
Here is an example set of checkpoints during normal activity;
And here are some things you may be interested in;
OE 10.2BSp07 32-Bit on Wndows Server 2008 64-bit.
We seem to be having some issues with Checkpointing. The issue is much much worse during online backups but during the day we are experiencing the issues too. I've been looking into this and the issues seem to tie in with checkpoints.
Although the singular APW seems to be doing a decent job, i'm in the process of adding another just to help out. Our disks are maintained by a 3rd party and i'm in the process of retrieving information regarding the make-up of the disks to understand if we can separate the bi and db files. Other than that i'm a little stuck. I feel the cluster size could be too large.
I have a 24hour recording of Checkpoints and APW usage and the only time we flush is at the beginning of the backup and APW writes % is rarely below 80%.
We have 2 extents for the bi; 1 at 4gb and a variable.
Any information would be greatly appreciated.
Here is an example set of checkpoints during normal activity;
Code:
06/10/16 Checkpoints
14:17:39
Ckpt ------ Database Writes ------
No. Time Len Freq Dirty CPT Q Scan APW Q Flushes Duration Sync Time
1463 14:11:01 398 0 261213 229170 130 2466 0 11.48 10.49
1462 14:04:14 401 407 125691 156792 286 519 0 0.79 0.13
1461 13:36:50 1610 1644 127401 72532 29224 69148 0 0.52 0.06
1460 13:03:31 1902 1999 4421 25673 43030 7071 0 0.62 0.16
1459 12:24:19 2256 2352 39281 5152 53118 158885 0 0.65 0.06
1458 11:45:18 2195 2341 159114 98902 35486 163071 0 1.58 0.11
1457 11:24:46 1203 1232 143526 50671 19526 179344 0 0.86 0.07
1456 11:09:50 868 896 227508 73238 10868 323464 0 1.48 0.17And here are some things you may be interested in;
Code:
06/10/16 Status: BI Log
09:09:04
Before-image cluster age time: 0 seconds
Before-image block size: 4096 bytes
Before-image cluster size: 262128 kb (268419072 bytes)
Number of before-image extents: 2
Before-image log size (kb): 4095996
Bytes free in current cluster: 120801211 (46 %)
Last checkpoint was at: 06/10/16 08:58
Number of BI buffers: 100
Full buffers: 2
06/10/16 Activity: BI Log
10:31:51 05/22/16 14:21 to 06/10/16 09:07 (450 hrs 46 min)
Total Per Min Per Sec Per Tx
Total BI writes 104067K 3940 65.67 0.16
BIW BI writes 70026156 2589 43.15 0.10
Records written 2852607K 108006 1800.10 4.33
Bytes written 363470M 14092041 234867.35 564.81
Total BI Reads 17636890 652 10.87 0.03
Records read 6178374 228 3.81 0.01
Bytes read 723679K 27400 456.67 1.10
Clusters closed 1428 0 0.00 0.00
Busy buffer waits 19415395 718 11.96 0.03
Empty buffer waits 101970 4 0.06 0.00
Log force waits 0 0 0.00 0.00
Log force writes 0 0 0.00 0.00
Partial writes 12966547 479 7.99 0.02
Input buffer hits 390025 14 0.24 0.00
Output buffer hits 925810 34 0.57 0.00
Mod buffer hits 193711 7 0.12 0.00
BO buffer hits 1933250 71 1.19 0.00
Shared Resources:
Busy After Image Extent: F:\db\ai\live.a4
Number of database buffers (-B): 16000000
Number of database alternate buffers (-B2): 0
Number of before image buffers (-bibufs): 100
Number of after image buffers (-aibufs): 100
Excess shared memory size (-Mxs): 386
Before-image truncate interval (-G): 0
No crash protection (-i): Not enabled
Maximum private buffers per user (-Bpmax): 64
Current size of locking table (-L): 35008
Locking table entries in use: 818
Locking table high water mark: 7321
Maximum number of clients per server (-Ma): 3
Max number of JTA transactions (-maxxids): 0
Delay of before-image flush (-Mf): 3
Maximum number of servers (-Mn): 331
Maximum number of users (-n): 932
Before-image file I/O (-r -R): Raw
Shared memory version number: 6412385
Number of semaphores used: 1267
Broker status: Executing
BI Writer status: Executing
AI Writer status: Executing
Watchdog status: Executing
Number of page writers: 1
Number of self-service users: 5
Number of remote users: 612
Number of servers: 331
Number of shut-downs: 0
Number of monitors: 2
LRU Disabled (-nolru): No
LRU force skips (-lruskips): 5
LRU2 force skips (-lru2skips): 0
Server message wait time (-Nmsgwait): 2
Delay first prefetch message (-prefetchDelay): Disabled
Prefetch message fill percentage (-prefetchFactor): 0
Minimum records in prefetch msg (-prefetchNumRecs): 16
06/10/16 Activity: Page Writers
14:21:48 05/22/16 14:21 to 06/10/16 09:07 (450 hrs 46 min)
Total Per Min Per Sec Per Tx
Total DB writes 297388K 11260 187.66 0.45
APW DB writes 290817K 11011 183.52 0.44
scan writes 17315346 640 10.67 0.03
apw queue writes 32614177 1206 20.10 0.05
ckp queue writes 242058K 9165 152.75 0.37
scan cycles 307 0 0.00 0.00
buffers scanned 4809449K 182096 3034.93 7.30
bufs checkpointed 266230K 10080 168.00 0.40
Checkpoints 1428 0 0.00 0.00
Marked at checkpoint 266810K 10102 168.37 0.40
Flushed at checkpoint 1046104 39 0.64 0.00
Number of APWs: 1
but not much i can do there.
