How to access reader endpoint correctly?

[jiyanhbo]

I followed the code sampler to execute read & write sql statement by calling reader & write endpoints. However, the read requests are not split evenly at replica instances. The read endpoint has 3 replica instances and most of db connection for reading operations come from a single instance.

The metric for reader db connection can be found here (instance _1 is primary instance and others are replica ones)

Do I set up anything wrong? My understanding is that the wrapper will

1. parse read endpoint to figure out all replica hosts (here is 3 hosts)
2. create db connection pools for each replica instance
3. evenly split reading operations to each replica's connection pool

Is that correct? if yes, Should I add connection pool initiation code explicitly to fix the issue? if not, how to split the read traffic evenly among replica instances?

[jiyanhbo]

To be more specific, I followed the code https://github.com/awslabs/aws-advanced-jdbc-wrapper/blob/main/examples/AWSDriverExample/src/main/java/software/amazon/ReadWriteSplittingPostgresExample.java where internal connection pool is commented.

I will comment out the section to use internal connection pool and check if the above issue is addressed or not

[jiyanhbo]

I added back the connection pool but still see similar kind of db connection issue. Most of connection are from one postgres instance. Any clue?

[congoamz]

Hi @jiyanhbo, thank you for reaching out with this problem. I had a few questions that will help clarify the problem for me.

1. Are you connecting directly to a reader or are you connecting to the writer and then calling setReadOnly(true)?
2. What does your URL look like when you are establishing the reader connections? Is it the standard aurora writer or reader cluster or something else?

[jiyanhbo]

I connect with reader directly.

The url is "jdbc:postgresql:aws://foo-beam-amer-aurpg-virginia-dev-v1.cluster-ro-cnssohswqu7e.us-east-1.rds.amazonaws.com:5432/foo-beam-amer", It is standard aurora cluster's reader endpoint.

[jiyanhbo]

The cluster topology can be found

The metrics for db connections can be found here

You can see that most of db connections are from one postgres instance.

[jiyanhbo]

I increased the tops by 5X and expect the db connections from two idle instances 1 & 3 should bump up. However, they are still super small and instance 2's connection jumped a lot.

My requirement is very simple, the db connection or read traffic should be evenly split among all reader instances.

[jiyanhbo]

I stopped the reader traffic and restarted later, this time most of db connections are from instance 1. In the previous test, it is from instance 2

Also I set the max connection pool size is 10 at HikariConfig. There are 3 service nodes, so Max db connections to a postgres replica instance should be 30. However, we can see much more connections than the limit in AWS metrics. Looks like the thread pool max limit is not working as expected.

[congoamz]

Hi @jiyanhbo, I believe I understand what is happening here. There are two ways to get reader load balancing:

1. When calling setReadOnly(true) on a writer connection, our driver will connect to a reader if the read-write plugin is loaded. In this scenario, new reader connections are load-balanced. Note that the load balancing strategy is random reader selection, so load-balancing will not be perfectly even but should be mostly even
2. When connecting to the reader cluster endpoint, Aurora will load balance which reader you connect to

In your situation, you are load-balancing readers using the 2nd strategy (connecting to the reader cluster endpoint). Note that this method of load-balancing is completely controlled by Aurora, not our driver. I found this Aurora documentation that mentions some reasons why you might not be seeing load-balancing when connecting to the reader cluster. From my own testing, I realized that Aurora will connect you to the same reader if you are establishing many connections within a very short time frame. If you wait 5 seconds between each connection (as shown in that Aurora doc page I linked), you should see more load-balancing happening. If not, you may want to review the other reasons on that page on why you might not be seeing load-balancing.

Regarding the connection pool size not being obeyed, please note that initial connections to a cluster URL will not be pooled. This is intentional as pooling cluster URLs may be problematic because they resolve to different instances over time. The instances are not capped at 30 in your example because the connections are using the cluster URL and thus are not being pooled. Currently, the main benefit of internal pools is when setReadOnly is called. When setReadOnly is called (regardless of the initial connection URL), an internal pool will be created for the writer/reader that the plugin switches to and connections for that instance can be reused in the future.

[jiyanhbo]

My code is almost same as your code. I create a connection to writer instance and call setReadonly(true) immediately without running a random sql as what your code did.

        // Connect to writer
        try(Connection conn = DriverManager.getConnection(url, props)) {
          // Internally switch to reader
          conn.setReadOnly(true);

          // Execute some arbitrary SQL
          final Statement readerStmt = conn.createStatement();
          readerStmt.execute("SELECT aurora_db_instance_identifier()");
        }

You can reproduce my issue when testing under high throughput. Under low throughput(<5), my test result is good also. But under high throughput(>50), the result is not as expected.

My test is done through Jmeter with 100-200 users calling my service api concurrently. It cause tps around 100 normally.

My code is from a read world app, not for test purpose only. The driver version is latest.

Also I write a simple library to load balance the traffic by creating a map of reader instance and connection pool. For each request, I randomly select a reader instance & get a connection from its connection pool. The result looks very good and exactly what I expected. I still think there is something wrong from the connection pool sharing. Can you point me where is the connection pool sharing logic from?

[congoamz]

Thanks for the info. How are you generating the higher throughput? I'd like to reproduce this on my end, so if you can provide full sample code to reproduce or if you can tell me how I should tweak my example to get the higher throughput that would be great.

The code for the connection pools is here

Given your sample code and the fact that we do not internally pool cluster URLs, I'm wondering if there's a better driver configuration for your workflow. The internal pools are meant to improve performance when switching often between the writer/reader in multiple different connections but it looks like you only need a reader in this case. Would it work for you to keep internal connection pools disabled and instead maintain your own external pool to the reader cluster? It looks like you are connecting to the writer before the reader specifically to trigger the internal connection pooling and using your own external pool would allow you to skip this step. You could also maintain a second external pool for the writer cluster if you'd like. Let me know if you think that configuration would work for you or not.

[jiyanhbo]

I used Jmeter for testing. Here is how I configure it

You can use JMeter to do the same testing.

I will use connection pool for both reader & writer to make sure the postgres db instances in stable status. Let me know if you saw similar metrics pattern as what I saw.

[renatomariscal]

@jiyanhbo Are you using AuroraHostListProvider plugin?

[congoamz]

@jiyanhbo couple questions in addition to renatomariscal's above:

• I personally haven't used JMeter before, will I get the same behavior if I spin up 500 threads and have each thread execute my sample code in an infinite loop?
• in your code that you are running with Jmeter, are all 100-200 users executing the same SQL query?