dev-resources.site

for different kinds of informations.

Aurora Limitless - Connection

Published at

11/24/2024

Categories

4 categories in total

Author

12 person written this

franckpachot

open

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 and the cluster endpoints are synonyms for the shard group endpoints.

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.

aurora Article's

30 articles in total

How to generate a Aurora Postgresql cluster with all auto explain enabled