--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:

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

Hi,


if, then we would use enterprise ssd's. I'm thinking about a Intel ssd...

I think by using a SSD the IO should not be greatest problem anymore!?
So let's follow your list of the next "bottleneck":

RAM:
RAM costs not too much nowadays, so in case of high-performance neccessary it should not be the problem to add additional RAM. Usuably we are using servers with 10-16 GB of RAM for our system. This means firebird can use around 6-10GB of RAM: Our database has a size of ca. 1-4 GB. Being not a firebird developer and so not understanding all the basics of the pages this could mean, that the whole database fits into ram?
As page size we are already using 16K, but whats the best option for pages? Just fill the whole RAM with pages (leaving of course a buffer) or is there a point where more pages are not increasing performance anymore?

The greatest would be of course, if firebird server could report by it self, whats his bottleneck. I don't know if you internally you have some performance indexes?

Thanks in the meantime for your answers. I'll try to do an experiment with this ssd's (will take some time, as I still need to order them) and them report my results here)
If someone has further suggestion, would be great ;)