G
George Potemkin
Guest
I missed that Status: Database Service Manager reports the counters since db startup. So I slightly "re-worded" the same statistics I posted above. Status: Database Service Manager Time Free Msgs Used Msgs Locked by RPLS Filled Access Collisions AI Blocks Percent Access/Block Notes/Block 04:00:28 187428 0 0 0 146,653,272 24813 6,161,883 96% 23.80 140 04:30:29 176446 10982 10982 0 133,841 10 29,805 99% 4.49 137 05:00:30 129880 57548 57548 0 116,841 14 75,856 99% 1.54 138 05:30:31 82080 105348 105348 0 109,030 27 72,563 99% 1.50 140 06:00:32 40849 146579 146579 0 102,666 31 66,092 99% 1.55 140 06:30:33 1727 185701 185701 0 143,567 39 85,502 99% 1.68 139 07:00:34 90 187338 187338 2757 140,428 51 49,726 99% 2.82 138 07:30:35 9 187419 187419 3282 147,051 51 46,937 99% 3.13 140 08:00:36 6094 181334 181334 787 152,776 52 64,125 99% 2.38 140 08:30:37 148 187280 187280 1850 135,885 44 43,748 99% 3.11 147 09:00:38 282 187146 187146 2746 154,694 43 29,054 98% 5.32 153 09:30:39 11 187417 187417 2498 150,499 55 40,463 98% 3.72 155 10:00:40 8 187420 187420 2645 160,840 41 27,531 98% 5.84 153 10:30:40 0 187428 187428 2506 173,083 49 25,577 98% 6.77 157 11:00:41 6 187422 187422 1359 193,062 31 12,627 97% 15.29 159 11:30:42 0 187428 187428 1021 193,274 16 9,685 96% 19.96 159 12:00:43 0 187428 187428 839 191,698 20 7,933 96% 24.16 159 12:30:44 0 187428 187428 990 192,201 17 9,313 96% 20.64 159 13:00:45 6 187422 187422 1113 194,764 23 10,732 97% 18.15 161 13:30:46 0 187428 187428 1508 195,405 36 15,022 97% 13.01 161 14:00:47 0 187428 187428 1700 194,589 53 16,662 97% 11.68 161 14:30:48 0 187428 187428 2525 196,809 46 24,024 98% 8.19 161 15:00:49 8 187420 187420 2627 191,212 59 26,520 98% 7.21 160 15:30:51 2244 185184 185184 2285 175,022 70 28,485 98% 6.14 159 16:00:52 24712 162716 162716 1165 172,104 87 42,393 99% 4.06 161 16:30:53 103103 84325 84325 0 143,864 55 26,906 98% 5.35 152 17:00:54 123870 63558 63558 0 68,242 14 17,954 97% 3.80 145 17:30:55 141597 45831 45831 0 67,828 9 19,297 97% 3.51 146 18:00:56 187428 0 0 0 178,082 40 16,868 97% 10.56 149 18:30:57 187428 0 0 0 257,580 44 7,775 94% 33.13 143 19:00:58 187428 0 0 0 345,113 96 25,769 98% 13.39 149 19:30:59 187428 0 0 0 355,315 77 22,158 98% 16.04 137 20:00:59 187428 0 0 0 281,343 69 18,364 98% 15.32 137 20:31:00 187428 0 0 0 81,337 10 902 89% 90.17 142 21:01:01 187428 0 0 0 60,178 5 190 71% 316.73 138 21:31:02 187428 0 0 0 59,145 2 247 79% 239.45 140 22:01:03 187428 0 0 0 55,854 4 130 71% 429.65 132 22:31:04 187428 0 0 0 53,761 0 28 25% 1,920.04 125 23:01:05 187428 0 0 0 57,486 1 161 65% 357.06 137 23:31:06 187428 0 0 0 48,780 1 15 42% 3,252.00 132 00:01:07 187428 0 0 0 51,271 3 658 96% 77.92 147 00:31:08 187428 0 0 0 50,421 3 1,696 98% 29.73 143 01:01:09 187428 0 0 0 78,023 3 442 71% 176.52 140 01:31:10 187428 0 0 0 172,987 4 2,707 83% 63.90 143 02:01:11 187428 0 0 0 80,442 1 281 51% 286.27 142 02:31:12 187428 0 0 0 85,199 1 593 68% 143.67 134 03:01:13 187428 0 0 0 185,981 23 9,860 97% 18.86 132 where: First interval - 410 hrs 14 min (db uptime). The rest intervals - 30 min. "Filled", "Access", "Collisions" are the increments per interval of the corresponding counters in Status: Database Service Manager ( "Area Filled Count", "Access Count", "Access Collisions" ). "AI Blocks" - the number of AI blocks generated per interval = "Total AI Writes" minus "Partial Writes" (from Activity: AI Log) "Percent" = "AI Blocks" / "Total AI writes" * 100% = an indirect indicator of idle db. "Notes/Block" = "Records written" (from Activity: AI Log) / "AI Blocks". AI blocksize = 16K. Average size of recovery notes is 112 bytes in the given example. Conclusions/Remarks: An unknown process(es) on source db is creating the collisions trying to access the pica queue but all 47 checks by promon show that the message entries locked only by Replication Server. The collisions are extremely low even during the most busy time: 96 collisions per 30 minutes (or one collision in 20 sec). In other words, almost 100% of the pica queue accesses are generated by Replication Server. Replication Server generates at least one access per incoming AI block (e.g. 102,666 accesses per 66,092 AI blocks). When transaction activity is low the number of pica queue accesses per AI block can be huge. In other words, Replication Server accesses the pica queue at least 27 times per sec (e.g. 48,780 accesses per 30 min with only 15 incoming AI blocks). For comparison: at busy time db was able to generate 85,502 AI blocks per 30 min or 48 AI blocks per sec. Max value of "Area Filled Count" per 30 min interval was 3,282 or almost twice per sec.
Continue reading...
Continue reading...