>
> It matters here whether you use Superserver or Classic Server.
> Superserver IIRC releases its buffer after every connection to a
> database is closed. It may be as simple as creating another
> connection from a service on your server that serves to hold
> the database open.
>
> I am also very surprised the OS cache is not helping? It is
> certainly noticeable on mine.
On FB 2.0.3 (version that I'm currently using) OS cache is wiped out
immediately after disconnecting the database. As a workaround, I
created a 'dummy' connection of the database that stays alive and does
nothing while other db connections do the work. This allowed me to
keep my OS cache on Windows2003 server, but still suffered db connect
latency since I would connect/disconnect with every request.
> All database activity happens within the context of a
> transaction.
> Some databases dumb this down for you. Firebird uses MGA, which
> allows you to have a stable snapshot (if that is what you want)
> without blocking writers. In a snapshot transaction, you see
> the database as it was at the moment your transaction started.
> You do not see new records, you see the old values of updated
> records and deleted records.
>
> In order to provide this functionality, Firebird must keep track of
> what transactions are currently active. If any of those transactions
> could potentially be "interested" in an old record version, that old
> record version can not be removed (garbage collected).
>
> If too much garbage collects in your database, it will choke the
> performance, so there is a balancing act to be done. At some point,
> every transaction should be "ended" so the garbage collector can do
> its thing. Ended means either rolled back or committed.
>
> In terms of a read only transaction, there is no (logical)
> difference between a rollback and a commit, both end the
> transaction.
>
> Now a read-committed read-only transaction is a special case.
> Read-committed means that it always fetches the most recent
> committed record version (note difference to snapshot). A
> read-committed read-only transaction doesn't need to worry
> about old record versions and therefore does not inhibit
> garbage collection and could I suppose therefore be left
> open indefinitely.
This is exactly why I recently decided to use 'read-only' access mode
and 'read-committed' isolation. From what I was able to gather from
Borrie's FB book, and the web, it seemed very efficient and I also
visualize that this model will work perfectly for for the vast
majority of my read-only transactions.
And like I said in my original post, I saw no memory leak even though
I don't commit this type of transaction. I really pounded my FB 2.0.3
test server with multiple connections performing different queries
always setting different parameters with each query (just like in a
live installation). I tested this in a huge loop which would have
made memory leaks obvious.
> All transactions need to be ended eventually (even if it is an
> implicit rollback caused by a disconnection).
I will keep this in mind for writable transactions or read-only
transactions that do not use 'read-committed' isolation.
I think I got a good handle on this now, and thank you for all of your
help and everyone else involved.