>> Can you compare plans and IO statistics of both engines ?
> Plans are difficult to compare because the checkout operation executes
> more than a dozen statements and uses parameters. We are using Firebird
> Embedded. Is there an easy way to record the statements that were
> executed and to print those out so that I could then execute them
> manually and check the plans and IO statistics?
It depends on tools you use to develop application. Many of them have tracing
capability which you may activate on application side. It gives you statements and
probably plans. To take IO statistics you need to modify application, i guess.
>> Also it is interesting to look at database statistics
>> after the test (gstat -r)
> As far as I can tell, the statistics do not differ considerably. The
> number of transactions has grown by approximately 100 (expected). The
> table and index statistics are almost identical -- the number of leaf
> buckets in several indices has increased by 1, which I guess is OK.
> I did debug the test with both FB 2.0.3 and FB 2.1.0. The difference I
It is also interesting to test 2.0.4 as it have latest nbackup patch.
> see is that in FB 2.0, write_buffer is called approximately 60 times for
> each commit (I am referring to the checkout operation I am testing).
> Precisely the same operations cause about 95 write_buffer calls when I
> switch the engine to FB 2.1.0. The initial database is exactly the same
> in both cases, and the executed statements are the same.
> In the test I repeat the checkout operation 100 times. Each time, the
> number of write_buffer calls during commit differs in this way between
> FB 2.0 and FB 2.1 (about 60 write_buffer calls vs. about 95 write_buffer
Do you measure number of write_buffer calls during commit only or during
statement execution + commit ?
> How can I tell what is making the additional write_buffer calls
> necessary? I can see that in FB 2.0 CCH_flush calls btc_flush while in
> FB 2.1 it calls flushDirty (DIRTY_TREE vs. DIRTY_LIST).
> Could it be that
> the new algorithm is causing additional (unnecessary?) flushing of pages
> when it tries to maintain the order of page writes?
I don't think so. But i will investigate such possibility too.
> Or, is the problem
> occurring earlier, i.e. FB 2.1 is modifying more pages than FB 2.0?
> What next?
Compare also number of calls of CCH_mark, unmark, insertDirty vs btc_insert,
removeDirty vs btc_remove. Or send your profile numbers to me ;) Or, better,
send to me sample database and application (if it is possible of course).