Dealing with Concurrent Requests & Connections in Cloud SQL
We have created an app on App Engine using Datastore. Now we have been led to use Cloud SQL as we wanted to use joins, nested queries, and functions such as average, total etc. Data gets migrated from Datastore to Cloud SQL by daily Cron jobs.
I was just going through the below links to know the basic details related to performance and limitations.
- https://cloud.google.com/sql/docs/diagnose-issues#data-issues
- https://cloud.google.com/sql/pricing#v1-pricing
- https://cloud.google.com/sql/faq#sizeqps
So far it looks like Tier D0 or D1 will serve the purpose that we intended.
A few things that are confusing me:
A) What is Pending Connection and how does it affect? Not sure if this throws 1033 Instance has too many concurrent requests when it exceeds 100. How do we handle this? Is it like we can create 250 connections but use only 100 at a time?
B) 250 Concurrent Connections. Should throw error Too Many Connections if it exceeds 250
C) Per app engine instance 12 concurrent connections per SQL Instance. How do we ensure that no more than 12 connections per app engine instance?
I have gone through the following forums:
What's a good approach to managing the db connection in a Google Cloud SQL (GAE) Python app?
But people face certain issues with that.
D) We got an OperationalError:
(2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 38") error when we tried a test with 1000+ requests.
We have 1500+ people using our system concurrently and it looks like it will fail. So we are just confused if we can use Cloud SQL due to the above-mentioned issues. But solutions should be available though.
In my company, we run a postgres database on Google Cloud SQL. We encountered a similar problem, and our solution consisted of using a proxy service that keeps a common pool of connectors for all the users.
For Postgres, this is called PgBouncer (https://www.pgbouncer.org/). After a short googling session I found ProxySQL (https://proxysql.com/) which is mentioned as a potential similar tool for MySQL (see: https://github.com/prisma/prisma/issues/3258).
On their website, ProxySQL also provides a schema that can help you understand how this service looks in practice.
