0

I am seeing this behaviour where the AWS RDS Proxy TargetGroup is set to take 75% of the available connections. As per documentation, RDS proxy should only take up these connections as required and not try to take them all.

What I am actually seeing is something like this -

enter image description here

where the currently 'borrowed' connections is same as the number of incoming client connections but the 'total' connections that are taken up is around the 75% mark of the max connections.

What could be the cause for this ? The application that is using this database is not holding any of these connections, this i've checked from the pg_stats_activity table.

Any pointers would help.

UPDATE: The large connections are fine, even if they are idle, as long as they are re-used the proxy. In my case, the problem seems to stem from 'pinning' of connections due to a SET client_encoding statement before every connection and thus not allowing the proxy to re-use the connection.

There's a value called EXCLUDE_VARIABLE_SETS which should exclude SET statements from being pinned but it is not doing what it is documented to be.

I have also tried moving this query to init query but no luck.

FrailWords
  • 886
  • 7
  • 19
  • Which RDS backend are you using? As I understand it different features are implemented for Aurora, MySQL, PostreSQL, etc. – afaulconbridge Mar 16 '21 at 08:20
  • @afaulconbridge the RDS backend I am using is PostgreSQL. I solved this problem by getting rid of all possible 'SET' statements from the code which were called during connection initialization and moved them to the `init` block in the RDS Proxy's setting. This way, even if the `EXCLUDE_VARIABLE_SETS` didn't work, we didn't have a large number `SET` statements. I'd be curious to know if the feature set differs by the type of backend and if that is documented somewhere. – FrailWords Mar 17 '21 at 05:35
  • that is interesting! The best I could find is https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-pinning - but I am sure I had read somewhere that the `init` SQL wasn't supported with PostgreSQL backend. If that's no longer the case then it makes it more usable. – afaulconbridge Mar 17 '21 at 17:11
  • @afaulconbridge the `init` query I was talking about is a section in the RDS proxy target configuration and not the same as `init` query in SQL. Just thought i'd clarify that. – FrailWords Mar 25 '21 at 14:15

2 Answers2

1

I did read about this before and I found this issue from another post:

AWS lambda function with runtime python 3.7 does not utilizing connection pool in rds proxy instead it create new connection on every request

They recommend modifying the db proxy target group to set the autocommit=0 before all of the queries made to the DB.

AWS support helped to resolve this issue. add SET autocommit=0 in the initial query fixed the issue. you can do the following as well. aws rds modify-db-proxy-target-group --target-group-name --db-proxy-name --connection-pool-config '{"InitQuery": "SET autocommit=0"}'

Mark Hayward
  • 133
  • 5
  • In my case, I am not using a Lambda. Even then, the problem of committing is not the problem I am facing. After some research, its got to do with 'session pinning' due to some statements and this stops the proxy from re-using the connections. Having idle connections is ok as long as they are re-used. Still, thanks for your answer. – FrailWords Jan 21 '21 at 16:34
0

If you are using PostgreSQL then the "Session pinning filter" does not work: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-pinning:~:text=RDS%20Proxy%20doesn%27t%20support%20session%20pinning%20filters%20for%20PostgreSQL

I confirmed this after contacting AWS support. For some reason they have enabled in the UI this feature even when the engine is PostgreSQL which is completely confusing to users. Worth mentioning that the Initialization query also doesn't work for PostgreSQL.

asdcamargo
  • 86
  • 4