Question Really High Index Reads.

[ron]

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:


The fact that each index only includes one field is, of course, extremely suspicious.

Comments very much appreciated.

Ron.

 

[Rob Fitzpatrick]

Can anyone explain what Progress might be doing to cause a situation like this?

For example:

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]

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]

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]

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.

 

[TheMadDBA]

That is one good argument for getting a cost based optimizer in the 4GL