Marcelo Caetano wrote:
I have a Firebird 2.5 server running in a Windows Server 2008 64bit. The server have 16 cpu cores and 16GB of RAM. The database file have 3099MB of size and I have 2% of writes against 98% of reads of all I/O requisitions. How can I put the entire database (or the most part) in memory by adjusting the Page Size, Cache Size and the DefaultDbCachePages parameter? Is it possible?
Sean Leyne answers:
Over time the Windows OS will effectively have the entire database in its page cache, so I don't think there is anything you really need to do.
Ann W. Harrison answers:
Sean is right that Windows will hold the working portions of the database in its page cache which may well be more effective than using database cache. Others are right that you could use a RAM disk - though the page cache solutions have the advantage of writing changes to disk, so you don't risk losing that 2% of your operations if the RAM disk fails. However, there's also a simple, if unnecessary, answer to your question.
Figure out how big your database is, leaving room for expansion, divide that number by your page size and the result is the number of pages in the database, give or take Window's file size measurements, and that should be your cache size. By allocating that much memory to the database page cache, you reduce the amount of memory available for other uses, like the system page cache.
Typically databases have a working set of data which is in regular use and other data that is referenced less often. You're likely to get best performance if you let the database page cache and the system page cache decide which pages are frequently used and should be kept in memory and which can rest quietly on disk, not wasting valuable RAM space. If you're even considering keeping the whole database in memory, run 64-bit Firebird. The MON$ tables that report on fetches/reads/writes/marks will give you a clue about the effectiveness of the cache.