Utilizing Proxy SQL and AWS Aurora for splitting read and write queries
We had to transfer extensive data from our legacy application to a new application that already has existing users. Following the migration, our new application experienced a substantial increase in both user base and data. This led to performance challenges in our system, causing the application to slow down and occasionally experience downtime as a single database (AWS Aurora MySQL 5.7) handles all the traffic. Additionally, we have noted that the prevalence of read queries surpasses that of write queries, with a ratio of 10:90. Furthermore, the CPU usage consistently reaches 100% most of the time.
How we solved the problem
The quick solution was implementation of read replicas. We have two potential pathways; one involves separating read and write queries at the application level, while the other approach is introducing Proxy SQL to autonomously segregate read and write queries without necessitating any changes to the application code. After careful consideration, we have opted for the Proxy SQL approach due to the substantial development time required for the former method.
Proxy SQL Setup
The below diagram illustrates Aurora Engine, with writer and reader cluster endpoints using Proxy SQL, which automatically directs write traffic to the cluster endpoint and read traffic to the reader endpoint.
What is Proxy SQL?
Proxy SQL is a high-performance proxy server for the MySQL and MariaDB protocols. It is placed between the application and the database engine, and it will help us achieve the application performance while also reducing the cost of cloud database engines.
You can use Proxy SQL to:
1. Improve the application’s performance.
2. Database High availability.
3. Scalability of Aurora Reader Instance.
Note: By default, Proxy SQL binds with Ports 6032 and 6033. 6032 is the admin port, and 6033 is the one that accepts incoming connections from clients.
For more information on Proxy SQL, See https://proxysql.com/.
Let us now examine the configuration of Proxy SQL for Aurora
Adding Servers to Proxy SQL
Commands to play with Proxy SQL service
service proxy SQL start - start the service.
service proxy SQL status - check the status of the service.
service proxy SQL restart - Reload once changes are made to config.First, connect to the Proxy SQL managed database:
mysql -u admin -p -h 127.0.0.1 -P 6032 - prompt='ProxySQLAdmin>'Default password will be: admin
Follow the below steps to configure Proxy SQL.
1. Writer Group
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'<AURORA_WRITER_ENDPOINT>',3306);2. Reader Group
INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_replication_lag) VALUES (11,'<AURORA_READER_ENDPOINT',3306,1);
load mysql servers to runtime;
save mysql servers to disk;3. Adding Users
You can have all your application users embedded in Proxy SQL.
INSERT INTO mysql_users (username,password,default_hostgroup)VALUES('<RDS_MYSQL_AURORA_ADMIN_USRENAME>','<RDS_MYSQL_AURORA_ADMIN_PASSWORD>',10);
INSERT INTO mysql_users (username,password,default_hostgroup)VALUES('<APPLICATION_USERNAME>','<APPLICATION_USER_APSSWORD>',10);
load mysql users to runtime;
save mysql users to disk;4. Monitor User
Proxy SQL needs a monitoring user to ping the MySQL server, make replication checks, and make sure to have “replication client” privileges for the monitoring user.
UPDATE global_variables SET variable_value='<RDS_MYSQL_AURORA_ADMIN_USRENAME>' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='<RDS_MYSQL_AURORA_ADMIN_PASSWORD>' WHERE variable_name='mysql-monitor_password';
load mysql variables to runtime;
save mysql variables to disk;You can proceed to check the monitor status from Proxy SQL by running the below command.
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;5. DNS Cache
Proxy SQL DNS caching allows storing DNS records locally and reusing them in the future, eliminating the need for new DNS queries.
Every DNS record has a time-to-live (TTL). TTL specifies the expiration time of the record and is managed using the mysql-monitor_local_dns_cache_ttl variable.
DNS records are checked after every interval, and expired records are refreshed by issuing a DNS query. The interval value is specified in the mysql-monitor_local_dns_cache_refresh_interval variable.
Other than that, cached records are checked when mysql_servers and proxysql_servers tables are updated.
If the domain contains multiple mapped IPs, the load is balanced among them in a round-robin fashion on the socket connection.
They represent the following:
· mysql-monitor_local_dns_cache_ttl : 5000 (ms) — TTL time-to-live per DNS record.
· mysql-monitor_local_dns_cache_refresh_interval : 5000 (ms) — The interval at which the Monitor module of the proxy will check ‘mysql_servers’ and ‘proxysql_servers’ tables for new records and send DNS queries for expired DNS records.
UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-monitor_local_dns_cache_ttl';
UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-monitor_local_dns_cache_refresh_interval';
load mysql variables to runtime;
save mysql variables to disk;6. Query Rules
Add the default query for RW-Split as given below:
INSERT INTO mysql_query_rules (rule_id, active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE',10,0);
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^SELECT.*',11,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Why proxy SQL?
1. When Compared with all the other tools, Proxy SQL has more features, documentation and easy to manage.
2. Its an Open-Source Tool.
3. From a cost perspective, you end up spending only for hosting it and no other cost is involved.
4. It comes with easy installation, setup, and it’s a lightweight service.
Challenges Faced
Although we did not encounter any challenges or issues during the process of setting up and applying the Proxy SQL configuration, the following challenges arose after deploying it into live applications.
1. As we are all aware, there will be a replication lag between the Aurora writer and the reader, our applications were not able to tolerate that lag.
2. Proxy SQL Cluster Mode (High Availability).
To avoid a single point of failure. We implemented AWS auto-scaling on a proxy SQL server using an AWS load balancer. But again, DNS cache plays a role here at the load balancer level. But when database busts happen from database clients (applications), database clients keep previous connections cached and connections sent to a single reader always. Due to that, Proxy SQL Cluster Mode did not work as expected.
Resolution
1. A delay of 100 milliseconds has been added between save and select operations in our application to accommodate the replication lag. This will resolve the replication lag issue.
2. With the help of Route 53 failover routing, we were able to manage a single point of failure.
This tool helps with the following:
- Load Balancing.
- Query Routing.
- High Availability.
- Query Caching.
- Reduced number of connections and overhead to the backend database.
- Control over database connections.
- Collect metrics on all database connections and monitor it.
However, the tool:
1. Can cause unexpected behavior in application logic.
2. Supports only MYSQL and MariaDB protocols.
3. Causes Network Overhead as it is positioned between the application and database thus resulting in a single point of failure.
Conclusion
Drawing from our experience, the implementation of Proxy SQL proved instrumental in overcoming database bottlenecks and optimizing costs. Through this approach, we have determined it to be the most effective with minimal development team efforts, resulting in a notable enhancement in our application performance. Specifically, we leveraged one key advantage of Proxy SQL — its ability to seamlessly separate read and write queries.
