> We have a number of clients who have quite large databases with pretty much all the data in two tables, we perform a searching on these two tables with a join.
>
> The database sizes go up to around 11GB, one issue we are experiencing is that the query time can vary considerably, some times we will get results back in a couple of seconds and then moments later a similar query can take minutes. Which I assume is something to do with how its being cached.

Differences in execution time with the same execution plan can be the
result of e.g. different I/O patterns and/or with cooperative garbage
collection in place, the executing statement has to do additional GC work.

If you are on Firebird 2.5, this all can be inspected with the Trace API.

> We would like to know the optimum settings for cache/buffers etc for a large database, we have changed the pagesize to 16k and experimented with the DefaultDbCachePages, when setting DefaultDbCachePages it seems to improve it and we can see firebird using a lot more memory, however it seems to release the memory very quickly and then queries can become slow again, are there any other settings that we should be looking at.

Realising the memory very quickly sounds like you are using Classic (CS)
or SuperClassic (SC) (both are the only options for SMP anyway) with
it's local cache per connection. So, if you have short-living
connections, the page cache probably doesn't help a lot, but the OS will
cache as well.

Regarding the optimal setting for the page buffer, this depends on
various factors, one of them is the number of connections when you are
on CS/SC. I have a "RAM sizing calculator" Excel sheet which takes a few
input parameters and calculates the approx. Firebird RAM usage scenario.
Feel free to contact me off-line if you want me to go through a simple
scenario.

Other facilities/areas which can help to improve the environment are:

* Analyze the database header page and database statistics (data/index
pages and record version)
* Use the MON$ tables for inspecting a current activity snapshot
* Use the Trace API for near real-time monitoring
* fb_lock_print is another tool which can help


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/

Do you care about the future of Firebird? Join the Firebird Foundation:
http://www.firebirdsql.org/en/firebird-foundation/