Karol Bieniaszewski wrote:
I use FB2.5.3.26605 Super Server 64bit on Windows Server 2008 R2 Ent. I create app for performance monitor and all is ok for stats but not when data are retrived in packets. e.g if i use flamerobin to get data from table it get first 300 records then next records and next.
In MON$IO_STATS - MON$PAGE_FETCHES field i see reads 120637 pages but my whole test database have only 6685 pages. Is something wrong with counters when status of statement is 2 in field MON$STATE?
Tim Ward answers:
For x records it's usual to see 2x fetches. If this creeps up to 3x fetches and your application slows down by several orders of magnitude it may be that you have fragmented records, with each record split across two or more pages.
Ann W. Harrison adds:
The reason for 2x fetches is that Firebird identifies a record through a slot in a vector on the data page - both the page and the slot are part of the RDB$DB_KEY. The page is identified indirectly through an index of pages for the table. The values in the vector on the data page are the length of the stored record and the offset on the page of the first byte of the record (Going through a record locator vector means that records can move within a page without affecting anything off the page, so it's very cheap to repack a page to reclaim fragments of space). So once a data page is in memory, Firebird reads the records on it first by looking up the offset and length, then by reading that length from that offset. Even for a natural scan, Firebird has some overhead fetches - first to find the right page in the index of data pages for the table, then to access that page to read page numbers. Indexed access requires more fetches...
Fragmented records increase the number of fetches - and possibly the much more expensive physical reads - as do old record versions.