ProxySQL Series: Amazon Aurora (RDS) Read-Write Split.

Dec 27, 2018

Mins to Read

All

Thinking of migrating MySQL databases to Aurora (RDS) and want to optimize read/write performance? This guide explores how ProxySQL can automate read-write splitting, reducing complexity and downtime.

We are going to see how to implement Proxysql for Amazon Aurora RDS, this would be next in series of ProxySQL blogs. Below is the list of our previous blogs on ProxySQL which provides deeper insights based on different use cases and different architecture.

ProxySQL 2.0 comes with native support for AWS Aurora, and also with many exciting new features. We will be exploring it later in upcoming blogs. Amazon Aurora is highly performant and fault tolerant MySQL Compatible DBAAS provided by Amazon. To know more about Aurora you can check our previous blog here

For the purpose of this blog I have used Aurora cluster of size 2 (1 Master + 1 Replica), Aurora is not available within free tier usage of AWS, the smallest supported instance would be t2.small (1VCPU & 2GB RAM).

Now let’s see about the end-point provided by AWS while provisioning an Aurora Cluster.

Aurora Endpoints:

Endpoints are the connection URI’s provide by AWS to connect to the Aurora database. Listed below the endpoints provided for an Aurora cluster.

Cluster Endpoint
Reader Endpoint
Instance Endpoint

Cluster Endpoint:

An endpoint for an Aurora DB cluster that connects to the current primary instance for that DB cluster. It provides failover support for read/write connections to the DB cluster. If the current primary instance of a DB cluster fails, Aurora automatically fails over to a new primary instance

Reader Endpoint:

An endpoint for an Aurora DB cluster that connects to one of the available Aurora Replicas for that DB cluster. Each Aurora DB cluster has a reader endpoint. The reader endpoint provides load balancing support for read-only connections to the DB cluster.

Instance Endpoint:

An endpoint for a DB instance in an Aurora DB cluster that connects to that specific DB instance. Each DB instance in a DB cluster, regardless of instance type, has its own unique instance endpoint

Among this different end-point, we will be using the “Instance Endpoint” ie., individual end-point in ProxySQL config.

The problem here is application should have read and writes split at the application layer. So that it can use the Reader and writer endpoints efficiently. But if a user migrates to Aurora for scalability then we need to have an intelligent proxy like Maxscale / ProxySQL, to have on the fly Read-Write split with almost Zero application level changes.

Now let’s see the configuration of ProxySQL for Aurora.

ProxySQL Version : proxysql-rc2-2.0.0-1-centos7.x86_64.rpm

OS version : Centos 7.3

Core : 1

RAM : 1G

Aurora Version: MySQL 5.7 compatible(aurora_version-2.03.1)

Instance End points: aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com, aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com

Below is the snap of my cluster

With the above end-points, I have the below host_group in ProxySQL

10(Writer Group)
11(Reader Group)

Adding servers to ProxySQL

Writer group:

 
ProxySQL>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com',3306);

Reader group:

 
ProxySQL>INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_replication_lag) VALUES (11,'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com',3306,1);
ProxySQL>load mysql servers to runtime;
ProxySQL>save mysql servers to disk;

Am inducing a replication lag threshold of 1secs, since its a cluster with a promised replication lag of less than 50Ms for query routing, This can be changed based on your use case and application criticalness.

Adding users:

You can have all your application user embedded into ProxySQL, Here am adding a single user for the purpose of the demo

 
ProxySQL>INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('mydbops','45264726',10);

ProxySQL>load mysql users to runtime;
ProxySQL>save mysql users to disk;

Monitor user:

ProxySQL needs a monitoring user to ping the MySQL server, make replication checks, make sure to have “replication client” privilege for monitor user

 
ProxySQL>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

ProxySQL>UPDATE global_variables SET variable_value='KabileshKabi' WHERE variable_name='mysql-monitor_password';

ProxySQL>load mysql variables to runtime;

ProxySQL>save mysql variables to disk;

You can proceed to check the monitor status from ProxySQL by running the below command.

 
ProxySQL> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;

