Re: Hibernate - Spring - Cloud SQL

Hi Keith,

An end-to-end test like this is valuable from an end-user's perspective, but it is difficult to tell where the time is going.  Please use appstats to get a better picture:

https://developers.google.com/appengine/docs/java/tools/appstats

This will tell you if the queries to the database are fast or slow, or maybe hibernate is doing thousands of queries unnecessarily.  If everything looks ok on the database side, I'd look harder at the warmup request.  I believe a request can be a warmup even if it doesn't go to /_ah/warmup.

Ken

On Sun, Sep 23, 2012 at 9:05 AM, Keith Mukai <keith.mukai@essaytagger.com> wrote:

Hey Joe,

I'm still using my Spring-Hibernate-CloudSQL framework, but I'm seeing some crazily unpredictable Cloud SQL response times. I have a trivial DB "poke" servlet that just opens a session, runs "SELECT 1", and reports an elapsed time. I have a cron job that hits the poke every five minutes and the execution time is all over the place. Here's a series of response times with me hitting the poke servlet and refreshing the page over and over again (in millis):

67

117

59

37

317

2764

2404

2080

35

28

1459

29

31

23

3959

68

132

The variability of these results just confounds me. If the code was doing something horribly inefficient, the results should be more uniformly terrible. But it's obviously capable of running the poke well enough. It's the 1000-3000ms responses that are freaking me out. I had zero other load during these tests.

On DB-heavy pages, *each* DB hit can scale up to similar 3000+ms times, causing the whole page to take an awful 12-15 seconds to load. And then the next request a second later might take 250ms. 

At first I thought it was just a warmup issue (thus the creation of the poke task) -- and it does seem to do better when it's fielding constant requests -- but then a long load will happen again at unpredictable times (and not related to an /_ah/warmup).

I have some issues on my end -- Spring isn't closing my Hibernate connections and I can't figure out why. But as request load increases, I tend to see better, more stable performance. Obviously we'd expect the opposite if those stale connections were bogging down the system. I'm not using a connection pool layer with Hibernate. 

I don't know if it's specifically a Cloud SQL issue, but that seems the most likely culprit at the moment. 

What other info can I provide to help diagnose what's going on?