ProxySQL & Amazon Aurora

Polyglot Projects ProxySQL
1

I have deployed proxysql in front of our RDS Aurora MySQL database cluster which has reader autoscaling set up. We’re using ProxySQL to mirror traffic against MySQL8 before we upgrade.

We have an issue where some reader instances are getting a lot more traffic than others.

We set it up using the rds cluster endpoint name as the hostname

INSERT INTO mysql_servers (hostname,hostgroup_id,port) VALUES ('${ClusterEndpoint}',10,3306);
INSERT INTO mysql_servers (hostname,hostgroup_id,port) VALUES ('${ReaderEndpoint}',20,3306);

Initially new readers would get no traffic

We changed the following MySQL Variables

set mysql-connpoll_reset_queue_length=0;
set mysql-reset_connection_algorithm=1;

Which definitely helped but it’s still not as balanced as it was before proxysql.

We tried editing mysql-connection_max_age_ms but this resulted in a lot of connection reset by peer errors in our apps.

Are there any other settings we could look at tweaking?

2

What’s in proxysql’s log file when new readers are created?

3

There were no errors or logs of note when readers were added.

In the end I swapped to removing autoscaling group and using the instance endpoints directly. This is a short term fix

4

That sounds like ProxySQL had not detected the new servers. Every time the mysql_servers table is modified, the contents of the table are dumped into the log.

On one of the RDS readers, check the contents of information_schema.replica_host_status after autoscaling adds/removes a host. Does this table change?

Verify that data with proxysql monitor.mysql_server_aws_aurora_log table.

5

Would ProxySQL be aware of the replicas being created if we are using the cluster reader endpoint directly in mysql_hosts?

6

According to the docs, it does not matter what endpoint you use. You can add just 1 reader endpoint, and all will be discovered:

When an Aurora cluster is enabled, Monitor connects to all the hosts in the cluster (in rotation), querying one node every check_interval_ms milliseconds. Monitor will retrieve cluster information from Aurora table information_schema.replica_host_status .

7

We are not using mysql_aws_aurora_hostgroups table but mysql_servers for defining the servers. The AWS docs specify mysql_servers, so I presume the docs were written before mysql_aws_aurora_hostgroups was launched.

8

Yes, you should be using the official docs from ProxySQL as they contain the most up-to-date information.