DB query execution lengths

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

User avatar
Zelek
Posts: 36
Joined: 24 Jun 2010, 22:36

DB query execution lengths

Postby Zelek » 04 Jul 2013, 04:09

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);
            }
        }
User avatar
Lapo
Site Admin
Posts: 21092
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: DB query execution lengths

Postby Lapo » 04 Jul 2013, 07:29

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/
Lapo
--
gotoAndPlay()
...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: JerodSa, Keganniff, Kevengar and 19 guests