Re: [firebird-support] OIT / NT
consider the comments on COMMIT RETAINING in the quotes from Helen's and
Ann's messages below - maybe there's one place where you're running with
the default and it's only triggered on the installation you're seeing
the problem on.
Tiberiu Horvath schrieb am 31.03.2012 um 10:03 (+0300):
> My BIG concern is that my program (Delphi XE with good old IBXFB performance - Helen Borrie 06.01.06
> components) run with the exactly same release, on 4 different clients
> generating diferent results.
Then look closely at how transactions are being committed. The IBX
default for AutoCommit is to use CommitRetaining. This is handy for
Delphi client apps and disastrous for the server. It causes garbage to
build up steadily. You are seeing better performance on a clean
database and degrading performance as garbage builds up and doesn't
> 3 of these are in normal parameters (while working, the gap between
> OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec)
> but when that particular client (same program, same FB client, same
> OS, same ISP) starts my program, the mentioned values increase in an
> alarming rate of 100...200 / 5 sec, so that at the end of a normal
> working day, the gap is arround 1.5 mil transactions. People using the
> program at this company complain about the slowdown in the afternoon
> compared with the morning.
> From: Ann Harrison
> Sent: Friday, March 30, 2012 7:07 PM
> OK, time for my lecture on OAT and OIT.
> The OAT is the oldest transaction that the system considers to be
> active. It blocks garbage collection and induces database bloat.
> Transactions that commit using "commit retaining" do not advance the
> OAT. Transactions that are left open for hours - even transactions
> that have not changed the database - leave the OAT stuck. Once the OAT
> is stuck, Firebird must keep old versions of records that transaction
> might read if it ever wakes up and starts working again.
- On 2-4-2012 8:58, Norman Dunbar wrote:
>> I disagree, the application (or application developer) should do that;)
>> (preparing statements every time it needs it). It is the driver and/or
>> connection pool that should keep a pool of prepared statements (see
>> section 11.6 of JDBC 4.1) for reuse.
> I disagree with your disagreement! ;-)
> I think the developer should know exactly what the application is doing,If an application developer should ever be concerned with it, he should
> otherwise the developer doesn't know what they should be coding for or
> protecting against surely? Especially when the cache isn't caching, for
> some reason?
isolate that concern to a separate layer. Preferably by implementing a
proxying JDBC datasource (or use one of a thirdy party) which allows for
that pooling/caching. He should not have code all around his application
to make that caching work.
>> It also duplicates whatAs a developer, I would expect the Oracle JDBC driver or UCP driver to
>> connection pools (including statement pools) could be doing for you.
> Not really. Oracle natively supplies connection pools and cursor caching
> for you, if you know how to use them. And this brings me back to my
> initial point above, the developer *should* know how to use them.
take care of most of those details for me (and it does, eg see
My concern as a developer is getting the application to work and add
features, not getting muddled in the intricacies of the database(s) I use.
I am exaggerating, but my point is to have a separation of concerns: my
application code should not be concerned with this, doing that will
complicate my code, make it harder to debug and couple it tightly to a
specific database/driver implementation.
> I mean, take the example linked to above. What happens when thisThis pooling is either part of the application itself (by way of the
> application is moved to a system that doesn't have the external
> connection pooling?
driver and its config) or of the application server running the
application. The driver taking care of the pooling would either be part
of the application, or be configured on the system according to the
deployment instructions of the application.
> On an Oracle system, you get lots of parsing, granted most of it will beMost Java applications work like this:
> soft parsing which isn't as resource intensive as hard parsing, but
> regardless, each parse takes a latch (two actually) and there is a queue
> of other statements, needing to be parsed waiting, for all these
> unnecessary parses. Performance suffers.
> Do it (what I call) right - prepare the statements as few times as
> possible (ie once) and use it as many times as possible, and you save
> resources, improve performance and make the user's life a whole lot better.
1) Open connection from datasource (usually: obtain logical connection
from connection pool behind that datasource)
2) Prepare statement
3) Set parameters
5) (optional) traverse resultset
6) close statement
7) repeat steps 3-5 or 2-6
8) Close connection (usually: close logical, physical is returned to pool)
The lifetime of steps 1-8 is usually very short (eg one request/response
cycle in a web application).
In this situation a developer cannot easily keep track of the
connection, and he shouldn't as he only 'owns' the connection for the
duration of steps 1-8!
For all intents and purposes to the application developer it will be as
if he retrieves a new connection every time (and depending on the
configuration and implementation class of the datasource it could be)!
So for the application developer there is no way to track statements
over multiple invocations of steps 1 to 8.
That is where statement pooling comes in. If statement pooling is
available/enabled, step 2 will check the statement pool of the physical
connection and retrieve the prepared statement if available (and
otherwise prepare a new one), while step 6 will return the statement to
the statement pool.
> Agree to disagree?No problem.