Firebird 2.5 – How to Determine Optimal Disk Cluster Size

disk-spacefirebirdperformance

I'm using Firebird 2.5.6 Classic Server on a Windows 7 64-bit machine. We're moving the database to a separate disk (32GB NTFS Volume) and are now provisioning that disk. My question is, what should be taken into account when deciding disk cluster size? The goal being to get optimal performance when the database is reading from disk. I'm hoping you all can provide information on the relevant factors so that I can make my own decision, and so this question will be helpful to others in different situations.

Should the cluster size (allocation unit size) match the Database Page Size? I'm not very knowledgeable in how Firebird uses the disk though, maybe it makes more sense to use a bigger cluster size? I read this question on the same topic but about SQL Server. I'm unsure if Firebird SQL behaves similarly to SQL Server when reading from disk, or if there are other factors to take into account.

Best Answer

A good question to ask here would be: are you using the server for OLTP or OLAP type queries?

If you are using OLTP and your queries are carefully crafted to retrieve and modify only one record at a time, then use a 4kb block size (I'm assuming that in OLTP, you don't have any records with record sizes in excess of 4kb?).

Personally, I would go with what it says here. This article recommends a 4kb block size for Interbase systems - I know they're not the same, but they are "kissing cousins..." :-). Unless you have a compelling reason not to, then choose 4kb.

Despite this, by far the most important thing in this article is what it says at the end:

Although 4KB seems to be the best page size for most databases, the optimal size depends on the structure of the specific metadata and the way in which applications access the data. For this reason, you should not consider the 4KB page size guideline to be a magic value. Instead, you should perform testing with your application and database under several different page sizes to analyze which configuration gives the best performance.

This is a very nicely phrased, technical way of saying YMMV, make sure and kick the tyres before you buy! FWIW, you could also look at this article - it popped up! BTW, not using Firebird 3? Check what the article says about 3.

Your question got me interested - unfortunately, there aren't many Firebird benchmarks on the internet - but I did find this about Postgresql. Again, not the same thing, but it is an MVCC system and its numbers might indicative?

Related Question