Scale with Maxscale part-4 (Amazon Aurora)

This is part-4 of the Maxscale Blog series

  1. Maxscale and Galera
  2. Maxscale Basic Administration
  3. Maxscale for Replication

Maxscale started supporting Amazon Aurora lately from its version 2.1 which comes with a BSL license, we are good until we use only 3 nodes, Amazon Aurora (Our Previous blog ) is a brilliant technology built by AWS team which imitates features of MySQL, Aurora is getting better and better with regards to scaling and feature with each release of its Aurora engine current version is 1.16 (at time of writing ) , Aurora architecture and features can be seen here. In this blog i will be explaining Maxscale deployment for Aurora.

Maxscale version : maxscale-2.1.13-1.x86_64
OS version              : Amazon Linux AMI 2016.09
Cores                        : 4
RAM                         : 8GB

Note: Make sure to have the EC2 machine with in same Availability Zone ( AZ ) where the Aurora resides which could greatly help in reducing the network latency.

For the purpose of this blog i have used 3 instances of Aurora (1Master + 2 Read Replica).

Now lets speaks about the end-points that Aurora provides.

Aurora Endpoints:

Endpoints are the correction URI’s provide by AWS to connect to the Aurora database. Listed below the endpoints provided by Aurora.

  •  Cluster Endpoint
  •  Reader Endpoint
  •  Instance Endpoint

Cluster Endpoint:

An endpoint for a Aurora DB cluster that connects to the current primary instance for that DB cluster,The cluster endpoint 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 these different end-point we will be using the “Instance Endpoint” ie., individual end-point in maxscale config.

The problem here is application should have reads and writes split at application layer. So that it can use the Reader and writer end points efficiently. But if a user migrates to Aurora for scalability then we need a intelligent proxy like Maxscale / ProxySQL. Currently Maxscale has inbuilt support for Aurora.

How is Aurora Monitored by Maxscale?

Maxscale uses a special monitor module called ‘auroramon’, since the Aurora does not follow standard MySQL replication protocol for replicating data to its under replica.

How ‘auroramon’ identifies master and replica from ‘Instance Endpoint’ ?

Each node inside the aurora cluster( in our use case its 1master + 2 Replica), has a aurora_server_id (@@aurora_server_id), which is a unique identifier for each instance/node.

And also Aurora all the relevant information about replication including the aurora_server_id inside the table information_schema.replica_host_status, Below is the structure of the table.

+----------------------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------------+---------------------+------+-----+---------------------+-------+
| SERVER_ID | varchar(100) | NO | | | |
| SESSION_ID | varchar(100) | NO | | | |
| IOPS | int(10) unsigned | NO | | 0 | |
| READ_IOS | bigint(10) unsigned | NO | | 0 | |
| PENDING_READ_IOS | int(10) unsigned | NO | | 0 | |
| CPU | double | NO | | 0 | |
| DURABLE_LSN | bigint(20) unsigned | NO | | 0 | |
| ACTIVE_LSN | bigint(20) unsigned | NO | | 0 | |
| LAST_TRANSPORT_ERROR | int(10) | NO | | 0 | |
| LAST_ERROR_TIMESTAMP | datetime | NO | | 0000-00-00 00:00:00 | |
| LAST_UPDATE_TIMESTAMP | datetime | NO | | 0000-00-00 00:00:00 | |
| MASTER_SLAVE_LATENCY_IN_MICROSECONDS | bigint(10) unsigned | NO | | 0 | |
| REPLICA_LAG_IN_MILLISECONDS | double | NO | | 0 | |
| LOG_STREAM_SPEED_IN_KiB_PER_SECOND | double | NO | | 0 | |
| LOG_BUFFER_SEQUENCE_NUMBER | bigint(10) unsigned | NO | | 0 | |
| IS_CURRENT | tinyint(1) unsigned | NO | | 0 | |
| OLDEST_READ_VIEW_TRX_ID | bigint(10) unsigned | NO | | 0 | |
| OLDEST_READ_VIEW_LSN | bigint(10) unsigned | NO | | 0 | |
| HIGHEST_LSN_RECEIVED | bigint(1) unsigned | NO | | 0 | |
| CURRENT_READ_POINT | bigint(1) unsigned | NO | | 0 | |
| CURRENT_REPLAY_LATENCY_IN_MICROSECONDS | bigint(1) unsigned | NO | | 0 | |
| AVERAGE_REPLAY_LATENCY_IN_MICROSECONDS | bigint(1) unsigned | NO | | 0 | |
| MAX_REPLAY_LATENCY_IN_MICROSECONDS | bigint(1) unsigned | NO | | 0 | |
+----------------------------------------+---------------------+------+-----+---------------------+-------+

The above structure of table is subjected to change as per Aurora version.

