Poor Google Cloud SQL performance

Question

I'm having a hard time getting decent performances with Google Cloud SQL, I'm doing some pretty basic CRUD operations, for instance:

public BaseUser getUser(String token) throws SQLException{
    Connection conn = DriverManager.getConnection(JDBC_CON_STRING);
    PreparedStatement ps = conn.prepareStatement(GET_USER_BY_TOKEN_QUERY);
    ps.setString(1, token);
    ResultSet rs = ps.executeQuery();
    List<BaseUser> users = inflateUser(rs);
    if(users.size() == 0){
        return null;
    }
    if(users.size() > 1){
        logger.info("DATABASE MAY BE CORRUPTED, THERE'S MORE THAN 1 USER WITH THE SAME TOKEN");
    }
    ps.close();
    rs.close();
    conn.close();
    return users.get(0);
}

And getting an average of 450ms reponse time for each query. += 150 for openConnection, 150 for operation, 150 for close. See the img. below.

I've read the google documentation, forums and multiple blogs and still can't see what I'm doing wrong (I must be doing something wrong, 450ms/query is wayyy to much...)

UPDATE 1: I'ts definitively a Google Cloud SQL issue, I installed my own local MySQL server and I'm having way better performances (80ms for an "insert or update", then select finally commit.), hope I could get some hints from Google dev. team, I really like the whole Google cloud platform, but it's simply impossible to work with that level of latency =(

UPDATE 2: 2014/05/06 The latency problem is the same with a D0 or a D16. Trying to insert 10000 rows (3 varchar and a ~100bytes blob) takes 32s from a Google ComputeEngine VM because of the latency. The duration is the same with 10000 inserts and a single batch insert. If I use 64 threads, then the duration is down to 3s. I tested with the native mysql jdbc driver.

Any suggestions? Thanks!

Declared

Are you sure your schema is indexed correctly? – Corbin

It's a test table with +- 15 columns and one secondary index. Si I guess it must be fine. Also, note that the connection and close operations take more than 100ms which isn't normal... – Declared

Have you granted access to your Cloud SQL instance to more than one app engine application? – Ahrendt

No, It's a D1, being used by only one gae app. – Declared

Sorry, it's a D0, but I'm still having the same issues with a D2. By the way I double checked that my instance is in the US and given that I'm not a premium user, it follows that my gae app is hosted in US as well. I've also tried creating a new instance, just in case anything got corrupted, but I still have the same problems! – Declared

I see the same problem wether I use a D0 or even a D16. The latency of individual queries is very high. – Demonize

This is strange; I regularly get <10ms latency from Cloud SQL. But 80ms on a local query is huge. Are you sure you have the proper indices defined? – Murrell

Answer 1

Give it a try with the MySQL Connector/J driver in AppEngine, it may give better performance. See https://developers.google.com/appengine/docs/java/cloud-sql/#Java_Connect_to_your_database for the classname and URL format to use.

Rob

Canonist

Same problem with MySQL connector – Demonize

Answer 2

I had problem with GAE + cloud SQL used with php and the socket.

The problem was my query that used actually 3 queries and read thousands of rows even tough only outputtet couple.

I found it with HeidiSQL and putting EXPLAIN in front of the select query.

Had to make the join in php side so that the load time (inspected in chrome devtools, not mysql run time) of that query-set dropped from 600 ms to 151 ms

Saddleback