How to get record read stat from client memory

[RP_wpb]

Hello Everybody:
OpenEdge Ver 10.2b.

Is there anyway to get record read stat just performed on client memory.
from what I understand, VSTs/Promonitor provide CRUD info based on buffer pool and disk access.
so say, if my client pgm is reading a record 10 times (with no-lock and -rereadnolock not enabled) everytime progress will not fetch the same record from buffer pool , right? so the VSTs might show '1' under reads but what I am looking for is how to see this '10'. is this possible?
Any suggestion would be helpful.
thanks in advance.

 

[GregTomkins]

If you 'find first x' inside a loop of 10, the VST _tablestat-read number will increase by 10. I just verified this quickly. The fact that 9 of those reads were most likely from memory doesn't seem to matter.

A flaw with this, however, is that _tablestat is across all users. I'm not aware of a way to look at individual table statistics on a per-user basis. If someone else knows, I'd be interested to learn that.

A curious thing I noticed is that if the record you're looking for doesn't exist, the number doesn't increase. I guess this is OK. Debatable, though. And probably irrelevant for most purposes.

Also, I'm always suspicious of how the VST numbers correlate to what one sees in promon ... you'd hope they come from exactly the same place, but, well, I hope a lot of things . In any case, I'm sure Curmudgeon will know the definitive answer!

 

[Rob Fitzpatrick]

All database reads go through the buffer pools (Primary or Alternate). So the stats-related VSTs will reflect all the logical I/O done by clients.

As Greg mentions, _TableStat and _IndexStat show cumulative database-wide CRUD stats from the time that (a) the database was opened or (b) the stats were zeroed out, either programmatically or via proutil -C zerostats.

Per-user CRUD stats can be obtained by similar queries on the _UserTableStat/_UserIndexStat VSTs that were added in 10.1B. How to query those tables efficiently is another matter. Downloading and reading the code in ProTop is a good place to start. It certainly isn't covered in the docs.

If you're not seeing what you expect in the VSTs, can you provide specifics on the scenario, i.e. your client code and VST-reading code?

 

[TheMadDBA]

I believe the _UserTableStat will still be correct because the server process still has to retrieve the record in order to ship the ROWIDs.

Is there a practical application for this or are you just curious? If you are trying to solve practical problems you are probably better off using the Profiler to find out where the issues are.

