Friday, June 21, 2013

Re: Connection setup time >700ms on a google compute engine instance

To expand on this issue. Even a single SET_AUTO_COMMIT or manual COMMIT statement takes 70-80 ms. I am not sure at this point if this is hibernate issue, but if the driver has any opportunity to batch such statements into a single request together with the real SQL that would decrease the response time by 50% on many queries.


Here is one instance:

21:47:49,648 FINE  [com.google.cloud.sql.jdbc.internal.SqlProtoClient] (Threadpool-6) Executing Operation: SET_AUTO_COMMIT
21:47:49,648 FINE  [com.google.cloud.sql.jdbc.internal.googleapi.RpcGoogleApi.DefaultGoogleApi] (Threadpool-6) Sending request to https://www.googleapis.com/sql/v1/jdbc/execOp?alt=proto
21:47:49,649 CONFIG [com.google.api.client.http.HttpTransport] (Threadpool-6) -------------- REQUEST  --------------
POST https://www.googleapis.com/sql/v1/jdbc/execOp?alt=proto
Accept-Encoding: gzip
Authorization: <Not Logged>
User-Agent: Google SQL Service/1.0 Google-HTTP-Java-Client/1.9.0-beta-SNAPSHOT (gzip)
X-Upload-Content-Length: 0
Content-Type: application/x-protobuf
Content-Length: 99

21:47:49,649 CONFIG [com.google.api.client.http.HttpTransport] (Threadpool-6)
^Nprod-ef2f:ef2f^RI^@d^_^M?S???bv???|b'???C??^S~?1???3w,??^P?r?{???^C^\?????6?U?_??8}?????]i^Z^D^H^D(^@@^_
21:47:49,722 CONFIG [com.google.api.client.http.HttpTransport] (Threadpool-6) -------------- RESPONSE --------------
HTTP/1.1 200 OK
X-Frame-Options: SAMEORIGIN
ETag: "_7wYyDbWr4n6mUP0M5ioyTuAIh4/0PoMTV1GP9GOFogpVgbpFNZgA7o"
Date: Fri, 21 Jun 2013 21:47:49 GMT
Transfer-Encoding: chunked
Expires: Fri, 01 Jan 1990 00:00:00 GMT
X-XSS-Protection: 1; mode=block
Content-Type: application/x-protobuf
Server: GSE
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Pragma: no-cache
X-Content-Type-Options: nosniff


On Saturday, June 22, 2013 12:21:20 AM UTC+3, Sergio Garcia Murillo wrote:
Hi Joe,

Cloud SQL with HA/redundancy is a killer functionality for us, in fact that was the reason for choosing GCE over AWS, so any improvement would be really appreciated.

We were already investigating the connection pooling with somewhat mixed results. The mayor blocker for now is the latency of single queries, even a "SELECT 1" takes 70-80ms to execute.

We were using the SELECT 1 as a validity query, so each request time is doubled. Also we have seen in some situations in which some estrange metadata is been fetched, adding an extra 160ms. So a request where only 3 queries are performed can take up to (3+1)*2*80=640 ms.

Now we are trying to move the validity query to background but we get some errors from time to time. I will be able to provide more information in the following days. Do you have any recommendation on keepalives and any setting that may increase performance?

Best regards
Sergio

El 21/06/2013 19:46, Joe Faith escribió:
Hi Sergio

Sorry to hear about the problems you've been having.
We realize that the latency of the external JDBC connection is quite large (including from GCE), and we are working very hard to improve the connectivity.
We hope to have more news for you real soon, but in the meantime it looks like connection pooling would help

The recommendation against using connection pooling in the FAQ [1] was intended to apply to connections from GAE, where the time to create a new connection is similar to that to test the liveness of an existing one.
We'll clarify it.

J





On Fri, Jun 21, 2013 at 4:22 AM, Sergio Garcia Murillo <sergio.gar...@gmail.com> wrote:
Hi all

I am trying to connect to my Cloud SQL instance inside a Google compute engine server instance, and we are observing that the setup time for a DB connection is always bigger than 700ms.

Given that not connection pooling is recommend, this makes our REST based app unusable as each request needs to open a new connection to the database and get's the 700ms minimum lag.

Below are the traces of a simple app running on the server instance showing the connection setup phase with the timestamps.

1371812704338 FINE Using cached Access token.
1371812704354 FINE Created client for instance: xxxxx
1371812704359 FINE Executing OpenConnection: null at xxxxx
1371812704513 CONFIG -------------- REQUEST  --------------
1371812704516 FINEST ProxySelector Request for https://www.googleapis.com/sql/v1/jdbc/openConnection?alt=proto
1371812704828 FINEST Proxy used: DIRECT
1371812704936 FINEST sun.net.www.MessageHeader@2a9cfec112 pairs: {POST /sql/v1/jdbc/openConnection?alt=proto HTTP/1.1: null}{Accept-Encoding: gzip}{Authorization: Bearer ya29.AHES6ZQrijCM7aJRY5jzsWayAIDp7lwoVFzHbFsVoWr2L2bY9FuYDNs}{User-Agent: Google SQL Service/1.0 Google-HTTP-Java-Client/1.9.0-beta-SNAPSHOT (gzip)}{X-Upload-Content-Length: 0}{Content-Type: application/x-protobuf}{Cache-Control: no-cache}{Pragma: no-cache}{Host: www.googleapis.com}{Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2}{Connection: keep-alive}{Content-Length: 66}
1371812704936 FINEST HttpClient.writeRequests() streaming=true
1371812704938 CONFIG
1371812705039 FINEST sun.net.www.MessageHeader@3e018c7412 pairs: {null: HTTP/1.1 200 OK}{Cache-Control: no-cache, no-store, max-age=0, must-revalidate}{Pragma: no-cache}{Expires: Fri, 01 Jan 1990 00:00:00 GMT}{Date: Fri, 21 Jun 2013 11:05:05 GMT}{ETag: "_7wYyDbWr4n6mUP0M5ioyTuAIh4/78JYOhpfkickmRL0KbO9P7pOgNI"}{Content-Type: application/x-protobuf}{Transfer-Encoding: chunked}{X-Content-Type-Options: nosniff}{X-Frame-Options: SAMEORIGIN}{X-XSS-Protection: 1; mode=block}{Server: GSE}
1371812705041 CONFIG -------------- RESPONSE --------------
1371812705042 CONFIG Response size: 75 bytes
1371812705043 CONFIG
1371812705048 FINEST RPC response: connection_id: "\000d\037\r\370x\331\2428\272t\267P\030\340K[\263P\262c\205\260.Vb\262*}\027\314r\305\341;\310\024\306\\\030\323\324Q\003Tp\355\303\242\352\003!\v3^\203/\030`I*\236\030U=\322>x\027;\302$\211"
1371812705122 FINEST Connection: {instance=xxxxx, database=xxxxx}

Is there any way to enhance the connection setup or enable the connection pooling reliably?

Best regards
Sergio

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
Joe Faith | Product Manager | Google Cloud
--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)