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
commit_retaining_release.patch
Description: Binary data
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
