Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-601

Best Practices for Aurora Cluster Reader Endpoint Failover

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 2.2.3
    • N/A
    • aurora, Failover, question
    • None
    • Aurora MySQL

    Description

      I'm trying to connect to Aurora with the MariaDB Connector and having a hard time figuring out how I should connect. I have two applications: one is read/write, and one is read-only. I'd like to setup failover properly for both applications. Based on what I can understand from documentation I've set up my URLs like this:

      For the read/write application, I have
      jdbc:mariadb:aurora://somecluster.cluster-lettersanddigits.us-east-1.rds.amazonaws.com:3306/db. Will this failover properly given a change in the master instance? Or do I have to specify additional parameters?

      For the read-only application I would like to connect to the cluster reader endpoint, for which I do not see documentation in the Connector. My URL looks like jdbc:mariadb://somecluster.cluster-ro-lettersanddigits.us-east-1.rds.amazonaws.com:3306/db. When I try to include aurora: in the reader endpoint URL, connection acquisition attempts hang indefinitely. My understanding is that as I have it, I will not see great failover properties from the read-only application. However AWS specifies that the cluster reader endpoint has certain failover properties itself, so I'm not sure how to leverage the Connector properly to get the best failover behavior.

      Is there documentation with respect to using the MariaDB Connector with Aurora cluster reader endpoints?

      Attachments

        Activity

          Never thought about that scenario. Not an expert but we are using Aurora MySQL, too. At the moment we distribute known read only selects to the cluster-ro endpoint. We do not specify aurora in the JDBC connection url. My assumption was that you always end up getting a read-only slave with the ro-endpoint. So it does the load-balancing but not the failover?

          I bookmarked this issue because it might be important to us. Sorry, I can't be of help.

          raupach Björn Raupach added a comment - Never thought about that scenario. Not an expert but we are using Aurora MySQL, too. At the moment we distribute known read only selects to the cluster-ro endpoint. We do not specify aurora in the JDBC connection url. My assumption was that you always end up getting a read-only slave with the ro-endpoint. So it does the load-balancing but not the failover? I bookmarked this issue because it might be important to us. Sorry, I can't be of help.

          I'm facing the exact same issue.
          For now, I am using the jdbc:mariadb endpoint (instead of jdbc:mariadb:aurora) for the read-only endpoint.
          Even though I'm not sure that's the correct way to use it, it seems to be working fine.

          danielfariati Daniel Faria Gomes added a comment - I'm facing the exact same issue. For now, I am using the jdbc:mariadb endpoint (instead of jdbc:mariadb:aurora ) for the read-only endpoint. Even though I'm not sure that's the correct way to use it, it seems to be working fine.
          plebedev Peter Lebedev added a comment -

          Per the documentation, if you use aurora mode and set the connection to read-only, it would automatically route to slave instances (aka readers):
          https://mariadb.com/kb/en/about-mariadb-connector-j/

          aurora This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection.
          This mode has been available since MariaDB Connector/J 1.2.0

          This was working fine with 1.7.2. I'm now trying to test 2.5.4 and I have some evidence that it is not always the case but according to the docs it should work.

          plebedev Peter Lebedev added a comment - Per the documentation, if you use aurora mode and set the connection to read-only, it would automatically route to slave instances (aka readers): https://mariadb.com/kb/en/about-mariadb-connector-j/ aurora This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection. This mode has been available since MariaDB Connector/J 1.2.0 This was working fine with 1.7.2. I'm now trying to test 2.5.4 and I have some evidence that it is not always the case but according to the docs it should work.
          marc.reilly Marc Reilly added a comment -

          Peter, I done a quick check and it seems the load balancing in Aurora may have stopped working between v 2.4.3 & 2.4.4. I haven't had the chance to deep dive but there are a couple of commits in 2.4.4 related to Aurora that may have contributed. From what I can tell releases 2.4.4+ are not working correctly for loadbalancing using the mariadb:aurora: connection string.

          2.4.4 release notes: https://mariadb.com/kb/en/mariadb-connector-j-244-changelog/
          https://mariadb.com/kb/en/mariadb-connector-j-releases/

          marc.reilly Marc Reilly added a comment - Peter, I done a quick check and it seems the load balancing in Aurora may have stopped working between v 2.4.3 & 2.4.4. I haven't had the chance to deep dive but there are a couple of commits in 2.4.4 related to Aurora that may have contributed. From what I can tell releases 2.4.4+ are not working correctly for loadbalancing using the mariadb:aurora: connection string. 2.4.4 release notes: https://mariadb.com/kb/en/mariadb-connector-j-244-changelog/ https://mariadb.com/kb/en/mariadb-connector-j-releases/
          jpotts Justin Potter added a comment -

          Was anyone able to find a definitive answer to this question? Using "aurora", read-only connections, and a aurora read-only cluster endpoint result in an error on my end, as it can't connect to the master node under the read-only aurora endpoint.

          jpotts Justin Potter added a comment - Was anyone able to find a definitive answer to this question? Using "aurora", read-only connections, and a aurora read-only cluster endpoint result in an error on my end, as it can't connect to the master node under the read-only aurora endpoint.
          plebedev Peter Lebedev added a comment -

          I was able to get it working against ro endpoint by dropping aurora protocol from the connection string, e.g.
          "jdbc:mariadb://my-database.cluster-ro-cp12345dovz.us-east-1.rds.amazonaws.com:3306/my_schema"

          aurora protocol enables failover, and for this the driver needs to read the topology on the cluster, so it makes a query to a writer node that is not available via a -ro endpoint. In case of -ro end point, you do not really need any failover, as Aurora itself will re-route the calls to an available node in the cluster.

          Hope, it helps.

          plebedev Peter Lebedev added a comment - I was able to get it working against ro endpoint by dropping aurora protocol from the connection string, e.g. "jdbc:mariadb://my-database.cluster-ro-cp12345dovz.us-east-1.rds.amazonaws.com:3306/my_schema" aurora protocol enables failover, and for this the driver needs to read the topology on the cluster, so it makes a query to a writer node that is not available via a -ro endpoint. In case of -ro end point, you do not really need any failover, as Aurora itself will re-route the calls to an available node in the cluster. Hope, it helps.
          jpotts Justin Potter added a comment -

          Thanks Peter! The bit about needing to connect to the master to read back the topology to provide failover support makes sense.

          By dropping the protocol, it seems like the driver won't load balance reads across replicas, or the include ability to blacklist failed read replicas via "autoReconnect." Ideally, both load balanced reads and blacklisting of read replicas would be available under a read-only endpoint, but I suspect there is some detail I'm missing.

          jpotts Justin Potter added a comment - Thanks Peter! The bit about needing to connect to the master to read back the topology to provide failover support makes sense. By dropping the protocol, it seems like the driver won't load balance reads across replicas, or the include ability to blacklist failed read replicas via "autoReconnect." Ideally, both load balanced reads and blacklisting of read replicas would be available under a read-only endpoint, but I suspect there is some detail I'm missing.
          plebedev Peter Lebedev added a comment -

          The read-only end point sits in front of actual nodes, so the load balancing is happening on aurora side, at least this is how it was explained to me. Here is my understanding how it works but I could be wrong:

          If you have a writer node and two reader nodes, and use -ro endpoint, your connections will be balanced between reader nodes. If one reader node goes down, your connections will go to the node that is still up. If the second reader node goes down, the connections will go to the writer.

          I was not able to verify the load balancing between two reader nodes because by the time we upgraded to a newer mariaDB driver and changed our connection URL to use -ro end point,. we made application changes, so we only needed one reader node to handle the load.

          We do have a setup, though, when we do not have a reader node, so the -ro endpoint correctly routes connections to the writer.

          plebedev Peter Lebedev added a comment - The read-only end point sits in front of actual nodes, so the load balancing is happening on aurora side, at least this is how it was explained to me. Here is my understanding how it works but I could be wrong: If you have a writer node and two reader nodes, and use -ro endpoint, your connections will be balanced between reader nodes. If one reader node goes down, your connections will go to the node that is still up. If the second reader node goes down, the connections will go to the writer. I was not able to verify the load balancing between two reader nodes because by the time we upgraded to a newer mariaDB driver and changed our connection URL to use -ro end point,. we made application changes, so we only needed one reader node to handle the load. We do have a setup, though, when we do not have a reader node, so the -ro endpoint correctly routes connections to the writer.

          People

            diego dupin Diego Dupin
            andrewparmet Andrew Parmet
            Votes:
            2 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.