>>> 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 ------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
