Answered Number of reading

[BobyIsProgress]

Hi everyone,

On the way to optimize (again) our BDD and application I encounter a big question.

With the promon we are monitoring the number of reading on the DB. I tried to do the same with the VST, but the number of reading is less.....

Do you have any idea why ?

OPEN QUERY {&SELF-NAME} FOR EACH  _TableStat NO-LOCK,
                            FIRST _File WHERE _File._File-Number = _TableStat._TableStat-id NO-LOCK

Thanks a lot.

 

[Cringer]

Do you have -indexrangesize and -tablerangesize set correctly on your database startup parameters?

 

[BobyIsProgress]

I'm going to check that during the day and keep you update.

In addition to that. For a DB with something like 100 user or more, what is for you the highest number of reading possible for an efficient DB ?

 

[Cringer]

That's impossible to say. It depends on the data, how the data is used, and many other factors.
Looking at reads across the whole database for all users is not going to tell you much at all. You really want to be looking at the reads each user does in a given timeframe. If their reads are high, then you want to find out what they are doing.

 

[BobyIsProgress]

That's impossible to say. It depends on the data, how the data is used, and many other factors.
Looking at reads across the whole database for all users is not going to tell you much at all. You really want to be looking at the reads each user does in a given timeframe. If their reads are high, then you want to find out what they are doing.

I see your approach I will dig on this way. Thank you for your expertise

So I will work with the VST _UserTableStat

 

[TomBascom]

ProTop will do all of this for you "out of the box". Go to ProTop Monitoring and Alerting Service and click on the "Download" button.

Even if you don't want to use the tool the source code will show you how to code the VST logic. But you really ought to just use the tool.

If you only have a single user running (frequently true in dev or test scenarios), or if one user dominates the activity (frequently true when looking at production problems) then you really don't need the detailed user level statistics, the global ones are going to tell you the same story. But ProTop does also let you drill down to a specific user in those cases where it is needed.

You can even turn on the client statement cache and see what line# of what program is guilty.

The "?" key will bring up a help screen that will explain the commands needed to monitor specific situations.

ProTop also includes "readprobe", a benchmarking tool that will tell you interesting things about your server's maximum potential read throughput. Readprobe measures read throughput without any IO -- that is as if you have the most perfectly tuned database ever. In real life you will not get your application to go quite that fast. But you will know what your "speed limit" is. The only way to go faster is to buy better hardware at that point.

The readprobe tool is in the rp2017 sub directory of the protop download.

 

[BobyIsProgress]

Hi Tom,
Thank you I will try it with my team

 

[Rob Fitzpatrick]

Do you have -indexrangesize and -tablerangesize set correctly on your database startup parameters?

I'm going to check that during the day

When ProTop starts, it will helpfully show you if the stats parameters are set improperly, given your schema, and even suggest appropriate values. Example:

┌─────────────────────────────── Table and Index Range Information ────────────────────────────────┐
│                                                                                                  │
│      The -*rangesize parameters are not set adequately to monitor all tables and indexes!!!      │
│                                                                                                  │
│                          -basetable:     -35                           -baseindex:     -30       │
│                     -tablerangesize:      50                      -indexrangesize:      50       │
│                                                                                                  │
│                Highest Stats Table#:      14                 Highest Stats Index#:      19       │
│             Lowest Monitored Table#:     -32              Lowest Monitored Index#:       0       │
│            Highest Monitored Table#:       9             Highest Monitored Index#:      19       │
│                                                                                                  │
│                                   Application Tables and Indexes                                 │
│                                                                                                  │
│         Actual Number of App Tables:       9         Actual Number of App Indexes:      25       │
│                  Minimum App Table#:       1                  Minimum App  Index#:       8       │
│                  Maximum App Table#:       9                  Maximum App  Index#:      32       │
│              Unmonitored App Tables:       0              Unmonitored App Indexes:      13       │
│                                                                                                  │
│                  Excess Table Range:       5                   Excess Index Range:     -12       │
│                                                                                                  │
│              Minimal App -basetable:       1               Minimal App -baseindex:       8       │
│         Minimal App -tablerangesize:       9          Minimal App -indexrangesize:      25       │
│                                                                                                  │
│                Suggested -basetable:       1                 Suggested -baseindex:       8       │
│           Suggested -tablerangesize:      59            Suggested -indexrangesize:      74       │
│                                                                                                  │
│                                   System Tables and Indexes                                      │
│                                                                                                  │
│                       Lowest Table#:    -361                        Lowest Index#:  -1,610       │
│                      Highest Table#:       9                       Highest Index#:   1,093       │
│                                                                                                  │
│       Suggested Complete -basetable:    -361        Suggested Complete -baseindex:  -1,610       │
│  Suggested Complete -tablerangesize:     420   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.          │
│                                                                                                  │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

If they are set properly, you can still access this display by hitting "1".

 

[TomBascom]

Some guy named "Rob" kept pestering me to improve those calculations.

I hear they're pretty good now.

 

[Rob Fitzpatrick]

Some guy named "Rob" kept pestering me to improve those calculations.

I hear they're pretty good now.

Poor you. He sounds terrible.

 

[TomBascom]

He buys me a beer once in a while so I give him some slack.

 

[BobyIsProgress]

Well, I did a bit a bit of research on ProTop web page and I saw that your experience is precious. So really thank you to take time to explain my mistakes and showing me the way

 

[Cringer]

Well, I did a bit a bit of research on ProTop web page and I saw that your experience is precious. So really thank you to take time to explain my mistakes and showing me the way

Tom is the authority when it comes to OE DBA stuff. Just don't tell Gus I said that.

 

[BobyIsProgress]

Tom is the authority when it comes to OE DBA stuff. Just don't tell Gus I said that.

I will keep it for me

 

[RealHeavyDude]

Not only is he the authority - I would call him an ambassador given his willingness to share his knowledge and experience.

 

[TomBascom]

Careful guys -- my wife already thinks I have a swelled head.

Plenty of people know lots of things that I don't know. (And I've been wrong on occasion too.) I do like to share - I find that explaining things helps me to understand them better.

 

[BobyIsProgress]

Careful guys -- my wife already thinks I have a swelled head.

Plenty of people know lots of things that I don't know. (And I've been wrong on occasion too.) I do like to share - I find that explaining things helps me to understand them better.

And I really thank you for that.

 

[ForEachInvoiceDelete]

Progress is pretty resilient in terms of record reads and performance.

We just had a warning from our MDBA(Bravepoint) as we are averaging 30 billion record reads per day but application performance is alright.

Praise the lord for a good database to mask crappy code.

 

[BobyIsProgress]

So I will start ProTop tomorrow if I can install it with the IT team.

In addition is it possible to know how many reading / programme ?

 

[TomBascom]

I think that you are asking about the data per connection that shows current program name and line#?

This is available if you enable the "client statement cache". There are two ways to do that when using ProTop, the * command will turn it on globally (use with caution) and the # command will allow you to specify a specific user.

Or you can enable it directly via PROMON.

Caution: in some situations (mostly old releases) there are memory leaks, permissions problems and potential conflicts that can cause issues if you use this feature carelessly. (To be clear, these are Progress issues -- not ProTop issues.) It can also have a noticeable impact on performance -- especially client/server performance. I do not generally suggest that people turn it on for all users and leave it on permanently (although I know of people who do that successfully). I recommend that you use it when you are looking for a specific problem.

I also suggest that when you turn it on you should almost always use the "single (top of stack)" option. You probably don't really need the full stack and enabling the full stack greatly increases the amount of data collected. Which amplifies the chances for the potential problems described above.