Slow insert from AppEngine application

[Luca Zanconato]

Hello,

since friday 21st the Cloud SQL instance has become really slow when bulk inserting records: we tried to remove indexes, to restart the instance and also to drop and recreate the database without success.

Before friday we were able to bulk insert 800 records per time on a single table, and now when trying to insert 400 the instance takes more than 60 seconds to respond and so AppEngine reaches the request timeout limit.

• instance name: workforcegso-pre:workforcegso-pre-db
• application name: workforcegso
• stacktrace:

2013-06-24 10:53:34.500

it.gocloud.wgso.srv.biz.wfm.AnnotatedActionsWorkflowManager handleWorkflow: null
java.lang.reflect.InvocationTargetException
	at com.google.appengine.runtime.Request.process-017071684c02af59(Request.java)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:45)
	at it.gocloud.wgso.srv.biz.wfm.AnnotatedActionsWorkflowManager.handleWorkflow(AnnotatedActionsWorkflowManager.java:86)
	at it.gocloud.wgso.srv.res.BulkInterventions.putCsv(BulkInterventions.java:94)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:45)
	at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:186)
	at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:70)
	at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:279)
	at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:86)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:136)
	at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:74)
	at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1347)
	at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1279)
	at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1229)
	at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1219)
	at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:419)
	at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537)
	at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:699)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
	at org.apache.shiro.guice.web.SimpleFilterChain.doFilter(SimpleFilterChain.java:44)
	at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
	at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.apache.shiro.guice.web.SimpleFilterChain.doFilter(SimpleFilterChain.java:41)
	at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
	at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.apache.shiro.guice.web.SimpleFilterChain.doFilter(SimpleFilterChain.java:41)
	at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
	at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.apache.shiro.guice.web.SimpleFilterChain.doFilter(SimpleFilterChain.java:41)
	at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
	at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.apache.shiro.guice.web.SimpleFilterChain.doFilter(SimpleFilterChain.java:41)
	at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
	at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
	at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
	at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
	at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:380)
	at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at com.google.tracing.TraceContext$TraceContextRunnable.runInContext(TraceContext.java:483)
	at com.google.tracing.TraceContext$TraceContextRunnable$1.run(TraceContext.java:490)
	at com.google.tracing.TraceContext.runInContext(TraceContext.java:777)
	at com.google.tracing.TraceContext$DoInTraceContext.runInContext(TraceContext.java:754)
	at com.google.tracing.TraceContext$AbstractTraceContextCallback.runInInheritedContextNoUnref(TraceContext.java:345)
	at com.google.tracing.TraceContext$AbstractTraceContextCallback.runInInheritedContext(TraceContext.java:337)
	at com.google.tracing.TraceContext$TraceContextRunnable.run(TraceContext.java:487)
	at java.lang.Thread.run(Thread.java:722)
Caused by: com.google.apphosting.api.DeadlineExceededException: This request (017071684c02af59) started at 2013/06/24 08:52:35.074 UTC and was still executing at 2013/06/24 08:53:34.497 UTC.
	at java.util.regex.Pattern$Curly.match0(Pattern.java:4170)
	at java.util.regex.Pattern$Curly.match(Pattern.java:4132)
	at java.util.regex.Pattern$GroupHead.match(Pattern.java:4556)
	at java.util.regex.Pattern$Start.match(Pattern.java:3408)
	at java.util.regex.Matcher.search(Matcher.java:1318)
	at java.util.regex.Matcher.find(Matcher.java:593)
	at it.gocloud.wgso.srv.data.BaseJpaDao.addDomainFilter(BaseJpaDao.java:102)
	at it.gocloud.wgso.srv.data.BaseJpaDao.query(BaseJpaDao.java:152)
	at it.gocloud.wgso.srv.data.BaseJpaDao.find(BaseJpaDao.java:245)
	at it.gocloud.wgso.srv.data.BaseJpaDao.saveOrUpdate(BaseJpaDao.java:349)
	at it.gocloud.wgso.srv.data.InterventionDaoImpl.addOrUpdate(InterventionDaoImpl.java:211)
	at it.gocloud.wgso.srv.biz.wfm.InterventionWorkflowManagerImpl.insertOrUpdate(InterventionWorkflowManagerImpl.java:72)
	... 79 more

Thanks
Luca Zanconato

Il presente messaggio ed i suoi allegati sono confidenziali in ragione della loro natura professionale e, in ogni caso, non accessibili al pubblico. Nel caso in cui il presente messaggio sia ricevuto da soggetto diverso dal suo diretto destinatario, si prega di darne immediata notizia al n° +390236680761 e di procedere immediatamente alla distruzione dello stesso e dei suoi allegati, da effettuarsi senza copiarne o rivelarne ad alcuno il contenuto. Grazie per la collaborazione.

This e-mail and any  attachments thereto are confidential and may be covered by legal professional privilege or otherwise protected from disclosure. If you are not the intended recipient, please notify us immediately by phone at +390236680761 and then immediately destroy this message and any attachments from your system, without copying or disclosing the contents to any other person. Thank you for your co-operation.

[Tony Tseng]

Hi Luca,

Your instance appears to be co-located with AppEngine. I don't see anything unusual with the connectivity. Are you still experiencing high latency?

--
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-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Luca Zanconato]

Hi Tony,

from this morning (GMT+2) the system started to respond with low latency again.

Thanks

Luca

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Luca Zanconato]

Hi Tony,

regarding the issue below, we have the same problem in another App Engine application:

• instance name: mapadore-prod:mapadore-db/prod
• application name: mapadore-prod

The application is very slow at bulk-inserting more than 200 records per time (another application writing to the same instance is faster).

We tried also to use the com.mysql.jdbc.GoogleDriver, but the writes are still slow.


Is there any other way to get it faster? Any other option to set?
The other application mentioned below started to write faster after a week, will it be the same for this one?


Thanks
Luca Zanconato

[Vishnu Fafat]

Hi Luca,

Can you take a look at this page: https://developers.google.com/cloud-sql/docs/native-mysql-gae?

--

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-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/48071648-8204-4e7b-8b2d-78c964d5553f%40googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

Vishnu Fafat

[Rob Clevenger]

Hi Luca,

Yes, you can get the performance to improve by migrating your app to HRD (vs. the deprecated master/slave style application), or using a new application that is an HRD application.

Please check out our FAQ entry: https://developers.google.com/cloud-sql/faq#hrapps

"""

Should I use Google Cloud SQL with my Master-Slave App Engine application?

We recommend that you use Google Cloud SQL with High Replication (HRD) App Engine applications. While you can use use Google Cloud SQL with applications that do not use high replication, doing so might impact performance.

""" 

Hope this helps,

Rob

On Tue, Oct 15, 2013 at 8:10 AM, Luca Zanconato <luca.za...@gocloud.it> wrote:

--

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-d...@googlegroups.com.

[Luca Zanconato]

Hi to all, thank you for the replies.

Our application is already on High Replication Datastore (not migrated but created in HRD) and also we are already using the native connection (driver "com.mysql.jdbc.GoogleDriver") to Cloud SQL.

On Monday, June 24, 2013 12:05:01 PM UTC+2, Luca Zanconato wrote: