How to Route Aurora MySQL Read Traffic Across Dedicated Reader Groups (Reporting vs. Transactional) with ProxySQL?

[kamleshmsharma]

We’re using Amazon Aurora MySQL with two distinct read workloads that need isolation and uniform load balancing connections / queries across reader instances in these respective reader groups -

Reporting queries: Long-running, analytical. Dedicated reader group (custom endpoint: reporting-readers).

Transactional reads: Low-latency, user-facing. Separate reader group (transactional-readers).

Our goal is to:

1. Uniformly load balance within each reader group (no DNS round-robin).
2. Route queries to the correct reader group without application code changes (e.g., no setReadOnly).

Attempted Solutions

1. AWS JDBC Driver: Failed to load balance across custom endpoints.
2. RDS Proxy: Doesn’t improve Aurora’s DNS-based rotation. The initial connections established in the application pool get assigned to any 1 of reader instance. Even load of concurrent requests to the app that causes new connections to be established again gets assigned to any 1 of the readers.

ProxySQL Approach we want to try
We plan to deploy ProxySQL to:

Define two hostgroups:

hostgroup 10 → Reporting readers (IPs: reporting-reader-1:3306, etc.).
hostgroup 20 → Transactional readers (transactional-reader-1:3306, etc.).

Route queries using:

SQL comments (e.g., /* REPORTING */) or
Schema/username rules.

Questions

1. Is this the right architecture for our use case? Can ProxySQL load balance say in a round-robin fashion when we establish the initial connection pool for reporting across the readers available in the reporting host-group ?
2. How to configure query rules to ensure:
   Reporting queries only use hostgroup 10.
   Transactional queries only use hostgroup 20.
3. How to handle reader failover (e.g., Aurora auto-scaling readers)?

Performance impact: Any tuning tips for high concurrency (1K+ connections)?

[kamleshmsharma]

@renecannao Please advice for the above question