DB query execution lengths

[Zelek]

I am noticing some strange behavior where roughly 1 in every 30 or 40 MySQL queries made by the server takes about 10 times as long as normal. For example, the query normally takes ~20ms to execute, but sporadically will take ~200ms. This happens even when I test with localhost, so network lag can be ruled out. Is this normal behavior for MySQL, or is something funky going on with my server extension?

Here's my example test code:

Code: Select all

     for (int i = 0; i < 500; i++) {
            Connection conn = null;
            PreparedStatement sql = null;
            ResultSet result = null;

            try {
                conn = Global.dbManager.getConnection();

                sql = conn.prepareStatement("UPDATE locations SET locX = ? WHERE usrId = 1");
                sql.setInt(1, i);

                long start = System.currentTimeMillis();
                sql.executeUpdate();
                D.log(i + "  execution length: " + (System.currentTimeMillis() - start));
            } catch (Exception e) {
                D.log("An SQL Error occurred: " + e.getMessage());
            } finally {
                DatabaseUtil.close(result);
                DatabaseUtil.close(sql);
                DatabaseUtil.close(conn);
            }
        }


[Lapo]

GIven that your test runs on a single thread and all queries are executed serially, it sounds very much like it's a problem with MySQL.
It could also be a hiccup from the garbage collector, because you're creating and dropping lots of objects very quickly.
You may want to run the test and check the AdminTool Dashboard to see if there are any significant spikes in the CPU indicator. If so, it might be the GC.

More on this here:
http://insightfullogic.com/blog/2013/ma ... on-java-3/