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
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nick Smith (JIRA)
Sent: 22 February 2008 14:36
To: [email protected]
Subject: [Sequoia] [JIRA] Commented: (SEQUOIA-1029) Recovery Log consistency
check fails using Derby as the database
[
https://forge.continuent.org/jira/browse/SEQUOIA-1029?page=comments#action_14292
]
Nick Smith commented on SEQUOIA-1029:
-------------------------------------
This works a lot better.
For others who use Derby as the Recovery Log, use the following VDB config :
<RecoveryLog driver="org.apache.derby.jdbc.EmbeddedDriver"
url="jdbc:derby:<my-path>recoverydb;create=true;shutdown=true"
login="APP" password="APP">
<RecoveryLogTable tableName="RECOVERY"
logIdColumnType="BIGINT NOT NULL"
vloginColumnType="VARCHAR(10) NOT NULL"
sqlColumnType="CLOB NOT NULL"
extraStatementDefinition=",PRIMARY KEY (log_id)"/>
<CheckpointTable tableName="CHECKPOINT"
checkpointNameColumnType="CLOB NOT NULL"/>
<BackendTable tableName="BACKEND"
databaseNameColumnType="VARCHAR(40) NOT NULL"
backendNameColumnType="VARCHAR(20) NOT NULL"
checkpointNameColumnType="CLOB NOT NULL"/>
<DumpTable tableName="DUMP"
dumpNameColumnType="CLOB NOT NULL"
dumpDateColumnType="TIMESTAMP"
dumpPathColumnType="CLOB NOT NULL"
dumpFormatColumnType="CLOB NOT NULL"
checkpointNameColumnType="CLOB NOT NULL"
backendNameColumnType="VARCHAR(40) NOT NULL"
tablesColumnType="CLOB NOT NULL"/>
</RecoveryLog>
As Suggested by Emmanuel Cecchet.
Many Thanks to all that work on Sequoia!
Nick
> 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.checkRecover
> yLogConsistency()
> 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).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://forge.continuent.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
