Subject Re: [firebird-support] Performance improvements for an embedded database after hibernation
Author Ann W. Harrison
Alec Swan wrote:
> When the system hibernates it writes its memory state to disk. When it comes
> back from hibernation the memory state is restored from disk. So, my guess
> was that the slow-down after hibernation was caused by reading memory state
> from disk. Moreover, the slow-down was only observed on regular hard drives
> and not on solid-state drives.

Yes, that sounds like the cost of reading the memory occupied by the
> Since Embedded server is configured similar to SuperServer, it uses
> 2048-page DB cache by default (right?). I reduced DefaultDbCachePages to 500
> and noticed a 3-fold improvement in the first query performance after
> hibernation. This change, however, somewhat slowed down average query
> performance, which is an acceptable trade-off.

Good. It's not a choice most people would like, but you know your
> I do not completely understand which firebird processes use memory cache and
> I wonder if there is a way to reduce the memory cache without giving up too
> much performance?

Everything in the database is organized 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 it starts, Firebird reads the database header page first,
then the first pointer page of a system table called RDB$PAGES. That
table tells it where to find the other tables - both metadata and
data. As the application starts, Firebird reads pointer pages which
tell it where to find the data pages for tables and index pages. 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 to avoid an extra read.

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. But the cache will always be full.

You can use the MON$ tables 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.

Hope this helps some.

Good luck,