Incorrect data in Activity browse
-
My testing continues...
11.6.3, CentOS 7.4
2 DBs
1st DB params:
BT: -207
TRS: 736
BI: -1591
IRS: 2704I don't use these params because I'm a masochist. I'm just dealing with PSC's wacky object numbering. The system table numbers are kind of reasonable I guess. But the index numbers definitely are not.
(Side note, in case anyone is interested in why I chose these numbers:
I want to see activity on meta-schema/auth/storage tables and indexes, in addition to application objects. The lowest such index is _storageobject._object-area-number, number -1591. The highest is _word-rule._wr-number, number 1093. The lowest such table is _sec-authentication-domain, number -207. So only TRS is determined by the size of my application schema; the rest are based on the above. I'm just doing this in testing, I don't do it at client sites; not yet, anyway.)My configuration of PT varies. Sometimes, when the DB is under load, I use the default of Interval/Rate when looking at Table Activity and Index Activity. Sometimes I look at Interval/Summary, and when the test is over and the DB is quiet I look at Cumulative/Summary or Raw/Summary.
The issue is that when using Raw/Summary, sorted by descending reads, in Table Activity and Index Activity I expect to see the same data that I get from similar queries of _tablestat and _indexstat. In Index Activity, I don't. I just get the top handful of system indexes, not the application indexes that have higher reads than those system indexes.
I'm curious why there is this difference in behaviour between TA and IA, and whether there is something I can change to address it. I tried playing with different values in "Z" but that didn't seem to help.
P.S.: This might be a case of me using PT incorrectly, or with an unsupported DB configuration, so I'm posting in the "Using ProTop" category rather than "Bugs".
-
Further to that, it looks like Table Activity is missing data too. Here is a side-by-side of my query of _tablestat (on the left) and the data from PT's Table Activity, both sorted by descending reads; the top 15 or so tables. They weren't taken at exactly the same time so I'm not concerned about small discrepancies in the numbers.
Table name Reads Table Name Read v ───────────────── ────────────── ─────────────────────────────────────── sd-joint 16,450,838 sd-joint 16450838 sd-payt 239,267 sd-payt 239267 _Field 152,294 _Field 152257 _File 29,868 _File 29044 sd-cash-drawer 26,205 sd-cash-drawer 26199 sd-teller-tran 25,125 sd-teller-tran 25125 occup 18,373 occup 18373 sd-xact 14,058 sd-xact 14058 sd-acct-xact 13,378 sd-acct-xact 13378 _Index 12,984 _Index 11774 _StorageObject 8,622 sd-acct 2829 user-parm 7,702 gen-key 95 tran-type 5,953 _sec-authentication-domain 57 sd-xdesc 4,337 _Db-Option 54 _Index-Field 3,818 sd-payt-det 47 employee 3,793 _sec-authentication-system 25The concern is that there are a lot of missing line items in PT. The two lists agree on the first ten line items. But the 11th in the PT list, table sd-acct, is number 19 in my list. The 12th, gen-key, is number 52 in my list.
Sorry if I'm repeating myself Tom; you may have already explained this to me in the past. But I need to know which numbers I can trust and which ones to ignore. And, ideally, I'd like to be able to trust all of them.
-
There might be a misfeature in play.
The table and index activity screens filter out results after the top 10 for each of the CRUD operations and then merge the results so that active updates don't get buried when the corresponding reads are (relatively) low.
The issue is that maintaining all of the tables and indexes can be a performance issue. ProTop is focused on the "top" culprits so on these screens (and "User IO", possibly a few others) only the top X results are actually being kept.
And in most cases people aren't really looking beyond the top 10 so you've likely never noticed it before. It may be more obvious now that I am being more insistent that you must have a reasonable window size (hoist by my own petard?)
It seemed like a good idea at the time...
There have been a lot of advances in the efficiency of some of that code, machine horsepower is better now so maybe it is no longer necessary or useful? I haven't really tested that in a long time.
-
Makes sense now, thanks for the explanation.
So given that more than 10 records are displayed, and records 11 to X aren't in some order that is obvious (to me), what are the remaining records? Should they even appear? Is there value in being able to see the records after the tenth?
-
Whatever is present will, by default, be sorted by reads. You can change the sort order with "z". You /should/ then be able to see the top 10 creates etc by rotating through the sort options.
If "top X" is greater than the number of rows displayed it would never get confusing (because there is no scrolling and quite likely never will be).
Now that I'm having to explain it I freely admit that it seems kind of counter-intuitive and clunky.
I feel some options to change all of that coming to a release candidate soon
-
Further to this, and assuming that I should see the same "culprits" in table and index CRUD within the top ten of each, I have an issue that I think I haven't reported before. In this case, the number two table ("vendor") in reads (Raw/Summary) doesn't show up in PT. Here is PT data and my data:
Tbl# Area# Table Name RM Chain #Records Frag% Scat Turns AvgRow Create Read v Update Delete OS Read ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── 274 7 sd-joint 502 29666 0.00% 1 1067.01 42 0 31653874 0 0 246 277 11 sd-payt 250412 44931896 0.22% 1 0.01 189 227 306531 886 0 5379 -2 6 _Field 10318 76.55% 6 16.33 315 0 168536 0 0 0 85 7 corp-individ 409 109744 52.71% 1 1.03 289 0 113334 0 0 4012 271 7 sd-cash-drawer 508 733 0.00% 1 52.01 117 0 38121 254 0 23 282 15 sd-teller-tran 336 19871693 0.00% 1 0.00 108 20 34233 0 0 20767 284 7 sd-xact 503 572 0.00% 1 54.48 84 0 31161 0 0 7 270 7 sd-acct-xact 507 8382 0.00% 1 3.54 50 0 29714 0 0 18 201 7 occup 507 164 0.00% 1 140.01 33 0 22961 0 0 2 -1 6 _File 688 92.30% 5 30.25 450 0 20810 0 0 0 Table Table name Reads Reads/sec O/S Reads Hit % Creates Updates Deletes ───── ──────────────────────────────── ────────────── ────────────── ────────────── ─────── ────────── ────────── ────────── 274 sd-joint 31,653,874 3,521 246 99.999 0 338 vendor 1,209,643 135 51 99.996 0 277 sd-payt 306,531 34 5,379 98.245 227 886 -2 _Field 168,536 19 100.000 0 85 corp-individ 113,334 13 4,012 96.460 0 271 sd-cash-drawer 38,121 4 23 99.940 254 282 sd-teller-tran 34,233 4 20,767 39.336 20 0 284 sd-xact 31,161 3 7 99.978 0 270 sd-acct-xact 29,714 3 18 99.939 0 201 occup 22,961 3 2 99.991 0 -1 _File 21,641 2 100.000 0
