I run a test on a small database, around 128MB using Firebird 2.5 64 bit
superserver and the test condition is like these :
1) Put database completely on RAM, set DefaultDbCachePages = 2048 and
FileSystemCacheThreshold = 0
2) Put database completely on disk, set DefaultDbCachePages = 2048 and
FileSystemCacheThreshold = 0
3) Put database completely on RAM, set DefaultDbCachePages = 2048 and
FileSystemCacheThreshold = 65536
4) Put database completely on disk, set DefaultDbCachePages = 2048 and
FileSystemCacheThreshold = 65536
I do some extensive select sql on this database, time to finished on each
conditions are :
1) +/- 37 sec
2) unknown since it seems took a very long time that I must ended with
task manager
3) +/- 6 sec
4) +/- 6 sec
From those result, I don't understand why result from test condition 1
(which is, database completely on RAM) is slower than condition 3 and 4.
Isn't test condition 1,3 4, all running on RAM?
Yes, but RAM disk is not the same as RAM. Further, some RAM disk drivers are
much better than others.
Sometime ago, I found an analysis/comparison of various RAM Disk drivers.
Although the analysis was a couple of years old, it did find that there are
substantial performance differences between drivers.
I can see if I can find the link, if you'd like.
Separately, did you run all the tests from the same state -- ie. a fresh
reboot. If not, test #1 would be paying the cost to load all of the pages into
the OS cache, whereas the other tests would not. Either reboot between tests,
or create a pre-test pass which would force the database to be loaded and then
run the tests.
Sean