Uneven load balancing across cluster nodes

[b1tninja]

We notice that connections are not evenly distributed across our readers causing unbalanced loads and eventually leading to failures. After looking into how the nodes are picked, it seems dependent on the LAST_UPDATE_TIMESTAMP of the replicas, at the time of querying the topology. The problem with that is, the instances will often start or fail-over around the same time, and so order of the replicas LAST_UPDATE_TIMESTAMP is pretty consistently the same for all instances of the driver, which results in the various instances all connecting to the same reader node, which typically is the reader in the same AZ as the writer. (One replica always gets slammed)

Could changing RETRIEVE_TOPOLOGY_SQL to ORDER BY RAND() improve reliability/performance?
https://github.com/awslabs/aws-mysql-jdbc/blob/main/src/main/user-impl/java/com/mysql/cj/jdbc/ha/plugins/failover/AuroraTopologyService.java#L209

https://github.com/awslabs/aws-mysql-jdbc/blob/main/src/main/user-impl/java/com/mysql/cj/jdbc/ha/plugins/failover/AuroraTopologyService.java#L76

  static final String RETRIEVE_TOPOLOGY_SQL =
      "SELECT SERVER_ID, SESSION_ID, LAST_UPDATE_TIMESTAMP, REPLICA_LAG_IN_MILLISECONDS "
          + "FROM information_schema.replica_host_status "
          + "WHERE time_to_sec(timediff(now(), LAST_UPDATE_TIMESTAMP)) <= 300 " // 5 min
          + "ORDER BY LAST_UPDATE_TIMESTAMP DESC";

Or is there any other tunables / solutions to more evenly distribute load across the replicas?

[crystall-bitquill]

Hi @b1tninja,

Thanks for reaching out and raising this.

We do not have any load balancing solutions at the moment, but we'll keep you updated as we investigate.

Thank you for your patience!

[brunos-bq]

Hello @b1tninja,

Thanks for reaching out. We'd like to point you to the AWS Advanced JDBC Wrapper. While we'll continue supporting the AWS JDBC Driver for MySQL, all new feature development is being prioritized for the Wrapper. The Wrapper has been designed to leverage the existing community MySQL JDBC driver, which the AWS JDBC Driver for MySQL is based on. This design allows customers to get the latest additions from the community drivers while taking advantage of the AWS specific functionality that the Wrapper offers.

In this case, the Wrapper supports Read/Write Splitting and has support for various Load Balancing strategies which might help in your situation here and offer you greater flexibility. Documentation can be found here. We recommend that you give the Wrapper a try as it offers newer functionality.

Please let us know if you have any questions.

Thanks.