Firebird uses a page cache to hold pages in memory. It is much quicker to retrieve pages from RAM than to go out onto the disc system and read them physically each time they are required. The following description of how Firebird uses its cache is taken from a posting by Ann Harrison on the Firebird Support mailing list.

The posting was in response to a query asking if there is a way to reduce the memory cache without giving up too much performance? This was itself in respect of a system that was taking a long time to wake up from hibernation and the cause was thought to be the time required to load back all the cache pages from disc before the first query (after hibernation) could be processed. The cause was confirmed and the DBA responsible for the database asked, on the list, how he could reduce the buffer cache as much as possible but still have a responsive system.

Ann has given her permission for this posting to be documented formally as part of the Firebird documentation project.

Everything in the database is organised as fixed sized pages of known structure - there are nine different types of pages. The page cache is the intermediate between the "working" parts of the database and the disk.

When initially started, Firebird reads the database header page, then the first pointer page of a system table called RDB$PAGES from which it then knows where to find the pointer pages, amongst other things, for the system and user tables within the database.

As soon as the application accesses the database and begins a transaction, Firebird reads the pointer pages telling it where to find the data pages for the tables involved in the application’s transactions. All the pages go into the cache and stay there until the cache is completely full. When there’s no place to put the next page, Firebird releases the least recently used page - not the first one read, but the one that has been referenced least recently and has not been modified.

When a transaction commits - and at a few other times - Firebird writes the pages changed by that transaction to disk, but does not release them from the cache - this helps avoid an extra read if the page is required again soon.

Over time and with luck, you end up with the most frequently changed and referenced pages resident in the cache - those would be transaction inventory pages, pointer pages for active tables, the header page, the top levels of indexes, etc. Data and lower level index pages swap in and out as required - but the cache will always be full.

You can use the MON$ tables (in Firebird 2.1 onwards, especially MON$IO_STATS) to figure out how well your cache is being used. They will report on the number of Fetches vs. Reads which is the number of times pages were accessed versus the number of times they had to be read from disk. When the number of reads goes up dramatically, you’ve reduced the cache too much.

The MON$ tables will also give you the number of Marks vs. Writes, which tells you the number of times pages are changed versus the number of times the pages were written to disk. When you see the disk writes go up, you’ve probably reduced the cache too much.

As mentioned above, the table MON$IO_STATS can be used to help you determine how well your buffer cache is performing. The table has the following structure:

To inspect the current statistics for the database as a whole, we would use the following query in isql:

The results of the above show that:

We can assume, therefore, that although a small number of pages have been read into the cache, there is nothing we can do to avoid that. When the database is started up the cache is empty, when applications connect and access the database, various pages must be read and the cache must be filled, so physical reads will be a necessity. In this example, it appears that once pages are in the cache they are being accessed quite frequently given that there have been approximately 103 cache reads for every physical read.

Of the 529 updated pages - and these are system as well as user pages - 526 have been written back to the physical discs but three still remain in cache, as yet, unwritten.

The results shown above show the performance of the cache over the life of the database so far. We can narrow this down to our current attachments by modifying the query to select those rows where the MON$STAT_GROUP is 1.

Interpretation of the above statistics is exactly the same as for the database as a whole.

We can further diagnose the statistics by individual transactions, as follows:

And, by individual statements:

Finally, it is possible - and probably most useful - to determine the statistics for your own session. You can find your attachment id from CURRENT_CONNECTION and use that in a query that joins with MON$IO_STATS using the MON$STAT_ID column.

The exact file history is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the “License”);you may only use this Documentation if you comply with the terms of this License.Copies of the License are available at https://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and https://www.firebirdsql.org/manual/pdl.html (HTML).

The Original Documentation is titled Firebird Database Cache Buffer.

The Initial Writer of the Original Documentation is: Norman Dunbar using data supplied by Ann Harrison.

Copyright © 2010–2020.All Rights Reserved.Initial Writer contact: NormanDunbar at users dot sourceforge dot net.

Contributor: Martin Köditz – see document history.

Portions created by Martin Köditz are Copyright © 2020.All Rights Reserved.Contributor contact: martin dot koeditz at it dash syn dot de.