Thank you for the pull request! I haven't looked at it closely yet (will do
that this weekend), but I think it's great. I have some concerns regarding the
commit demarcation as compared to the requirements of jdbc, but I will come
back at that in comments on the pull request.
Mark
----- Reply message -----
Van: "Maxim Smyatkin" <smyatkinma...@gmail.com>
Aan: <firebird-devel@lists.sourceforge.net>
Onderwerp: [Firebird-devel] Moving Jaybird auto-commit implementation into
Firebird
Datum: vr, mei 29, 2015 16:10
Hello,Recently we've got an information from some company that they would like
to use Firebird/Red Database with OpenCMS, but they couldn't afford it because
of bad performance. We compared it with PostgreSQL and it was true - on some
page views PG showed 4 seconds while for Firebird it was almost 27 seconds.
Skimming through performance audit log explained us, that CMS executes lots
(thousends) of small selects per page view. I agree that it seems like a
horrible design, but anyway PG handled it much faster.
The reasons were:
1. In auto-commit mode Jaybird wraps any statement into start/commit calls. So,
it does two additional calls to DBMS per statement;
2. It doesn't really matter if the statement changes anything or not. It
executes start/commit in both cases.
Here is a pull request with changes to fix this behaviour using DBMS API's
auto-commit flag:
https://github.com/FirebirdSQL/jaybird/pull/1
Any further information about implementation or results is in comment to the
pull request.
It seems to work excellent with OpenCMS, but Jaybird's unit tests show several
problems:
1. testBatch shows failure, because now every statement in the batch execution
mode is commited separately. This behaviour is perfectly fine as JDBC
specification states that you can't rely on batch mode + auto-commit mode.
Maybe, Firebird has some clients who rely on this behaviour, I don't know.
Anyway, you can wrap batch execution into freshly started non auto-commit
transaction, if you wish. I decided to leave this decision up to you and didn't
touch it.
2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in Firebird
transaction engine. When transaction started in auto-commit mode actually
commits (via commit_retaining) it doesn't free any resources. E.g., what
happens in these tests is:
- we create a procedure P using a table T.
- the P states its interest in the T (while looking for P's dependencies);
- transaction commits the changes with retaining flag and forgets to say that
it isn't interested in the T anymore;
- we try to drop the T, but we can't because it is marked as "in use"!
I attached a patch (for 2.5 branch) to release resources on commit_retaining.
But I believe the problem is rather in modify_procedure()'s code, because it
should release rel_use_counts on its own.
3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with an
Error. I don't know why it happens and how to fix it. Looks like some kind of
race conditions, but I'm not sure. If anyone could look into it, it would be
awesome.
Here is my commit for OpenCMS, so that it could effectively use modified
Jaybird:
https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
I will issue pull request as soon as I verify that this Jaybird improvement is
fine.
--
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
