On 29-5-2015 16:10, Maxim Smyatkin wrote: > 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.
I am not sure if others depend on this behavior, but I'd prefer to keep the behavior of this as it was. Your suggestion to wrap it in its own transaction seems a good one, but it might be complex to implement (a lot of Jaybird is built around a "single active transaction" model of transactions. > 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. As far as I know the keeping resources like the cursor and blobs open on commit_retaining is intentional (I believe it is one of the reasons for the "retaining" in the name). The error itself is probably related to the fact that some Jaybird tests unnecessarily cleanup after themselves. > 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. I'll look into it. I hadn't noticed before that you made the pull request for Branch_2_2, I will first release Jaybird 2.2.8 (this weekend) before looking at it. > > 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. Mark -- Mark Rotteveel ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
