ActiveMQ
  1. ActiveMQ
  2. AMQ-2520

Oracle 10g RAC resource usage VERY high from the passive servers SQL requests to the Database.

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Not a Problem
    • Affects Version/s: 5.3.0, 5.4.0
    • Fix Version/s: 5.x
    • Component/s: Broker
    • Labels:
      None
    • Environment:

      Redhat Enterprise Linux 5, Oracle 10g RAC

      Description

      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?

        Activity

        Hide
        Gary Tully added a comment -

        So does the poll work for you by having the lock statement return immediately and hence make the lockAcquireSleepInterval relevant again.
        We can provide a specialization of the lock implementation for Oracle if this is the case.

        Show
        Gary Tully added a comment - So does the poll work for you by having the lock statement return immediately and hence make the lockAcquireSleepInterval relevant again. We can provide a specialization of the lock implementation for Oracle if this is the case.
        Hide
        Thomas Connolly added a comment -

        In the class org.apache.activemq.store.jdbc.Statement I changed the code to add the WAIT 0 (as per the report).

        I then set the lockAcquireSleepInterval back to one second (i.e. the default DEFAULT_LOCK_ACQUIRE_SLEEP_INTERVAL setting).

        A specialisation of the DefaultDatabaseLocker class where the call to getLockCreateStatement method (allow this to be overridden in the DatabaseLocker interface?) calls the Oracle specific WAIT 0 should do it. This is how I got around the problem.

        public void start() throws Exception {
        stopping = false;

        LOG.info("Attempting to acquire the exclusive lock to become the Master broker");
        String sql = statements.getLockCreateStatement(); // For Oracle use the WAIT 0 statement
        LOG.debug("Locking Query is "+sql);

        Show
        Thomas Connolly added a comment - In the class org.apache.activemq.store.jdbc.Statement I changed the code to add the WAIT 0 (as per the report). I then set the lockAcquireSleepInterval back to one second (i.e. the default DEFAULT_LOCK_ACQUIRE_SLEEP_INTERVAL setting). A specialisation of the DefaultDatabaseLocker class where the call to getLockCreateStatement method (allow this to be overridden in the DatabaseLocker interface?) calls the Oracle specific WAIT 0 should do it. This is how I got around the problem. public void start() throws Exception { stopping = false; LOG.info("Attempting to acquire the exclusive lock to become the Master broker"); String sql = statements.getLockCreateStatement(); // For Oracle use the WAIT 0 statement LOG.debug("Locking Query is "+sql);
        Hide
        Gary Tully added a comment -

        you should be able to override the lockCreateStatement and any other via config:

        <jdbcPersistenceAdapter dataSource="#oracle-ds" useDatabaseLock="true" lockAcquireSleepInterval="300000" createTablesOnStartup="true">
            <statements>
                  <statements lockCreateStatement="SELECT * FROM ACTIVEMQ_LOCK WHERE ID = 1 FOR UPDATE WAIT 0"/>
            </statements>
        </jdbcPersistenceAdapter>
        
        Show
        Gary Tully added a comment - you should be able to override the lockCreateStatement and any other via config: <jdbcPersistenceAdapter dataSource= "#oracle-ds" useDatabaseLock= " true " lockAcquireSleepInterval= "300000" createTablesOnStartup= " true " > <statements> <statements lockCreateStatement= "SELECT * FROM ACTIVEMQ_LOCK WHERE ID = 1 FOR UPDATE WAIT 0" /> </statements> </jdbcPersistenceAdapter>

          People

          • Assignee:
            Unassigned
            Reporter:
            Thomas Connolly
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development