HA Router - Recommended MySQL settings

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

HA Router - Recommended MySQL settings

Matthew
Hi there,

We're taking a look at using the JDBC store for the HA Router using AWS Aurora as the clustered DB.

Do you have any recommended parameters for the JDBC URI?

Also, we're currently using the standard MySQL Connector/J JDBC driver - have you done any tests with this versus the MariaDB connector?

Kind Regards
Matthew

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HA Router - Recommended MySQL settings

IIT Software
Administrator
We haven't. Actually, all you need is a URI that points to the same database instance (Shared JDBC Store).
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HA Router - Recommended MySQL settings

Matthew
Hi - yes we set the URI to point to the same DB instance on both Active and Standby instances and it works but we're getting some unpredictable behaviour during failover testing.  

When we failover the Aurora cluster, we get the following exception from swiftMQ (hostnames replaced).  Sometimes it then recovers but other times, it doesn't and locks up..

The only client hitting the router is a single client (MultipleQueueSender.java) on the default HA router configuration.  The client is instantly restarted if an exception is thrown.  

We're testing against SwiftMQ HA 9.7.3 with the following store config

  <swiftlet name="sys$store">
    <connection-pool idle-timeout="20000" keepalive-interval="30000" min-connections="10"/>
    <jdbc driver-classname="org.mariadb.jdbc.Driver" password="xxxx" url="jdbc:mariadb:aurora://aurora-cluster-endpoint.rds.amazonaws.com:3306/swiftmq?assureReadOnly=true&amp;validConnectionTimeout=10" username="root"/>
    <statements/>
    <swap path="../../store/instance1/swap"/>
  </swiftlet>

java.sql.SQLNonTransientConnectionException: (conn:11) Communications link failure with primary host aurora-cluster-endpoint.rds.amazonaws.com:3306. Could not send query: unexpected end of exception, read 0 bytes from 4
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:156)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:118)
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:245)
        at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:343)
        at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:394)
        at org.mariadb.jdbc.MariaDbConnection.commit(MariaDbConnection.java:638)
        at com.swiftmq.impl.store.jdbc.pool.PooledConnection.commit(Unknown Source)
        at com.swiftmq.impl.store.jdbc.StoreWriteTransactionImpl.commit(Unknown Source)
        at com.swiftmq.impl.store.jdbc.StoreWriteTransactionImpl.commit(Unknown Source)
        at com.swiftmq.impl.queue.standard.MessageQueue.commit(Unknown Source)
        at com.swiftmq.swiftlet.queue.QueuePushTransaction.commit(Unknown Source)
        at com.swiftmq.impl.jms.standard.v750.NontransactedQueueSession.visit(Unknown Source)
        at com.swiftmq.jms.smqp.v750.ProduceMessageRequest.accept(Unknown Source)
        at com.swiftmq.impl.jms.standard.v750.SessionQueue.process(Unknown Source)
        at com.swiftmq.tools.queue.SingleProcessorQueue.dequeue(Unknown Source)
        at com.swiftmq.impl.jms.standard.v750.s.run(Unknown Source)
        at com.swiftmq.impl.threadpool.standard.PoolThread.run(Unknown Source)
Caused by: java.sql.SQLException: Communications link failure with primary host aurora-cluster-endpoint.rds.amazonaws.com:3306. Could not send query: unexpected end of exception, read 0 bytes from 4
Query is: COMMIT
on HostAddress{host='aurora-cluster-endpoint-1.rds.amazonaws.com', port=3306},master=true.
        at org.mariadb.jdbc.internal.failover.AbstractMastersListener.throwFailoverMessage(AbstractMastersListener.java:470)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.handleFailOver(FailoverProxy.java:293)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:233)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.invoke(FailoverProxy.java:215)
        at com.sun.proxy.$Proxy0.executeQuery(Unknown Source)
        at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:337)
        ... 13 more
Caused by: java.io.EOFException: unexpected end of exception, read 0 bytes from 4
        at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:246)
        at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:214)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1047)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1028)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:165)
        at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mariadb.jdbc.internal.failover.AbstractMastersListener.invoke(AbstractMastersListener.java:368)
        at org.mariadb.jdbc.internal.failover.FailoverProxy.executeInvocation(FailoverProxy.java:222)
        ... 16 more
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HA Router - Recommended MySQL settings

IIT Software
Administrator
It seems to me the failover of the DB instances is not transparent. It doesn't replay the current transaction but throws an exception. This would require the SQL client to handle this. SwiftMQ requires a transparent failover here.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HA Router - Recommended MySQL settings

Matthew
Ah ok - I guess that precludes MySQL then...

Do you have a recommended HA architecture for cloud infrastructure?  In our case, AWS.

Historically, we have found the replicated file store can be slow when failing over and also when the failed node is brought back online.  The shared JDBC store is significantly faster but the requirement for a transparent failover may present us with some issues in the cloud.  

We haven't yet tried the shared file store.. it wasn't my first choice in AWS.

Rgds
Matthew
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: HA Router - Recommended MySQL settings

IIT Software
Administrator
I'd use the replicated file store. It is stable and works everywhere. Failover does not happen that often.

For a shared file store you might check EFS. But keep in mind that disk sync must be enabled and you need to ensure that every write is completed from the EFS itself (buffers must be flushed). I don't know about the speed of EFS but it might be worth to check it out.
Loading...