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: 2704

    I 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           25
    

    The 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.


  • administrators

    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?


  • administrators

    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           
    

 

Looks like your connection to ProTop Community was lost, please wait while we try to reconnect.