Or you can download my session tracing tools ( http://www.oehive.org/project/SessionTrace ) which combines Profiling and User VSTs in one.

 

[GregTomkins]

I kind of glossed over the OP's comment about -rereadnolock. That might be important. We always use -rereadnolock (I think) and in any case I was trying this from a simple self-service client. I'm fuzzy on how VST's and remote clients interrelate. Details, details.

TIL about _UserTableStat, aha there it is, thanks Rob Fitzpatrick!

 

[jdpjamesp]

How to query those tables efficiently is another matter.

It so happens I've written a guide... http://www.jdpalmer.co.uk/index.php?id=user-table-stats

 

[Rob Fitzpatrick]

It so happens I've written a guide...

Good stuff. Setting -tablerangesize/-indexrangesize correctly is key, so you don't miss any CRUD stats. Here is a simple check to get the minimums:

/* display the highest application object numbers in the current database */

/* this is the minimum acceptable value for -tablerangesize */
find last dictdb._file no-lock where _file._tbl-type = "T" use-index _file-number no-error.                

/* this is the minimum acceptable value for -indexrangesize */
find last dictdb._index no-lock where not _index._index-name begins "_" use-index _index-number no-error.  

display
  "Logical database   : " ldbname( "dictdb" )                      skip
  "Highest app table #: " _file._file-number format ">>>9"         skip
  "Highest app index #: " _index._idx-num    format ">>>9"         skip(1)
  "Note: these values are based on the assumption that -basetable" skip
  "      and -baseindex are both set to the default value of 1."
with no-box no-labels
.

 

[Rob Fitzpatrick]

I kind of glossed over the OP's comment about -rereadnolock. That might be important.

Good point. We use -rereadnolock on some AppServer and WebSpeed agents but I confess I haven't tested the effect of using that parameter on CRUD stats shown in the VSTs.

 

[RP_wpb]

Thanks Rob.
But why would it go every time through the buffer pool on server? I do understand if I force an exclusive lock,it has go and fetch the record from server but I am just reading with no-lock.
The background is, we suspect there are lot of pgms in our code base which are repeatedly reading same records and its sprinkled across the code base. So as a first step thought, if somehow we can get the stat on how much client read from memory as compared to reads from buffer pool this will give some info.

All database reads go through the buffer pools (Primary or Alternate). So the stats-related VSTs will reflect all the logical I/O done by clients.

As Greg mentions, _TableStat and _IndexStat show cumulative database-wide CRUD stats from the time that (a) the database was opened or (b) the stats were zeroed out, either programmatically or via proutil -C zerostats.

Per-user CRUD stats can be obtained by similar queries on the _UserTableStat/_UserIndexStat VSTs that were added in 10.1B. How to query those tables efficiently is another matter. Downloading and reading the code in ProTop is a good place to start. It certainly isn't covered in the docs.

If you're not seeing what you expect in the VSTs, can you provide specifics on the scenario, i.e. your client code and VST-reading code?

 

[GregTomkins]

Here's a suggestion that might be totally useless: if you, say, suspect a lot of reads on the FOO table, you could add a session trigger to the top of the call stack 'on read of FOO'. Then in the trigger, you could log the details of the access. I'm not sure how much detail you can get (eg. you probably want the .P name and line# of the code that generated the read). But this would (I am pretty sure) catch 100% of the reads regardless of how they were resolved. One catch: you need to set it up table-by table and user-by-user. Another: it's not really practical for ongoing production use as it would have some overhead for sure. Another: it won't catch CAN-FIND's.

 

[TomBascom]

Thanks Rob.
But why would it go every time through the buffer pool on server? I do understand if I force an exclusive lock,it has go and fetch the record from server but I am just reading with no-lock.

The buffer pool has nothing to do with locking.

It exists to cache data in memory in order to avoid disk IO.

The background is, we suspect there are lot of pgms in our code base which are repeatedly reading same records and its sprinkled across the code base. So as a first step thought, if somehow we can get the stat on how much client read from memory as compared to reads from buffer pool this will give some info.

The VSTs only report data from the servers point of view. They have no knowledge of anything happening on the client.

Reread Nolock (-rereadnolock)

Use Reread Nolock (-rereadnolock) to tell the AVM how to decide between multiple copies of a cached record that were read from the database using NO-LOCK.

So first you need multiple copies...

The AVM stores records read from the database in internal buffers that are linked to the record buffers of your ABL application. When two internal buffers for the same record exist, the AVM decides which to keep and use by rules based on the locking level and age of each copy. This parameter only affects how the decision is made when both of the following conditions are true:

A record is read from the database with NO-LOCK.

The AVM finds an older NO-LOCK copy of that same record already in memory.

This seems fairly relevant:

This parameter causes no extra database activity; it simply determines whether to keep the older or newer of the copies already in memory.

More details:

You use this parameter to resolve client‑server currency conflicts. You also use it to resolve server‑to‑server currency conflicts by using it as an AppServer startup parameter through the Progress Explorer or by setting the srvrStartupParam property in the ubroker.properties file for the appropriate AppServer. When applications use record buffers with a large scope, such as default buffers scoped to the main block of long life persistent procedures as you might have on an AppServer, using -rereadnolock is strongly advised.

Remember the following when using -rereadnolock:

It has no affect on records that are retrieved through RECID or ROWID. In that case, the AVM will not re‑read the record. Instead, it uses the copy of the record already stored in the buffer. If you need the most current version of the record, use the RELEASE statement on all buffers that contain a copy of the record before reading the record, or use the FIND CURRENT or GET CURRENT statement to re‑read the record.

It has no affect on the behavior of the query cache used for a query with NO-LOCK that is specified through the CACHE phrase of the DEFINE QUERY statement. To force the AVM to always re‑read the record, set the cache size to zero (0). However, this may significantly degrade performance if the database is accessed across a network. Set the cache size to zero only when it is critical to retrieve the most current version of a record.

It has no affect on the behavior of the prefetch cache that is used by default when retrieving records NO-LOCK across the network. By default, when executing a CAN-FIND function or a FIND, FOR, or OPEN QUERY statement on a database that is accessed across a network, the AVM fetches several records at a time and stores them in a prefetch cache. The AVM will only send a request to the database server to fetch more records if the requested record is not in the current prefetch cache. If the record is in the current cache, the AVM will not read a new copy of that record even if -rereadnolock is set. To eliminate this cache so that the most current version of the record is always read, use the NO-PREFETCH keyword in the appropriate statements. However, using the NO-PREFETCH keyword may significantly degrade performance. Set NO-PREFETCH only if it is critical to retrieve the most current version of a record.

 

[TomBascom]

... we suspect there are lot of pgms in our code base which are repeatedly reading same records and its sprinkled across the code base.

You are almost certainly correct. Almost all applications have some variation on that behavior and it is at the root of, or is a major indicator of, many serious performance problems.

It is also very, very obvious from the db server perspective and not an invisible client-side issue.

Set *rangesize appropriately and download ProTop. You'll find out all sorts of interesting things about how your application accesses data.

For bonus observations enable the client statement cache (this will tell you what line# of what procedure is driving the activity).

 

[TheMadDBA]

I think it would be clearer if the documentation also stated that it causes no difference in read activity.. as in no change in database IO required.