Another important variable to check is the ‘SESSION_ID’ which is a unique identifier value for replica nodes, but for the master server it will defined as ‘MASTER_SESSION_ID.

Based on these two variables the master and replica is segregated by maxscale monitor and which sets the status flag based on which the router sends the traffic to nodes.

Now let get into the configuration part, of maxscale for Aurora.

Installation and administration has been covered in previous blogs part1 and part2.

Below is the Aurora monitor module configuration.

[Aurora-Monitor]
type=monitor
module=auroramon
servers=nodeA,nodeB,nodeC
user=USERXXXX
passwd=9BE2F1F3B182F061CEA59799AA758D1DAE6B8ADF32845517C13EA0122A5BA7F5
monitor_interval=2500

Below is the server, definitions, i have named each node as nodeA, nodeB & nodeC and have provided instance end-point.

[nodeA]
type=server
address=prodtest-rr-tier1XX.xxxxxx.us-east-1.rds.amazonaws.com
port=3306
protocol=MySQLBackend
persistpoolmax=200
persistmaxtime=3600

[nodeB]
type=server
address=proddtest-rr-tier1YY.xxxxxxx.us-east-1.rds.amazonaws.com
port=3306
protocol=MySQLBackend
persistpoolmax=200
persistmaxtime=3600

[nodeC]
type=server
address=proddtest-rr-tier1ZZ.XXXXXXX.us-east-1.rds.amazonaws.com
port=3306
protocol=MySQLBackend
persistpoolmax=200
persistmaxtime=3600

In the above server i have enabled connection pool by defining persistpoolmax and persistmaxtime, this would greatly help in overcoming restart of instance due to memory handling with aurora.

Once all the config is done you can reload Maxscale config / restart Maxscale service.

-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status 
-------------------+-----------------+-------+-------------+--------------------
nodeA | proddtest-rr-tier1XX.xxxxxx.us-east-1.rds.amazonaws.com| 3306 | 0 | Slave, Running
nodeB | proddtest-rr-tier1YY.xxxxxxx.us-east-1.rds.amazonaws.com | 3306 | 0 | Slave, Running
nodeC | proddtest-rr-tier1ZZ.XXXXXXX.us-east-1.rds.amazonaws.com | 3306 | 0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------

 

Maxscale_Aurora.jpg
Architecture

 

Below is the method to check the RW split from command line, by default a query inside ‘start transaction;’ goes to the master node, Since am connecting directly from max scale node i have used local socket, you can also use IP and Port instead.

[root@ip-XXXXXX mydbops]# mysql -u mydbops -pXXXXXX -S /tmp/ClusterMaster -e "show global variables like '%aurora_server_id%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| aurora_server_id | proddtest-rr-tier1ZZ       |
+------------------+----------------------------+

[root@ip-XXXXXXX mydbops]# mysql -u mydbops -pXXXXX -S /tmp/ClusterMaster -e "start transaction;show global variables like '%aurora_server_id%';commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| aurora_server_id | proddtest-rr-tier1XX       |
+------------------+----------------------------+

To monitor the percentage of RW split between master & replica and configuration stats as below.

MaxScale> show service "Splitter Service"
Service:                             Splitter Service
Router:                              readwritesplit
State:                               Started
use_sql_variables_in:      all
slave_selection_criteria:  LEAST_BEHIND_MASTER
master_failure_mode:       fail_instantly
max_slave_replication_lag: 30
retry_failed_reads:        true
strict_multi_stmt:         true
strict_sp_calls:           false
disable_sescmd_history:    true
max_sescmd_history:        0
master_accept_reads:       true

Number of router sessions:           5
Current no. of router sessions:      2
Number of queries forwarded:          20
Number of queries forwarded to master:8 (40.00%)
Number of queries forwarded to slave: 12 (60.00%)
Number of queries forwarded to all:   4 (20.00%)
Started:                             Mon Feb 19 15:53:48 2018
Root user access:                    Disabled
Backend databases:

[prodtest-rr-tier1XX.xxxxxx.us-east-1.rds.amazonaws.com]:3306    Protocol: MySQLBackend    Name: nodeA
[proddtest-rr-tier1YY.xxxxxxx.us-east-1.rds.amazonaws.com]:3306    Protocol: MySQLBackend    Name: nodeB
[proddtest-rr-tier1ZZ.XXXXXXX.us-east-1.rds.amazonaws.com]:3306    Protocol: MySQLBackend    Name: nodeC

Total connections:                   7
Currently connected:                 2

 

Now you can start using Maxscale and scale your queries for Aurora cluster. It is becomes no more mandatory for the segregating the reads and writes queries for Aurora.

 

Advertisements

2 thoughts on “Scale with Maxscale part-4 (Amazon Aurora)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s