Sorry, you must all be really bored with these, but I just can't get my
head round it.

We're looking to upgrade our (Linux) operating system, and with that is
likely to come an upgrade from 32 bit Firebird 2.1 Superserver to 64 bit
Firebird 2.5, but what architecture?

We need more performance than we're getting at the moment, a factor of
10 would be a nice start.

We've got two types of workload

(a) Web accesses - short-lived connections that run large queries, which
in our experience really do benefit from having thousands of pages in
the Firebird cache. There are typically multiple queries in the same
connection that use the same data. Concurrent queries from multiple
users, or subsequent queries from the same users, are also very likely
to use some thousands of the same pages that were used by immediately
preceding queries. There are a small number of simultaneous users, say
from two to a dozen.

(b) Persistent links to other systems. I believe that these need
considerably less in the cache, but share some of their cache
requirements with the web accesses. These typically perform large
numbers of small queries, as opposed to the small number of large
queries that the web accesses involve. There are single figures of these
connections.

We observe that when we've got performance problems one core is
saturated, for seconds or even minutes at a time, and that seems to be
the bottleneck. (Yes we keep nibbling away, and sometimes taking large
bites, at schema design and query optimisation etc, and we will continue
to do so, but we don't think that can be the complete solution.)

So for 64 bit 2.5 the choices seem to be

- Superserver, which keeps us our large shared cache, but will (still)
only use one core (of the many available on the hardware)?
- one of the other two architectures, which will use more cores, but may
simply occupy them re-reading the same thousands of pages time and time
again from the database into the cache?

Have I got that right so far?

I've read stuff about keeping persistent connections from PHP to the
database to maintain the state of the private cache in a non-Superserver
setup, and how that causes all sorts of problems if one web access falls
over, resulting in a failure to rollback the transaction automatically
on script termination. So, not recommended. I've also read stuff about
how not having a large shared cache doesn't actually matter, because
you're not reading from the disk into cache anyway, you're only reading
from the filing system's cache into the database server's cache. This
latter point does make sense cf some testing and measuring we've done,
but surely it still takes lots of CPU cycles to copy thousands of pages
from one cache into the other over and over again?

Yes I know we could "just try the various architectures and measure the
results", but it's not as simple as that. We want to do some
restructuring of our system anyway, and options include putting bits of
it onto different boxes, and splitting some tables into separate
databases, and possibly putting different database servers onto
different boxes, and stuff like that, which gives rise to an impractical
number of combinations of systems to design, code and test - having to
only design and code one system would go down best with management.

So ... ... ... ???

Oh, and if we do use one of the private-non-persistent-cache
architectures, can we set the cache size differently to suit different
connections with radically different types of workload?

Or have I completely misunderstood the whole business?

--
Tim Ward