[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=16200986#comment-16200986 ] 

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

Also note that if you use the FOR UPDATE NOWAIT or FOR UPDATE WAIT N forms of the locking query (as should be the default!!!), you will get an exception from the attempt to lock. Currently these are being logged at "warn" level, so they will flood your logs with something that should be perfectly ignored, as this exception is the expected state 99.99% of the time (it should at most be logged at debug level). So there's definitely a need for code change here.

> 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.4.14#64029)