Queries related to Cloud SQL concurrent connections and requests

[Kartik Domadiya]

I need help in understanding few things related to Cloud SQL.

We have an app on App Engine using Datastore. Now we have requirement of using Cloud SQL with Datastore. The data is migrated from Datastore to Cloud SQL via cron jobs on Appengine.

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 for looks like First Generation Tier D0 - D1 will serve the purpose.

Few things which 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 ? 

    Have gone through 

            http://stackoverflow.com/questions/10424442/what-are-the-connection-limits-for-google-cloud-sql-from-app-engine-and-how-to

            http://stackoverflow.com/questions/10158805/whats-a-good-approach-to-managing-the-db-connection-in-a-google-cloud-sql-gae/10438622#10438622

    But people face certain issues in that.

d) We got OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 38") error when     we tried a test with 1000+ requests concurrently.

We have 1500+ people(as of now and it will grow) using our system concurrently and 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. 

[sande...@sruthika.net]

Hi karthik,

Even my scenario is similar.I have users more than 10k!! what is the final solution??

[Kartik Domadiya]

No Sandeep,
I am stuck in understanding the above mentioned three points If you can help.

[David Newgas]

Replies in-line:

On Tue, Jan 26, 2016 at 11:39 PM, Kartik Domadiya <kartik....@searce.com> wrote:

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 ?

The key word here is "pending". This is really a limitation on the rate at which new connections can be opened. Due to our connection routing infrastructure we have a limit on the number of connections that can be in an internal startup phase at once.  As the FAQ explains: "Establishing a connection takes, on the server side, about 1.25 ms; because of the 100 pending connection limit, this means a maximum of 800 connection per second. If more than 100 clients try to connect simultaneously then some them will fail."

b) 250 Concurrent Connections.

        Should throw error Too Many Connections if it exceeds 250

This is correct. The key word here is "concurrent", as this is the number of connections that exist at a given moment, rather than anything to do with the rate at which they are opened.

 

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 ? 

    Have gone through 

            http://stackoverflow.com/questions/10424442/what-are-the-connection-limits-for-google-cloud-sql-from-app-engine-and-how-to

            http://stackoverflow.com/questions/10158805/whats-a-good-approach-to-managing-the-db-connection-in-a-google-cloud-sql-gae/10438622#10438622

    But people face certain issues in that.

If you have one connection per request then you can ensure <= 12 connections per app engine instance by doing two things: (a) don't increase max-concurrent-requests above 12 and (b) ensuring you close db connections at the end of the request. If you use connection pooling, ensure you have less than 12 connections and they are closed cleanly when no longer used.

 

d) We got OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 38") error when     we tried a test with 1000+ requests concurrently.

This error message occurs for lots of different problems. My guess is you are hitting the 100 pending connection limit. Try ensuring you open less than 800 connections per second (but you can keep them open >1s, allowing for >800 concurrent connections).

 

We have 1500+ people(as of now and it will grow) using our system concurrently and 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. 

If you are using D0 or D1 but reaching limits of your instance, try moving to a larger tier.  Also you can try Cloud SQL second generation instances (in beta) which have some better performance characteristics.

Yours,

David