Question about VST Table/Index I/O Statistics.

ron

Member
Linux 3.10.0-1160.2.2.el7.x86_64 x86_64
OE 11.7.4 Enterprise

Hi, We have:

-tablerangesize 350
-indexrangesize 550

... and those settings comfortably accommodate the tables and indexes in our "user" system. By default, of course, the -baseindex and -basetable are both zero.

As things are we make quite a lot of use of the table and index statistics for analysing activity in our database. But, of course, there is also quite a bit of "other" activity -- especially with respect to Auditing. I see that the table numbers for Auditing are around the -300 mark. There are also many system tables between -1 and -299 which, I presume, are Schema tables. There are also tables way up in the -16000 range which I presume are VSTs.

I wish to collect the table and index statistics for Auditing. I have never set -basetable or -baseindex to negative values before. Is there anything "special" that I need to know before I delve into this new realm?

By the way (in case you ask) -- the I/O with respect to Auditing is "considerable". I am aware of ways to address that by hiving-off the reporting part of it to a second database. That is something we will do, but we can't do it in the short term.

Ron.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
By default, of course, the -baseindex and -basetable are both zero.
Close; they both default to 1. ;)
As things are we make quite a lot us use of the table and index statistics for analysing activity in our database.
Good!
There are also many system tables between -1 and -299 which, I presume, are Schema tables.
Some are, some aren't. A good way to understand what these tables are is to look at their categories (available in 11.0+). Example:
Code:
for each dictdb._file no-lock use-index _file-number:
  display
    _file._file-number
    _file._file-name
    _file._category.
end.
Every system table has a defined category.
There are also tables way up in the -16000 range which I presume are VSTs.
Correct. VSTs have _file-number < -16384.
Is there anything "special" that I need to know before I delve into this new realm?
Not really. In releases prior to 11.7, there was a bug in VST indexing that you had to be aware of when querying. But for you it will be straightforward.

Obviously, when setting negative values for -basetable and -baseindex, you will need to increase the values of -tablerangesize and -indexrangesize by corresponding amounts in order to ensure all your application objects remain within the windows of CRUD statistics coverage.

ProTop (Download ProTop - White Star Software - free download!) can help you with that. Use the T command to provide options for different range sizes based on your schema. (Disclaimer: I work on this product.)

Example:
Code:
┌─────────────────────────────── Table and Index Range Information ────────────────────────────────┐
│                                                                                                  │
│                                                                                                  │
│                                                                                                  │
│                          -basetable:       0                           -baseindex:       0       │
│                     -tablerangesize:      50                      -indexrangesize:      50       │
│                                                                                                  │
│                Highest Stats Table#:      49                 Highest Stats Index#:      49       │
│             Lowest Monitored Table#:       1              Lowest Monitored Index#:       0       │
│            Highest Monitored Table#:       9             Highest Monitored Index#:      32       │
│                                                                                                  │
│                                   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:       0       │
│                                                                                                  │
│                  Excess Table Range:      40                   Excess Index Range:      18       │
│                                                                                                  │
│              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 pseudo tables such       │
│    as VSTs and SQL views as these do not have any CRUD statistics associated with them.          │
│                                                                                                  │
│  Suggested settings can be found in:  /home/rob/pt/3.14159x/tmp/sp.range.pf                      │
│                                                                                                  │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘
I am aware of ways to address that by hiving-off the reporting part of it to a second database. That is something we will do, but we can't do it in the short term.
That's good, and not just because of the I/O impact of writing and reading the data. Heavy auditing can also cause significant database growth. Sometimes the audit data grows to be larger than the application data.

It is a best practice to keep only short-term audit data in a production database, and archive it regularly into an audit database. If there are multiple application databases being audited, they don't each have to have their own audit archive databases. Their audit data can be consolidated into a single archive database.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Kudos to you @ron for doing this. I'm a proponent of looking at system object logical I/O. In looking at your audit I/O, you'll also be able to see all of your meta-schema I/O, if your statistics parameters are set correctly. It can be surprising how much there is for some applications.

One thing to note is that although all system tables have negative numbers, the same is not true of system indexes. For reasons that are not clear to me, Progress assigned positive index numbers to some of their system indexes. Some are small (0-7) and some are large (around 1,000). (This includes some audit indexes, for _Db-Detail and _Client-Session.) That's why the UI sample above, taken from a sports database, shows a highest index number of 1093.

You can see them listed together like this:
Code:
for each dictdb._file no-lock,
  each dictdb._index no-lock of _file
  break by _file._file-number by _index._idx-num:
  
  display
    _file._file-number when first-of( _file._file-number )
    _file._file-name   when first-of( _file._file-number )
    _file._category    when first-of( _file._file-number )
    _index._idx-num
    _index._index-number.
end.
 

ron

Member
Rob -- thank you very much for your really helpful replies!

Yes, I had noticed that many system indexes are positive ... which was "odd".

I was not aware of the _category field ... that will be quite helpful.

In the past I have found the table/index activity information very useful for analysing how a system is performing and occasionally it will identify some surprising anomalies. One that I discovered a few years ago was that a relatively "small and inconspicuous" table had about 500,000 index reads on each of two indexes for every one table read! I was astonished. Someone had created the table and set-up the indexes without having any idea of how indexes work. It was very easy to fix once found.

At present I am anxious to get the Audit tables analysed, I think they are generating quite a lot of activity. Making changes regarding Auditing will be difficult, unfortunately, because we have an application suite that heavily accesses data in the Audit tables. It is using Auditing to fulfil a task that I think should have been done with CDC. :(

Ron.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I was not aware of the _category field ... that will be quite helpful.
The _file._category field can also be updated. If you would like your schema to be more self-documenting, you can define categories for your application tables. They will be preserved when you export the schema to a .df.

Code:
ADD TABLE "foo"
  AREA "data"
  DUMP-NAME "foo"
  CATEGORY "my category"
 

TomBascom

Curmudgeon
It is using Auditing to fulfil a task that I think should have been done with CDC. :(

Relatively speaking CDC is a bit of a new kid on the block so it wouldn't be terribly surprising if someone used auditing because it was the most appropriate tool back when they did whatever they did.
 
Top