Navigation

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Incorrect "Excess TblRSz" and "Excess IdxRSz"

    ~~Bugs~~
    2
    22
    11466
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Rob Fitzpatrick
      Rob Fitzpatrick last edited by

      I think the stat that will help most people and be simplest to report is: how many application tables and indexes do not have stats coverage. That could be because they are below base* or because they are beyond the rangesize.

      1 Reply Last reply Reply Quote 0
      • tom
        tom administrators last edited by

        Tables I can do that. Indexes are not clean. So from a consistency perspective I was thinking that it makes more sense to just count all of each.

        For one thing it's a whole lot easier to explain. And there is a lot to be said for that.

        Yes, that will result in about three hundred more objects than they really absolutely need -- but in the big scheme of things is that really a problem?

        (A certain famous application has a configurator tool that applies the same -tablerangesize to every database in its scope, which results in thousands of excess *rangesize being allocated because other than the main db none of them need more than a handful...)

        I do like the idea of an optional more detailed report too.

        1 Reply Last reply Reply Quote 0
        • Rob Fitzpatrick
          Rob Fitzpatrick last edited by Rob Fitzpatrick

          Just curious, why are indexes not "clean"? I mean, apart from the unindexed query it might require to look for application indexes. But you're already caching them in a temp-table so it shouldn't be an issue.

          Certainly it's the case that some people are interested in their object counts as their schemas evolve. Mine grow from release to release, so I like to know how many tables and indexes I have. But I think object count, while interesting in and of itself, is insufficient for judging whether you have all the stats you need.

          When I was first learning about the metaschema and discovered the stats tables and params, my first stab at sizing them was by counting tables and indexes. But I quickly realized I was missing stats records. Then I discovered that indexes start at 8, not 1. I also discovered, primarily in internal (dev/qa/test) environments with lots of schema changes, that there could be gaps in the numbers. So a database with 100 tables and 200 indexes could have a highest table number above 100 and a highest index number above 200. Indexes especially can have gaps due to the temp indexes sometimes created in incremental schema files.

          So I settled on my current process of finding the lowest and highest object numbers, calculating the start and end numbers of the stats "window", and determining if any of the application objects are outside that window, i.e. there are no stats kept for them.

          Yes, that's a lot to explain and it may be over a newbie's head. It would have been for me, initially. But in my opinion, it's one of the things a newbie needs to learn to rise above being a newbie.

          Of course PT reports a lot more than CRUD stats. But they are an important part of what it does, and an important reason why a person goes to the trouble of using it at all, as opposed to just running promon which is much easier and more straightforward. Using it to look at CRUD could be frustrating if the user learns, after the fact, that they weren't seeing the whole picture. You use highlighting to call out badly-set params in other parts of PT. I think it would be useful for users if it were somehow obvious that they aren't able to see CRUD stats for all of their application objects.

          1 Reply Last reply Reply Quote 0
          • tom
            tom administrators last edited by

            Indexes aren't "clean" because "system" indexes and application indexes are all mixed together. You don't have segregation by negative idx-num like you do for meta-schema tables. (There are some negatively numbered system indexes.)

            It is relatively easy to say that if you want all application tables you need to set the base to X and the range to Y. And there won't be anything else in there.

            With indexes you will be including things that are not "application".

            That's why I don't see them as "clean".

            1 Reply Last reply Reply Quote 0
            • Rob Fitzpatrick
              Rob Fitzpatrick last edited by

              Ok, that makes sense. But that's just life in OE; there's nothing you can do about it.

              Well, having written that, there is something you could do about it if it really mattered a lot. Your results go into temp tables before you display them, so you could filter out index records where the index name begins "_". For the record, I'm not suggesting you do that. Seems like a lot of work for not much gain.

              On a side note, I'd really like to see Progress renumber their indexes. It shouldn't break anything; no one should be taking dependencies on indexes having certain numbers (though I wouldn't be shocked if they did). Release 12.0 would be a good place to do that type of thing. They would have to rebuild their sample and empty DBs but it would be a win for customers.

              1 Reply Last reply Reply Quote 0
              • tom
                tom administrators last edited by

                🙂

                1 Reply Last reply Reply Quote 0
                • Rob Fitzpatrick
                  Rob Fitzpatrick last edited by Rob Fitzpatrick

                  Feel free to vote:
                  https://community.progress.com/community_groups/products_enhancements/i/openedge_database_enhancements_-_tell_us_what_youd_like_to_see/renumber_system_indexes
                  ☺

                  1 Reply Last reply Reply Quote 0
                  • tom
                    tom administrators last edited by tom

                    How about something like:

                    excessTbl = (( tblRange - maxTblNum ) - abs( _TableBase ))
                    excessIdx = (( idxRange - maxIdxNum ) - abs( _IndexBase ))
                    

                    I think that works for positive and negative -base*. At least it seemed to make sense when I tried a few test cases.

                    • maxTblNum is the maximum table number less than 32768
                    • maxIdxNum is the maximum index number under 993 if there are no application indexes > 993

                    That should avoid over-allocating for people who do not deliberately want to monitor system stuff.

                    Rob Fitzpatrick 1 Reply Last reply Reply Quote 0
                    • Rob Fitzpatrick
                      Rob Fitzpatrick @tom last edited by

                      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?

                      tom 1 Reply Last reply Reply Quote 0
                      • Rob Fitzpatrick
                        Rob Fitzpatrick last edited by

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

                        1 Reply Last reply Reply Quote 0
                        • tom
                          tom administrators @Rob Fitzpatrick last edited by

                          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.

                          1 Reply Last reply Reply Quote 0
                          • Rob Fitzpatrick
                            Rob Fitzpatrick last edited by

                            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.

                            1 Reply Last reply Reply Quote 0
                            • tom
                              tom administrators last edited by

                              @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!

                              1 Reply Last reply Reply Quote 0
                              • Rob Fitzpatrick
                                Rob Fitzpatrick last edited by

                                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?
                                1 Reply Last reply Reply Quote 0
                                • tom
                                  tom administrators last edited by

                                     ┌────────────────────────── 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 🙂

                                  1 Reply Last reply Reply Quote 0
                                  • Rob Fitzpatrick
                                    Rob Fitzpatrick last edited by

                                    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.

                                    1 Reply Last reply Reply Quote 0
                                    • tom
                                      tom administrators last edited by

                                          ┌─────────────────────────────── 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.          │
                                          │                                                                                                  │
                                          └──────────────────────────────────────────────────────────────────────────────────────────────────┘
                                      
                                      1 Reply Last reply Reply Quote 0
                                      • Rob Fitzpatrick
                                        Rob Fitzpatrick last edited by

                                        Nice. 🙂

                                        1 Reply Last reply Reply Quote 0
                                        • First post
                                          Last post