> 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/