Question Really High Index Reads.

ron

Member
OE 10.1C on Solaris. (Heading for 11.6 Q1 next year.)

I have collected two months of tableStat in indexStat into an Excel report to analyse. Several "interesting" things for me to chase - but one thing stands-out as being quite strange; something I haven't seen before.

The VST details are collected hourly, 24x7. During peak times one table (nm-hdr) has around 400,000 reads, 15,000 creates, nil deletes and 30,000 updates. That looks OK. The table has 7 indexes and two of them have what appears to me as being a ridiculous number of reads. Index "status-ix" has about 200,000,000 reads and index "type-ix" has about 200,000,000 reads also. They are never identical - but they are always within about 1% of each other.

To my mind there is a major problem here. Can anyone explain what Progress might be doing to cause a situation like this? Is it possible, for example, that a query is specifying keys that don't match an index - but somehow Progress is "joining" two indexes in some way?

Here is what the index definitions look like:

upload_2016-10-27_18-5-4.png
The fact that each index only includes one field is, of course, extremely suspicious.

Comments very much appreciated.

Ron.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Can anyone explain what Progress might be doing to cause a situation like this?
For example:
Code:
for each nm-hdr no-lock where nm-subtype = 'foo' use-index status-ix:
  /* do stuff */
end.
for each nm-hdr no-lock where nm-current-status = 'bar' use-index type-ix:
  /* do other stuff */
end.
That would do a pair of table scans. Same would apply of course if there were no WHERE clause at all. There are lots of ways to write a bad query.

How many records are there in the table?
How long a period of time is "peak times"?

Some approaches you could take:
  • narrow down your CRUD measurements to smaller periods of time to potentially eliminate some programs or business processes from consideration;
  • look at the _UserIO stats to see who has the outlying stats; you're likely to see one client or a few with much bigger numbers than everyone else;
  • query _UserTableStat and _UserIndexStat to see which users in particular are reading via status-ix and type-ix;
  • compile your code with XREF and look for WHOLE INDEX scans on nm-hdr (requires source);
  • once you nail down which users are the culprits (via the above steps), add -clientlog "file.log" -logentrytypes QryInfo:3,4GLTrace:3 to the clients' startup parameters. You'll be able to see when they query nm-hdr and which programs they ran at the time.
Is it possible, for example, that a query is specifying keys that don't match an index - but somehow Progress is "joining" two indexes in some way?
In some cases the ABL query engine can resolve a query with two indexes. For example, if you have a query predicate with an OR and each side of the OR has an equality match or range match on the leading component(s) of non-unique indexes.
 

TheMadDBA

Active Member
Just a small add to what Rob said (all great advice).. It is possible you have some other queries that are causing the excessive reads without showing a WHOLE-INDEX in the XREF.

You could have a FOR EACH with equality matches on status and/or type but range matches on other columns. So the compiler decides to use those indexes but not others. Always a peril with using multiple indexes because the 4GL only considers equality matches when using multiple indexes.
 

ron

Member
Thanks a lot. I'm getting the developers to review this urgently. I suspect that whoever originally set-up this table was "clueless" about how indexes work. I feel sure that two (maybe more) of the indexes need a few more keys added to them. I'll let you know what they find!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The designer might have too much faith in OpenEdge's ability to use multiple indexes to resolve a query. Wouldn't be the first time I've seen that.
 
Top