Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Paul, On Wednesday 16 November 2011 at 06:04 Dmitry Yemanov wrote: Also, some Windows versions are suspected in giving the file system cache too high priority thus possibly swapping out the pages of the process working set, so a largish internal page cache could prove itself to be a bad idea in this case. Do you know which windows versions? It affects all x64 deployments of Win7, Win2003 and Win2008 (non-R2). In the case of Win2008R2 x64 systems, the new Dynamic Cache Service will limit file cache to 90% of physical RAM by default. This setting may not be appropriate and need adjustment if the amount of application/service memory (not working memory) required for the server exceeds 10%. In our case, for servers with 6GB-9GB we have set the file cache to 30-50% of memory due to the unpredictable number of classic FB engine processes that can be created (can vary from 75 to150/200), even with FB cache set to 75 pages (@ 8KB), and other application memory requirements. Naturally, as the amount of RAM increases we can increase the file cache, but on a case by case basis. Sean -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-novd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Hi Vlad, as the subject says. What's the unit for the resulting values for number of reads, writes, fetches, marks? Just want to be sure if a particular result makes sense here. ;-) Same as for isql's statistics - number of operations. Note, we have no operations on group of pages, so, for ex. N writes means N times write and every write is length of one page. So, the numbers I see is number of pages? I have the following trace output for a select count(*) on a table with ~100.000 records. --- select count(*) from accommodation ^^^ PLAN (ACCOMMODATION NATURAL) 1 records fetched 81 ms, 7149 read(s), 214192 fetch(es) If it is pages, then this would mean ~ 800MB (4K page size) fetched from the cache? Does this make sense? Thanks again, Thomas Hope this helps, Vlad -- 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 -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Hi Vlad, as the subject says. What's the unit for the resulting values for number of reads, writes, fetches, marks? Just want to be sure if a particular result makes sense here. ;-) Same as for isql's statistics - number of operations. Note, we have no operations on group of pages, so, for ex. N writes means N times write and every write is length of one page. So, the numbers I see is number of pages? Yes I have the following trace output for a select count(*) on a table with ~100.000 records. --- select count(*) from accommodation ^^^ PLAN (ACCOMMODATION NATURAL) 1 records fetched 81 ms, 7149 read(s), 214192 fetch(es) If it is pages, then this would mean ~ 800MB (4K page size) fetched from the cache? Does this make sense? Fetch is not a physical read. This is more like reference. When Firebird need access to page buffer (to get record, for ex.) it asks page cache to fetch page buffer. If this buffer is not present at page cache, it will be read from disk first. 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. Regards, Vlad -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Hi Vlad, as the subject says. What's the unit for the resulting values for number of reads, writes, fetches, marks? Just want to be sure if a particular result makes sense here. ;-) Same as for isql's statistics - number of operations. Note, we have no operations on group of pages, so, for ex. N writes means N times write and every write is length of one page. So, the numbers I see is number of pages? Yes I have the following trace output for a select count(*) on a table with ~100.000 records. --- select count(*) from accommodation ^^^ PLAN (ACCOMMODATION NATURAL) 1 records fetched 81 ms, 7149 read(s), 214192 fetch(es) If it is pages, then this would mean ~ 800MB (4K page size) fetched from the cache? Does this make sense? Fetch is not a physical read. I know. I said fetched from the cache. This is more like reference. When Firebird need access to page buffer (to get record, for ex.) it asks page cache to fetch page buffer. If this buffer is not present at page cache, it will be read from disk first. 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? Thanks again, 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
- Original Message - From: Thomas Steinmaurer t...@iblogmanager.com To: For discussion among Firebird Developers firebird-devel@lists.sourceforge.net Sent: Tuesday, November 15, 2011 12:11 AM Subject: Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...) Hi Vlad, as the subject says. What's the unit for the resulting values for number of reads, writes, fetches, marks? Just want to be sure if a particular result makes sense here. ;-) Same as for isql's statistics - number of operations. Note, we have no operations on group of pages, so, for ex. N writes means N times write and every write is length of one page. So, the numbers I see is number of pages? Yes I have the following trace output for a select count(*) on a table with ~100.000 records. --- select count(*) from accommodation ^^^ PLAN (ACCOMMODATION NATURAL) 1 records fetched 81 ms, 7149 read(s), 214192 fetch(es) If it is pages, then this would mean ~ 800MB (4K page size) fetched from the cache? Does this make sense? Fetch is not a physical read. I know. I said fetched from the cache. This is more like reference. When Firebird need access to page buffer (to get record, for ex.) it asks page cache to fetch page buffer. If this buffer is not present at page cache, it will be read from disk first. 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? What do you understand under fetched from memory ? There is no operations like memmove or so on. Fetch is just : - find buffer by given page number - lock it - return it address Regards, Vlad -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
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. 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. Sean -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
On Mon, Nov 14, 2011 at 5:11 PM, Thomas Steinmaurer t...@iblogmanager.comwrote: 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? Pages aren't fetched from cache. Once a page is in cache, data is fetched from it. Sometimes the data is a record, to be expanded into a record buffer, sometimes it's an index node, sometimes it's a page number from an offset on a pointer page, or the state of a transaction from a TIP, or ... 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? It's more misinterpreted than misleading. You would never increase the number of pages in the cache to reduce the number of fetches. In an ideal world, the number of fetches would be enormous and the number of reads would be *infinitesimal, meaning that all most every request was resolved from cache and therefor the cache is big enough. The only ways (I can think of at the moment) to reduce the number of fetches are to do less work, or work more efficiently (e.g. don't do a count (*) on a big table).* Cheers, Ann -- RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Ann, On Mon, Nov 14, 2011 at 4:40 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: So, the numbers I see is number of pages? For reads and writes. Marks and Fetches are recorded each time a page is referenced in cache. So if you read 20 records on a page, you'll probably get 40 fetches - one for each reference to the page/line index and one for each record actually read. Probably at least one to validate the page header, and possibly others. Good luck (not that Thomas needs it) I do, I do. ;-) Thanks for jumping in. 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
On Mon, Nov 14, 2011 at 5:11 PM, Thomas Steinmaurer t...@iblogmanager.com mailto:t...@iblogmanager.com wrote: 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? Pages aren't fetched from cache. Once a page is in cache, data is fetched from it. Sometimes the data is a record, to be expanded into a record buffer, sometimes it's an index node, sometimes it's a page number from an offset on a pointer page, or the state of a transaction from a TIP, or ... 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? It's more misinterpreted than misleading. You would never increase the number of pages in the cache to reduce the number of fetches. I would expect to reduce the number of (physical) reads when increasing the cache. It would be also interesting if reads exposed in various places (isql, monitoring tables, trace api) are pure physical reads from disk and/or fetched from the OS file system cache. I guess, it could be also from the file system cache, because I've seen situations where execution time is larger for the same number of physical reads for a newly established connection, which then decreases for sub-sequent execution of the same statement with an equal number of reads. Regards, Thomas In an ideal world, the number of fetches would be enormous and the number of reads would be /infinitesimal, meaning that all most every request was resolved from cache and therefor the cache is big enough. The only ways (I can think of at the moment) to reduce the number of fetches are to do less work, or work more efficiently (e.g. don't do a count (*) on a big table)./ Cheers, Ann -- 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 -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
It would be also interesting if reads exposed in various places (isql, monitoring tables, trace api) are pure physical reads from disk and/or fetched from the OS file system cache. I guess, it could be also from the file system cache, because I've seen situations where execution time is larger for the same number of physical reads for a newly established connection, which then decreases for sub-sequent execution of the same statement with an equal number of reads. There is no way to know if OS resolved read request from cache or from disk. Even if application is used so called direct IO read request could be resolved from file cache - if some other application reads same file using file cache few moments before. Regards, Vlad -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
It would be also interesting if reads exposed in various places (isql, monitoring tables, trace api) are pure physical reads from disk and/or fetched from the OS file system cache. I guess, it could be also from the file system cache, because I've seen situations where execution time is larger for the same number of physical reads for a newly established connection, which then decreases for sub-sequent execution of the same statement with an equal number of reads. There is no way to know if OS resolved read request from cache or from disk. Thought so that Firebird can't differ here. Just speaking out loud that if execution time for the same statement is a fraction with the same number of physical reads, then data is probably coming from the file system cache instead of the disk. A perfect use case for the Trace API to detect such things. Even if application is used so called direct IO read request could be resolved from file cache - if some other application reads same file using file cache few moments before. Yup. Thanks, 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
Vlad, PS Snapshot (concurrency) transaction guarantees that once read record could be read again and will be the same. So, in theory, we can just re-read same data page when looking for next record. Why in theory? Because i want to be careful and don't want to make too early conclusions :) This (raw) idea should be considered from the all possible points of view and, ideally, we need to find a way to make same optimisation for every kind of transaction. I don't see how the DP could change for the purposes of the next record for Snapshot transactions. Yes, the additional versions of the next record could be posted to the DP in the cache page (marking changes or deletions) but the existence of the record on the DP can't change within the snapshot. Only if the new record changes have overflowed to other DPs would other pages need to be loaded. It would seem that a significant performance improvement could be yielded by changing the current functionality. It could be not so significant, especially with shared cache in mind :) Regards, Vlad -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
PS Snapshot (concurrency) transaction guarantees that once read record could be read again and will be the same. So, in theory, we can just re-read same data page when looking for next record. Why in theory? Because i want to be careful and don't want to make too early conclusions :) This (raw) idea should be considered from the all possible points of view and, ideally, we need to find a way to make same optimisation for every kind of transaction. I would argue that not every transaction can have the same optimization. We may need to apply optimizations on a common cause basis, which would allow for some modes/transactions to use Optimization A vs Optimization B vs. no optimization. Handling a SELECT for a Snapshot transaction is completely different that an UPDATE statement for a read-committed transaction. It would seem that a significant performance improvement could be yielded by changing the current functionality. It could be not so significant, especially with shared cache in mind :) Not needing to access the cache at all, therefore by extension no page coordination, would have to provide a benefit. Sean -- 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
Re: [Firebird-devel] Trace API - What's the unit for number of (reads |fetches ...)
15.11.2011 2:11, Thomas Steinmaurer wrote: 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? I bet you're thinking about the cache hit ratio which is commonly used to estimate the cache efficiency. It's usually calculated as (1 - physical reads / logical reads). In the Firebird case (logical reads = fetches and they're mostly record-level) this metrics always gets overestimated and thus practically useless. In order to have a workaround, I was thinking about an additional counter adjacent fetches - number of fetches belonging to the same page which is subsequently accessed by the same retrieval, i.e. kinda redundant fetches that turn the supposedly page level counter into the record level one. So the formula could be changed to something like: 1 - reads / (total fetches - adjacent fetches) thus resulting to a more realistic estimation. The fact that data page fetches are intermixed with pointer page fetches during the retrieval makes the calculation not so trivial but it this still looks possible. The question is whether there's actual demand from our users / DBAs. I know this is a quite popular performance tuning metrics in other databases but so far nobody complained on the our side ;-) Dmitry -- 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
