> our company is developing a system using firebird since years. This system is > a "real-time" production steering application. > This means for us, that our applications need to perform a lot of different > sql (with join over some table) in the shortest possible time. Today if we > encounter a slow application behavior, we try to optimize the sql's...
SQL tuning (and the applied underlaying physical model) is still one of the most crucial area when it comes to improving performance, so that's a good start. > I just wonder, what performance would be possible usind firebird, as we are > planning to switch to a more flexible database structure, and this will for > sure increase the db load. Preparing stuff with the goal to be more flexible/generic usually results in a solution where things are getting interesting with a growing data volume, increasing concurrent access etc. A good example is a fact table in a data warehouse: Should the "measures" be layed out vertically in a separate column per measure or one row per measure and type with one value in a generic measure column? While the latter is much more flexible when new measures and/or types or coming along, the first approach is much better from a performance POV, due to deceasing the number of rows. It's all about your requirements and the trade-offs you are willing to take when it comes to flexiblity vs. performance. But there is still a chance of getting both right, possibly with a third attribute in the equation: redundancy. But that's just a guess, I don't know your application domain, but I guess with your mentioned joins over some tables, we are talking 3NF here. ;-) > We are using the superClassic with around 30-50 db- users connected. We need > to keep our db as small as possible to keep the performance on a high level. > This means every year we need to copy away the old data. Actually the fb- > process does not often claim to the CPU's limit, so i think the bottleneck > are the HDD's, where we are using usuably RAID 1 with fast server HDD's. > > My question: > > Is there any complete documentation regarding all the performance critical > points for the firebird db? Helen's book (a new edition has been arrived lately) is discussing Firebird entirely, so I'm sure you can pick up importaning pieces from there. You might also investigate into commercial services from IBPhoenix, myself etc. which helps you to get the best out of your Firebird environment very quickly compared to searching Google, discussing things on public lists etc. I have a few performance articles or even a smallish book in mind, but due to lack of time and (ideally) sponsors (bing, bing; anyone listening *g*), I haven't came across starting with that. > What processor to use with which "db- usage- pattern"? > -->Up to how many core provide more speed? > How to optimally use page size settings (RAM)? > What about SSD's? Which modell is good for productive usage? > OS- settings, p.e. cluster size of file system? Interesting questions. I guess there is no rule them all answer, but my personal priority chain when it comes to have more/better etc. somehow looks like that: I/O >> (tm) RAM >> (tm) CPU *(tm): Some people silently reading this list and never posting something useful are making money out of that what we are writing in public here, thus be careful when you re-use the '>>' in that context. ;-) With the most important thing from left to right. Your I/O can't be fast enough in a database environment. A suitable amount of RAM might be much more important than e.g. 8 cores vs. 24 cores. But again, this all needs to be investigated for your personal needs, which you might not want to be discussed in public. ;-) Well luckily, we don't discuss Oracle here, because having CPU at the end of the priority chain is a real licensing cost saver. Don't get me wrong: I won't like to say that you will be able to handle a particular load on a single core when having 48G RAM available. You might be able, but again, it depends on your usage pattern. Catching up with your questions, but again there is no bullet-proof answer, as we don't know your exact usage pattern: * Number of cores (don't take the following to seriously): Ideally the max. number of concurrent connectins, because a single connection wiht CS/SC can fully utilize a core. But I guess less is fine as well. ;-) * Page size: 8K or 16K * Page buffers: More than the default of 75. It depends on the number of concurrent users, available RAM etc. * RAID level: Raid 5 is a no-go. Ideally RAID 10. * SSD: Cool. Get them, if you have the budget. Even better if we are talking "Enterprise" SSD here. * OS: Take the one you are most comfortable with. Ah well, I'm currently taking me out of business article/book/services wise ... ;-) Hope this still helps though. -- 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/
