[jira] [Commented] (AMQ-2520) Oracle 10g RAC resource usage VERY high from the passive servers SQL requests to the Database.

["Volker Kleinschmidt (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/AMQ-2520?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15172717#comment-15172717 ] 

Volker Kleinschmidt commented on AMQ-2520:
------------------------------------------

Hmm. Why on earth isn't this the default behavior of the default DB locker? You naturally do NOT want to sit around holding active transactions that are trying to acquire a DB resource that you know you'll never get unless the master broker fails. That's being a really bad citizen in the DB world. The FOR UPDATE NOWAIT clause is the right thing to use, period.

> Oracle 10g RAC resource usage VERY high from the passive servers SQL requests to the Database.
> ----------------------------------------------------------------------------------------------
>
>                 Key: AMQ-2520
>                 URL: https://issues.apache.org/jira/browse/AMQ-2520
>             Project: ActiveMQ
>          Issue Type: Bug
>          Components: Broker
>    Affects Versions: 5.3.0, 5.4.0
>         Environment: Redhat Enterprise Linux 5, Oracle 10g RAC
>            Reporter: Thomas Connolly
>             Fix For: 5.x
>
>
> Two active MQ brokers are installed on RH EL 5 servers (one per server). 
> They're configured as a JDBC master / slave failover (as per examples). Failover is tested and working and messages delivered.
> Oracle is used for synchronisation (ACTIVEMQ_ tables), persistence etc.
> We run a durable subscriber, and the client connects via a failover operation.
> The SELECT * FROM ACTIVEMQ_LOCK FOR UPDATE is causing spin lock on the Oracle database.
> Basically the indefinite waiting from the passive mq instance is causing high resource usage on Oracle.
> After a short period Oracle dashboard shows a high number of active sessions from Active MQ due to the continuous execution of
>     UPDATE ACTIVEMQ_LOCK SET TIME = ? WHERE ID = 1
> in the keepAlive method in 
>     https://svn.apache.org/repos/asf/activemq/trunk/activemq-core/src/main/java/org/apache/activemq/store/jdbc/DatabaseLocker.java
> As a workaround we've had to push out the lockAcquireSleepInterval to 5 minutes in the configuration of ActiveMQ, but this didn't work. 
> <jdbcPersistenceAdapter dataSource="#oracle-ds" useDatabaseLock="true" lockAcquireSleepInterval="300000" createTablesOnStartup="true"/>
> We're currently changing the broker to poll rather than block so in Statement.java we've added a WAIT 0 that throws an exception if the lock is not acquired.
>     public String getLockCreateStatement() {
>         if (lockCreateStatement == null) {
>             lockCreateStatement = "SELECT * FROM " + getFullLockTableName();
>             if (useLockCreateWhereClause) {
>                 lockCreateStatement += " WHERE ID = 1";
>             }
>             lockCreateStatement += " FOR UPDATE WAIT 0";
>         }
>         return lockCreateStatement;
>     }
> Any suggestions to this issue, this seems to be a quite fundamental issue?



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)