Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)

[Thomas S. <ts...@ib...>]

>>>       In your example we see 7149 physical reads and 214192 times this
>>> pages was referenced by the engine. To read a record engine needs to
>>> access pointer page and (at least one) data page. You have ~100K records
>> so we can explain ~200K fetches.
>>
>> Ok, but is there a way then to tell how many pages have been fetched from
>> the cache as the number above for fetched is more likely "referenced" and
>> not real number of pages fetched from memory?
>>
>> I guess the same applies to MON$IO_STATS.MON$PAGE_FETCHES? If so,
>> isn't comparing MON$PAGE_READS with MON$PAGE_FETCHES a bit
>> misleading if one wants to check to possibly increase the database page
>> buffers?
>
> Depending on the size of a query and the nature of the data, it is possible that data from a related table accessed via a FK would be:
> - loaded into cache (count as 1 disk read),
> - be referenced 4 subsequent times (count as 4 fetches) and
> - then be dropped from cache (due to LRU algorithm)
> - to be re-loaded into cache at a later point in the query execution (count as ANOTHER disk read)
> - be referenced 4 more subsequent times (count as 4 fetches)
>
> The fact that (in the above example) there are 20% disk reads (2 out of 10 total operations) could suggest that cache should be increased.

I also think a pretty stable number of reads for the same statement for 
sub-sequent execution indicates that a higher cache might help as well.

> The ideal statistic that would help in knowing whether the cache needs to be increased would be "disk re-reads", but that would require that the engine track a list of the RDB$Key of every row accessed in a query to make the distinction between "read" (initial load) and "re-read" (re-load into cache due to LRU).  That list would likely require more overhead than really its worth.
>
> So, they are not "misleading", they are just "not ideal".

Yep. Vlad's "referenced" term is reasonable for the semantic behind that 
number.

Regards,
Thomas