Anderson Farias wrote:

>
> True. OTOH I doubt a shared cache would do any good to a system like the one
> I'm describing. Users are most of the time doing different tasks.
>

The problem with separate caches is that a number of "structural"
database pages are shared, even if two applications are using
different tables. The four I can think of are the header page,
the transaction pages, the page inventory pages, and the generator
pages. If several connections are starting transactions and writing
data that uses generators, all these pages are areas of contention.

When a transaction starts, it changes the header page, updating the
next_transaction value. When a transaction commits, it changes a
transaction page. When Firebird allocates a new page on behalf of
a transaction, that connection updates a page inventory page. When
a garbage collection pass frees up a page, it also updates the
page inventory page. And every time a generator is incremented or
decremented, the page that holds it changes.

In a "cache per connection" architecture, before changing a page
in cache, the process must get an exclusive lock on the page.
The request for an exclusive lock is passed to all processes that
have a shared lock, requiring them to release their lock at the
earliest opportunity. When all shared locks are gone, the process
can change the page. As soon as any other process wants the page,
it requests a shared lock. That action causes the holder of the
exclusive lock to be notified, and it must write the page to disk,
then release its lock. The process(es) that requested the shared
lock acquire it, but must read the page from disk to get the latest
version.

So, in a 200 user application where each connection starts on
transaction per second, the header page and the transaction
inventory page are written 200 times a second and read approximately
39,800 times per second. That's 400 writes and 79,000 reads per
second without doing any other work. The O/S page cache cuts the
cost of the reads by a lot, but all the pages that were changed have
to go to oxide.

A shared cache architecture has to write all the changes too, but
it can group transaction commits, combine them with other changes
and generally make several changes in a single pass. On the other
hand, it's harder to synchronize...

Cheers,

Ann