The Aurora Limitless cluster provides a writer and reader endpoint:
There's also a endpoint for the shard group:
Both connect to a router within the shard group. They are simple DNS entries from Route53.
If you try to connect to the postgres database, you will get an error:
psql: error: connection to server at "limitless.cluster-cvlvfe1jv6n5.eu-west-1.rds.amazonaws.com" (34.255.43.73), port 5432 failed: FATAL: invalid connection request to non-limitless database "postgres" by user "postgres".
DETAIL: Only connections to limitless databases is allowed.
You must connect to the postgres_limitless database:
postgres_limitless=> \c
You are now connected to database "postgres_limitless" as user "postgres".
postgres_limitless=> \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
--------------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-------------------------------------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
postgres_limitless | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
rdsadmin | rdsadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | rdsadmin=CTc/rdsadmin +
| | | | | | | | rds_aurora_limitless_metadata_admin=c/rdsadmin +
| | | | | | | | rds_aurora_limitless_heat_mgmt_admin=c/rdsadmin
rdsadmin_limitless | rdsadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | rdsadmin | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/rdsadmin +
| | | | | | | | rdsadmin=CTc/rdsadmin
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | postgres=CTc/postgres +
| | | | | | | | =c/postgres
(7 rows)
The endpoint will connect to any router and they may be in different availability zones:
postgres_limitless=> select inet_server_addr(), rds_aurora.limitless_instance_az(); \c
inet_server_addr | limitless_instance_az
------------------+-----------------------
172.31.21.176 | eu-west-1b
(1 row)
You are now connected to database "postgres_limitless" as user "postgres".
postgres_limitless=> select inet_server_addr(), rds_aurora.limitless_instance_az(); \c
inet_server_addr | limitless_instance_az
------------------+-----------------------
172.31.21.176 | eu-west-1b
(1 row)
You are now connected to database "postgres_limitless" as user "postgres".
postgres_limitless=> select inet_server_addr(), rds_aurora.limitless_instance_az(); \c
inet_server_addr | limitless_instance_az
------------------+-----------------------
172.31.13.141 | eu-west-1a
(1 row)
You are now connected to database "postgres_limitless" as user "postgres".
You will see that the load balancing is not well distributed. If possible, it is preferred to use Limitless Connection Plugin.
The rds_aurora view limitless_subclusters lists the nodes in the cluster (subclusters) with their type (router or shard), and limitless_stat_activity is a global view of pg_stat_activity from all nodes:
postgres_limitless=> select * from rds_aurora.limitless_subclusters order by 1;
subcluster_id | subcluster_type
---------------+-----------------
2 | router
3 | router
4 | shard
5 | shard
(4 rows)
postgres_limitless=> select count(*)
, subcluster_type, datname,usename,backend_type
, string_agg(distinct subcluster_id,',') subcluster_ids
from rds_aurora.limitless_stat_activity
group by
subcluster_type, datname,usename,backend_type
order by 2,1 desc;
count | subcluster_type | datname | usename | backend_type | subcluster_ids
-------+-----------------+--------------------+-------------------------------------+------------------------------------------+----------------
20 | router | postgres_limitless | rds_aurora_limitless_dtx_admin | client backend | 2,3
10 | router | rdsadmin | rdsadmin | client backend | 2,3
3 | router | postgres_limitless | postgres | client backend | 2,3
2 | router | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless nodes info cleanup | 2,3
2 | router | postgres_limitless | rds_aurora_limitless_metadata_admin | Aurora limitless task dispatcher process | 2,3
2 | router | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless vacuum cutoff worker | 2,3
2 | router | postgres_limitless | rdsadmin | aurora limitless cron launcher | 2,3
2 | router | postgres_limitless | rdsadmin | client backend | 2,3
2 | router | | rdsadmin | aurora limitless nodes file watcher | 2,3
2 | router | | rdsadmin | logical replication launcher | 2,3
2 | router | | | aurora resource monitoring | 2,3
2 | router | | | aurora runtime process | 2,3
2 | router | | | autovacuum launcher | 2,3
2 | router | | | background writer | 2,3
2 | router | | | checkpointer | 2,3
2 | router | | | walwriter | 2,3
2 | router | rdsadmin_limitless | rds_aurora_limitless_metadata_admin | client backend | 2,3
2 | router | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless cron job watcher | 2,3
2 | router | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless database cleanup | 2,3
20 | shard | postgres_limitless | rds_aurora_limitless_dtx_admin | client backend | 4,5
16 | shard | rdsadmin_limitless | rds_aurora_limitless_metadata_admin | client backend | 4,5
9 | shard | postgres_limitless | postgres | client backend | 4,5
9 | shard | rdsadmin | rdsadmin | client backend | 4,5
8 | shard | demo | rds_aurora_limitless_metadata_admin | client backend | 4
2 | shard | | | walwriter | 4,5
2 | shard | postgres_limitless | rds_aurora_limitless_metadata_admin | Aurora limitless task dispatcher process | 4,5
2 | shard | postgres_limitless | rdsadmin | aurora limitless cron launcher | 4,5
2 | shard | postgres_limitless | rdsadmin | client backend | 4,5
2 | shard | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless database cleanup | 4,5
2 | shard | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless cron job watcher | 4,5
2 | shard | | rdsadmin | aurora limitless nodes file watcher | 4,5
2 | shard | | rdsadmin | logical replication launcher | 4,5
2 | shard | | | aurora resource monitoring | 4,5
2 | shard | | | aurora runtime process | 4,5
2 | shard | | | autovacuum launcher | 4,5
2 | shard | | | background writer | 4,5
2 | shard | | | checkpointer | 4,5
1 | shard | postgres_limitless | rds_aurora_limitless_metadata_admin | aurora limitless vacuum cutoff worker | 4
(38 rows)
All the views and functions are provided by the aurora_limitless_fdw extension.
The statistics from cross-node requests also give an idea of the shard group topology:
postgres_limitless=> select orig_subcluster,orig_type,orig_instance_az,dest_subcluster,dest_type,dest_instance_az
,latency_us, same_az_requests+cross_az_requests requests
from rds_aurora.limitless_stat_subclusters
natural join (select subcluster_id as orig_subcluster, subcluster_type as orig_type from rds_aurora.limitless_subclusters)
natural join (select subcluster_id as dest_subcluster, subcluster_type as dest_type from rds_aurora.limitless_subclusters)
order by orig_instance_az=dest_instance_az, orig_type=dest_type, latency_us desc
;
orig_subcluster | orig_type | orig_instance_az | dest_subcluster | dest_type | dest_instance_az | latency_us | requests
-----------------+-----------+------------------+-----------------+-----------+------------------+------------+----------
3 | router | eu-west-1b | 4 | shard | eu-west-1a | 1773 | 426517
2 | router | eu-west-1a | 5 | shard | eu-west-1b | 966 | 238554
2 | router | eu-west-1a | 3 | router | eu-west-1b | 1284 | 104705
3 | router | eu-west-1b | 2 | router | eu-west-1a | 913 | 103991
2 | router | eu-west-1a | 4 | shard | eu-west-1a | 4105 | 384638
3 | router | eu-west-1b | 5 | shard | eu-west-1b | 473 | 279003
(6 rows)
In the next post, I'll create a sharded table and see how it is distributed to the shard nodes.
Top comments (0)