CODEWITHSUNDEEP
javagoogle-app-enginejdbcgoogle-cloud-sql
Rodrigo Manyari
Rodrigo Manyari

Reputation: 253

Poor Google Cloud SQL performance

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.

enter image description here

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!

Upvotes: 6

Views: 1516

Answers (2)

gray_15
gray_15

Reputation: 471

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

Upvotes: 0

Rob
Rob

Reputation: 176

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

Upvotes: 1

Related Questions