+--------------------------------------------------------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
| aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126658026840 | 1634 | NULL |
| aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126658024189 | 737 | NULL |
| aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126648026685 | 1419 | NULL |
+--------------------------------------------------------------+------+------------------+----------------------+------------+

Query Rules:

Am herewith adding the default query for RW-Split as below.

 
ProxySQL>INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE',10,0);
ProxySQL>INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (2,1,'^SELECT.*',11,1);

Now the configuration for Read-Write splitting is pretty much done.

How ProxySQL Monitors Aurora:

ProxySQL 2.0 comes with native monitoring for Aurora, by checking the variable “Innodb_read_only”, to determine the reader/Writer for the individual nodes, This info of check has to be added to the table “mysql_replication_hostgroups” in ProxySQL

Below is the structure of the table:

Table: mysql_replication_hostgroups

 
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

Here am making the check based on the hostgroup on the “INNODB_READ_ONLY” variable for each node in the hostgroup as below.

 
insert into mysql_replication_hostgroups values (10,11,'innodb_read_only','This is aurora cluster');

Now our setup of read-write split for Aurora is completely ready to accept traffic. We have made a read-write split with failover.

Failover handling With ProxySQL

I was just curious to note the fail-over and Switch-over with Aurora and ProxySQL to regroup the host group. So I induced a manual failover from the console as below

Before Fail-over below is the server status and host_group.

 
+--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer |
+--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 10 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 1 | 0 | 0 | | 140686935296000 |
| 11 | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | 140686967069728 |
| 11 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 1 | 0 | 0 | | 140686967652640 |
+--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+

The ProxySQL Monitor makes checks within spans of few milliseconds as below. When it finds a consecutive fail of checks for three times, it makes the fail-over.

 
2018-12-18 10:27:02 MySQL_Monitor.cpp:657:monitor_read_only_thread(): [ERROR] Timeout on read_only check for aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 after 7ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on 'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com' (115).

2018-12-18 10:27:03 MySQL_Monitor.cpp:657:monitor_read_only_thread(): [ERROR] Timeout on read_only check for aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 after 2ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on 'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com' (115).

2018-12-18 10:27:03 MySQL_Monitor.cpp:803:monitor_read_only_thread(): [ERROR] Server aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 missed 3 read_only checks. Assuming read_only=1

And next, it proceeds to remove the server from the writer group ‘10’ as below,

 
2018-12-18 10:27:03 MySQL_HostGroups_Manager.cpp:1107:commit(): [WARNING] Removed server at address 140686935296000, hostgroup 10, address aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them

You can see a graceful shifting of servers across the host groups 10 as below.

 
+-----------+--------------------------------------------------------------+----------+--------------+----------
+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+-
-----------+
| hostgroup | srv_host | srv_port | status | ConnUsed
| ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv |
Latency_us |
+-----------+--------------------------------------------------------------+----------+--------------+----------
+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+-
-----------+
| 10 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | OFFLINE_HARD | 0
| 0 | 7 | 0 | 7 | 256100 | 0 | 15091179 | 0 |
1157 |
| 10 | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | ONLINE | 0
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
1695 |
| 11 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | ONLINE
| 0
| 3 | 22 | 0 | 8 | 553552 | 0 | 17702113 | 1082107502 |
1157 |
+----------+--------+---------+-------------+---------+-------------------+-----------+-----+--------------------

Advantages of ProxySQL with Aurora

On the wire query caching, With TTL
Connection Multiplexing making efficient connection usage.
Reduced CPU usage.
Automated failover with Aurora end-points and query routing.

We are exploring more, See you soon with my new exciting upcoming blogs

Thanks for your continued support and Time !!

Need expert help with your MySQL to Aurora migration? Mydbops, our open-source database management service, offers comprehensive MySQL support, including ProxySQL implementation and performance tuning. Let our team of database specialists ensure a smooth and efficient migration to the cloud. Contact Mydbops today!

Image Courtesy: Jouni Rajala on Unsplash

No items found.