Hi guys, This discussion shows that we need some additional work on the SQL handling in the recovery log. Here are three things I have noted:
1.) Need an index on transaction_id. Some databases like MySQL have syntax that you can specify the index using the existing tags but others like PostgreSQL do not. 2.) We should create the database automatically if it does not exist. Hypersonic and MySQL do this, I assume the same would be true for Derby. However, PostgreSQL does not. This should be baked into the tags. 3.) The SQL appears to be sub-optimal for some databases. I second Emmanuel's call to identify any other SQL statements that do not appear to work efficiently. I have to do another round of small recovery log fixes in the near future and will try to get these items folded in as well. Thanks, Robert On 2/27/08 8:54 AM, "Emmanuel Cecchet (JIRA)" <[EMAIL PROTECTED]> wrote: > [ > https://forge.continuent.org/jira/browse/SEQUOIA-1029?page=comments#action_143 > 00 ] > > Emmanuel Cecchet commented on SEQUOIA-1029: > ------------------------------------------- > >> From the mailing list: > > For info: with regards to the "unbearably slow" performance of this sql on > Derby, I've found the following equivalent SQL is orders of magnitude faster > under Derby: > > Instead of: > SELECT * FROM RECOVERY WHERE sqlStmt LIKE 'begin%' AND transaction_id not in > ( SELECT transaction_id FROM RECOVERY WHERE ( sqlStmt = 'commit' OR sqlStmt = > 'rollback' ) ) > > Use: > select * from recovery r1 where r1.sqlStmt like 'begin%' and not exists > ( select transaction_id from recovery r2 WHERE r1.transaction_id = > r2.transaction_id > AND ( r2.sqlStmt = 'commit' OR r2.sqlStmt = 'rollback' ) ) > > However, it does also require an index on recovery.transaction_id > > I've build my own version of Sequoia with this SQL, and the checking of the > recovery log has come down from many minutes/hours for large recovery logs, to > seconds. > > E.g. with 72,000 records in the recovery log, on a Sun X4100: > Current SQL: at least 15 minutes (I got bored and gave up) > New SQL: 10 seconds. > > > I can't speak for it's performance on other databases, however... > > Andrew Lawrenson > > ------------- > > This should be tried with LIKE instead of = > All feedback is welcome to report any issue with other databases or confirmed > better performance (with Derby or others). > > Thanks, > Emmanuel > >> Recovery Log consistency check fails using Derby as the database >> ---------------------------------------------------------------- >> >> Key: SEQUOIA-1029 >> URL: https://forge.continuent.org/jira/browse/SEQUOIA-1029 >> Project: Sequoia >> Type: Bug > >> Components: Recovery Log >> Versions: sequoia 2.10.11 >> Environment: JAVA 1.5.0_13 >> Solaris 10 SPARC >> Derby 10.3.2.1 >> Reporter: Nick Smith >> Assignee: Emmanuel Cecchet >> Fix For: sequoia 2.10.10 > >> >> Original Estimate: 3 hours >> Remaining: 3 hours >> >> The Recovery Log consistency check fails when using Derby as the database. >> The consistency check SQL should use 'LIKE' instead of '=' as this databases >> databases have different ways to interpret '=' on Strings. >> i.e. the SQL in >> org.continuent.sequoia.controller.recoverylog.RecoveryLog.checkRecoveryLogCon >> sistency() >> should be : >> stmt = getDatabaseConnection().prepareStatement( >> "SELECT * FROM " + getLogTableName() + " WHERE " >> + getLogTableSqlColumnName() + " LIKE ? AND " >> + "transaction_id not in (SELECT transaction_id" + " FROM " >> + getLogTableName() + " WHERE " + getLogTableSqlColumnName() >> + " LIKE ? OR " + getLogTableSqlColumnName() + " LIKE ?) "); >> instead of : >> stmt = getDatabaseConnection().prepareStatement( >> "SELECT * FROM " + getLogTableName() + " WHERE " >> + getLogTableSqlColumnName() + " LIKE ? AND " >> + "transaction_id not in (SELECT transaction_id" + " FROM " >> + getLogTableName() + " WHERE " + getLogTableSqlColumnName() >> + " = ? OR " + getLogTableSqlColumnName() + " = ?) "); >> stmt.setString(1, BEGIN + "%"); >> stmt.setString(2, COMMIT); >> stmt.setString(3, ROLLBACK); >> Please Note! >> Derby also seems to unbearably slow performing the above SQL (at least with >> my recovery log configuration). -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
