The cluster endpoint connects you to the primary instance for the DB cluster. You can perform both read and write operations using the cluster endpoint. The DB cluster can also have up to 15 Aurora Replicas that support read-only access to the data in the DB cluster.
The primary instance and each Aurora Replica has a unique endpoint that is independent of the cluster endpoint and allows you to connect to a specific DB instance in the cluster directly. The cluster endpoint always points to the primary instance. If the primary instance fails and is replaced, then the cluster endpoint points to the new primary instance.
View the replica lag
select server_id, if(session_id='MASTER_SESSION_ID','Writer','Reader') Server_Role, REPLICA_LAG_IN_MILLISECONDS from INFORMATION_SCHEMA.REPLICA_HOST_STATUS;
I’ve been trying to understand the endpoints in AWS so I recently raised a case and here is there response.
Please I would like to explain to you the Aurora Endpoints from [1]:
Cluster endpoint
A cluster endpoint is an endpoint for an Aurora DB cluster that connects to the current primary instance for that DB cluster. Each Aurora DB cluster has a cluster endpoint and one primary instance.
The cluster endpoint provides failover support for read/write connections to the DB cluster. Use the cluster endpoint for all write operations on the DB cluster, including inserts, updates, deletes, and data definition language (DDL) changes. You can also use the cluster endpoint for read operations, such as queries.
If the current primary instance of a DB cluster fails, Aurora automatically fails over to a new primary instance. During a failover, the DB cluster continues to serve connection requests to the cluster endpoint from the new primary instance, with minimal interruption of service.
Reader endpoint
A reader endpoint is 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. If there is more than one Aurora Replica, the reader endpoint directs each connection request to one of the Aurora Replicas in a round robin way.
The reader endpoint provides load balancing support for read-only connections to the DB cluster. Use the reader endpoint for read operations, such as queries. You can’t use the reader endpoint for write operations.
Instance endpoint
An instance endpoint is 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. So, there is one instance endpoint for the current primary instance of the DB cluster, and there is one instance endpoint for each of the Aurora Replicas in the DB cluster.
From your account, I can see you have an Aurora DB cluster called “apidbaup003-cluster” with a writer node “apidbaup003” and a reader node “apidbaup003-2b”. This cluster have the below endpoints. I assume this is the cluster you are referring to, please correct me if am wrong as the cluster name is not mentioned in the case and this is the only cluster in this account.
Cluster (Writer) Endpoint: apidbaup3-cluster.cluster-xxxxxxxx.ap-southeast-2.rds.amazonaws.com
Cluster (Reader) Endpoint: apidbaup3-cluster.cluster-ro-xxxxxxxx.ap-southeast-2.rds.amazonaws.com
Writer Instance End-point: apidbaup3.xxxxxxxx.ap-southeast-2.rds.amazonaws.com
Reader instance End-point: apidbaup3-2b.xxxxxxxx.ap-southeast-2.rds.amazonaws.com
Here are reasons why connections can go to the reader instance:
1. If the cluster reader endpoint is used by clients/applications.
2. If the instance specific endpoint of the current reader is used by clients/applications.
Q: How does the cluster endpoint determine a connection to the reader?
==========================================================
The DB cluster distributes connection requests to the reader endpoint among available Aurora Replicas. If the DB cluster contains only a primary (writer) instance without a reader, the reader endpoint serves connection requests from the primary instance. If an Aurora Replica is created for that DB cluster, the reader endpoint continues to serve connection requests to the reader endpoint from the new Aurora Replica, with minimal interruption in service. In your case, the cluster has two instances so the Cluster Writer Endpoint will always point to the current writer while the cluster read only endpoint will always point to the current reader instance(s).
Also, regarding the sleeping connections, please confirm from PROCESSLIST how long those threads have been in sleep state. If the duration changes from time to time, then it shows that the threads are performing tasks. I took some metric from the reader node to show evidence of activities on the reader node.
Reference:
[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Overview.html#Aurora.Overview.Endpoints