1

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.

  • Is that SSD or HDD? – Vérace Oct 26 '17 at 19:08
  • I'm using a HDD – Dusty Vargas Oct 26 '17 at 20:49
2

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?

  • Much thanks for your answer. Unfortunately, the database in question is used by a third-party app so I don't have the option of upgrading to Firebird 3.0. I was tasked with performance tuning, but don't really have any DBA experience so a lot of what you wrote went way over my head. I'm sure that's some useful information though, thanks for taking the time to put together a great answer. Hopefully another community member is qualified to mark this as accepted? – Dusty Vargas Oct 26 '17 at 21:59
  • It's your question - you're the only person who can accept the answer! You can also upvote if you're so inclined! :-) – Vérace Oct 26 '17 at 22:00
  • I will experiment with different disk cluster sizes as well as different page sizes and see what works best for me. And if I understand correctly the differences between Transactional and Analytical in this context, the database is used for OLTP type queries (transactions?). – Dusty Vargas Oct 26 '17 at 22:01
  • I would upvote but lack reputation. I'll mark it as accepted. – Dusty Vargas Oct 26 '17 at 22:01
  • Do you have access to the schema? If so, check the sizes of your most regularly queried and written to tables - that should be a help. On yahoo groups, there are firebird lists - many of those who actually write the code post and/or lurk there. Yours is the sort of question that would interest the performance wonks! – Vérace Oct 26 '17 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.