Best way of dealing with limit of 60 concurrent connections per app instance from App Engine standard environment to Cloud SQL?

Question

App Engine Python standard environment (threadsafe)
Cloud SQL MySQL Second Generation
All request handlers need database and are user-facing
no ORM, just plain SQL

According to Cloud SQL: Pricing and Access Limits with our machine type Cloud SQL has a limit of max 4,000 concurrent connections per app, and more importantly in our case, when connecting from App Engine standard environment, a limit of max 60 concurrent connections per app instance to the Cloud SQL instance. (The other limits aren't close to become a bottleneck)

The most likely bottleneck would be the max 60 concurrent connections per app instance. I have no numbers available, so I'm not sure whether a single app instance (F1/B1) could even serve more than 60 users at the same time (probably not).

The current code makes sure that for every request (where database is needed), a connection is created and finally closed (even when an exception occurred earlier. The connection might be closed earlier depending on the situation. I'm not sure if this is the best approach. Simplified function as called from a webapp2.RequestHandler class:

def handle_FAW_Request_approve(reqh, ref):
    try:
      conn = connect_to_cloudsql()
      # do queries, maybe updates
      # eventually conn.close() earlier if database not needed anymore
      # do something else
      # return response
    finally:
        try:
            conn.close()
        except Exception as err:
            # connection never existed or already closed
            pass

Answers to this question suggest a (threadsafe) way to re-use database connections (or cursors). I assume this would help to save the time that is otherwise lost with any overhead of opening/closing connections, but yet no safe-guard that the app instance will stay within the connection limits, right?

Is App Engine able to detect the connections problem by itself and automatically spawn new instances (with auto-scaling) instead of directing more traffic to that instance?

Or does the app need to deal with the limit by itself? What would the solution be to avoid user-facing server errors occurring randomly by exceeding the given limit?

With auto-scaling, the default max_concurrent_requests is 8 so it's unlikely that you would have issues with the concurrent connection limit. — Vadim, Mar 05 '18 at 20:50
@Vadim I forgot the `max_concurrent_requests` for auto-scaling. That would help indeed. Thank you! (PS: I would accept your comment as answer.) — Ani, Mar 05 '18 at 21:30

score 1 · Accepted Answer · answered Mar 06 '18 at 18:37

App Engine has built in limits on how many requests it will send to a single instance before it spins up a new one. With automatic scaling, the default value of max_concurrent_requests is 8 so it's unlikely you would reach 60 concurrent requests on a single instance unless something goes really wrong.

You should also consider raising the max_concurrent_requests value as it's likely you can get more out of a single instance than 8 requests.

Best way of dealing with limit of 60 concurrent connections per app instance from App Engine standard environment to Cloud SQL?

1 Answers1