Incorrect "Excess TblRSz" and "Excess IdxRSz"



  • Offhand I'd say no.

    E.g.:
    BT: 100
    TRS: 400
    lowest table #: 100
    highest table #: 490
    actual excess: 9
    calculated excess: (TRS - maxTbl#) - abs( BT ) = (400 - 490) - 100 = -190

    I think the values to be compared are:

    • highest table number ("HT")
    • highest stats table ("HST")

    The latter is my arbitrary name for the highest table # for which stats will be collected, given BT and TRS. It is equal to TRS + BT - 1.

    E.g., it gives the correct answers in these cases:
    BT 1, TRS 50: HST = 50 + 1 - 1 = 50
    BT -500, TRS 505: HST = 505 + (-500) - 1 = 4
    BT 100, TRS 400: HST = 400 + 100 - 1 = 499

    So:
    excess = HST - HT
    excess = (TRS + BT - 1) - HT

    Does that make sense?



  • Separately, it's also worth comparing BT (basetable) to LT (lowest table #). If BT > LT then BT is too high. (It also means the currently-calculated "excess" may be inaccurate.)

    BT is not likely to be set wrong, if set at all, but it's an easy check to do and it will keep monkeys like me out of trouble. 😉


  • administrators

    We may have conflicting goals. I am primarily trying to ensure that ProTop users can always monitor their application tables. So a base value > 1 isn't something that I want to encourage or support.

    The over-arching goal of the "excess" metrics is to make sure that Joe Ordinary User has an indication that application data is not being adequately monitored.

    Optionally I do sort of want to ensure that when certain persons who won't be named but whose initials might be "Rob Fitzpatrick" set the base less than zero or otherwise muck around doing fun stuff that it isn't needlessly wrong. But some of these use cases don't seem like something that I can support without losing the usefulness of the metric for the types of people that are the primary target.

    As you suggested, I think the ultimate solution will have to be to go down the path of showing a bunch of different scenarios such as "monitor application data", "monitor meta schema", "monitor everything" etc and show the combination of values needed for those scenarios.



  • I agree I'm an "outlier" and I'm really not trying to tailor PT to my specific needs. You've been very accommodating and I'm not trying to push that.

    That said, I like the calculation I provided (the excess one, not the basetable one) as it works, as far as I am aware, in all cases whether BT is 1 or not. So I think it is a reasonable way to calculate excess stats slots for Joe Ordinary.


  • administrators

    @rob-fitzpatrick said in Incorrect "Excess TblRSz" and "Excess IdxRSz":

    BT 100, TRS 400: HST = 400 + 100 - 1 = 499

    This, I think, (hope) is the last bit giving me trouble.

    If you were monitoring sports2000 with these settings, using these calculations ProTop would indicate that you have an "excess" of 499 table "slots". But in reality you are monitoring no table stats at all.

    So perhaps this leads to a different thought -- I am really trying to illustrate "unmonitored (application) tables" (or indexes) and "excess" is probably the wrong way to communicate that.

    It has been a long and winding road to get there -- but I think that is really what I need to recognize.

    Thanks!



  • So, keeping our friend Joe Ordinary in mind, maybe two things are relevant:

    • do I have unmonitored objects?
    • if I don't today, how close am I to being there if my schema grows?

  • administrators

       ┌────────────────────────── Excess Range Calculation ───────────────────────────┐
       │                                                                               │
       │                  _tableBase:    -500                    _indexBase:       1   │
       │             -tablerangesize:     600               -indexrangesize:      50   │
       │        Highest Stats Table#:      99          Highest Stats Index#:      50   │
       │                                                                               │
       │ Actual Number of App Tables:      25  Actual Number of App Indexes:      55   │
       │          Minimum App Table#:       1           Minimum App  Index#:       8   │
       │          Maximum App Table#:      25           Maximum App  Index#:      62   │
       │      Unmonitored App Tables:       0       Unmonitored App Indexes:      12   │
       │                                                                               │
       │     Lowest Monitored Table#:    -361       Lowest Monitored Index#:       1   │
       │    Highest Monitored Table#:      25      Highest Monitored Index#:      50   │
       │                                                                               │
       │          Excess Table Range:      74            Excess Index Range:     -13   │
       │                                                                               │
       └───────────────────────────────────────────────────────────────────────────────┘
    

    My "scratch" numbers... soon to be a hot key or something along those lines 🙂



  • Cool. Very comprehensive. 👍

    One bit of feedback: Joe is probably more familiar with the param names, -basetable and -baseindex, than with their VST field names.

    One more: looks like "excess index range" is off by one.


  • administrators

        ┌─────────────────────────────── Table and Index Range Information ────────────────────────────────┐
        │                                                                                                  │
        │                          -basetable:       1                           -baseindex:       1       │
        │                     -tablerangesize:      50                      -indexrangesize:      50       │
        │                                                                                                  │
        │                Highest Stats Table#:      50                 Highest Stats Index#:      50       │
        │             Lowest Monitored Table#:       1              Lowest Monitored Index#:       1       │
        │            Highest Monitored Table#:      25             Highest Monitored Index#:      50       │
        │                                                                                                  │
        │                                   Application Tables and Indexes                                 │
        │                                                                                                  │
        │         Actual Number of App Tables:      25         Actual Number of App Indexes:      55       │
        │                  Minimum App Table#:       1                  Minimum App  Index#:       8       │
        │                  Maximum App Table#:      25                  Maximum App  Index#:      62       │
        │              Unmonitored App Tables:       0              Unmonitored App Indexes:      12       │
        │                                                                                                  │
        │                  Excess Table Range:      24                   Excess Index Range:     -12       │
        │                                                                                                  │
        │              Minimal App -basetable:       1               Minimal App -baseindex:       8       │
        │         Minimal App -tablerangesize:      25          Minimal App -indexrangesize:      55       │
        │                                                                                                  │
        │                Suggested -basetable:       1                 Suggested -baseindex:       8       │
        │           Suggested -tablerangesize:      75            Suggested -indexrangesize:     104       │
        │                                                                                                  │
        │                                   System Tables and Indexes                                      │
        │                                                                                                  │
        │                       Lowest Table#:    -361                        Lowest Index#:  -1,610       │
        │                      Highest Table#:      25                       Highest Index#:   1,093       │
        │                                                                                                  │
        │       Suggested Complete -basetable:    -361        Suggested Complete -baseindex:  -1,610       │
        │  Suggested Complete -tablerangesize:     436   Suggested Complete -indexrangesize:   2,753       │
        │                                                                                                  │
        │                                                                                                  │
        │  * "System" tables and indexes include the meta-schema but do not count psuedo tables such       │
        │    as VSTs and SQL views as these do not have any CRUD statistics associated with them.          │
        │                                                                                                  │
        └──────────────────────────────────────────────────────────────────────────────────────────────────┘
    


  • Nice. 🙂


 

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