| Subject | Database settings for large tables |
|---|---|
| Author | Toby Moxham |
| Post date | 2012-03-23T09:15:42Z |
Hi,
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.
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.
Thanks
Toby
This email is confidential and subject to important disclaimers and conditions including on offers, accuracy and completeness of information, viruses and confidentiality, available at http://www.oak.co.uk/disclaimers.
Company Registration No. 2102234 | VAT No 434 6208 63 | Registered Office: 7 Albany Park, Cabot Lane, Poole, Dorset, BH17 7BX
[Non-text portions of this message have been removed]
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.
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.
Thanks
Toby
This email is confidential and subject to important disclaimers and conditions including on offers, accuracy and completeness of information, viruses and confidentiality, available at http://www.oak.co.uk/disclaimers.
Company Registration No. 2102234 | VAT No 434 6208 63 | Registered Office: 7 Albany Park, Cabot Lane, Poole, Dorset, BH17 7BX
[Non-text portions of this message have been removed]