MySQL disconnects now and then causing problems

So, as you know MySQL disconnects now and then.

So, I've come up with a solution. Sending a query every 25 seconds when MySQL connects.

Code:

    public static void runAsyncBeat() {
        new Thread(new Runnable() {
            @Override
            public void run() {
                PreparedStatement ps;
                    ps = con.prepareStatement("SELECT 1;");
                    ps.execute();
                    Thread.sleep(1000*25);
                runAsyncBeat();
            }
        }).start();;
    }

Which is called after connect

Code:

        //DB connect driver stuff...
        runAsyncBeat();
    }

This causes a drop in tps (from 20tps to 19.80/19.90 in tps).

I've also tried Bukkit's deprecated Async scheduler, which yields the same results.

 

@Neilnet


Woah, don't make a new Thread. All you need to do is send a query, just schedule a Runnable that gets run every 30 seconds that contains code to send a dummy statement.

 

@mythbusterma

Even still, with a Sync or even Async bukkit scheduler, I still don't get the full package of 20 tps.

Code:

    public static void runSyncHeartbeat(Main plugin) {
        plugin.getServer().getScheduler().scheduleSyncRepeatingTask(plugin, new Runnable(){
            public void run(){
                PreparedStatement ps;
                try{
                    ps = con.prepareStatement("SELECT 1;");
                    ps.execute();
                }catch(SQLException ex){
                }
            }
        }, 0L, 25*20L);
    }

 

Do you have access to the "my.cnf" of your MySQl server? If so, you can increase the timeout delay.
Link
There is also a method described to do this even without having direct file access.
But it's a bit strange, because the default timeouts are pretty high. (8 hours, afaik)
Your hoster may have set lower values though.

If you issue the query with a scheduler every 25 seconds, your TPS will not be influenced during the wait time of the scheduled task. So if you expierience a slight drop in TPS the whole time, I'm sure it has nothing to do with that.
When you use a sync task, the one tick when it does execute the query (50 ms nominal) may be too short, so your tick rate may be influenced a little every 25 seconds. But not the whole time.

BTW: Is your MySQL DB local or remote? If it's remote, an async task may be better suited to do the alive query.
So it does not impact your TPS.

 

What is the motivation behind this? 0.20 TPS drop can literally be caused by anything.

 

@CubieX

Don't modify the connection settings just for your plugin. Use dummy queries.

@Neilnet

Also, yes that is all you need to do Neil, look elsewhere for your TPS drops (if they even exist at all, something that small may just be error).

 

Normally you wouldn't have to. But it seems like the OPs MySQL server has lower settings than the default.
Instead of ugly dummy queries I would rather set proper settings (if possible) to avoid future hassle because of the same reason.
Per-session settings can usually also be set after a connection has been established.
e.g.

Code:

SET SESSION wait_timeout = 28800;

Better fight the cause than the symptoms.

If he has a long inactive time inbetween DB updates, an immediate DB response may not be necessary anyway.
So he could even reconnect on-demand and omit those dummy queries this way.
Also, if the connection gets dropped by some other reason he needs a strategy to reconnect annyway.
Of course it depends on his application what's practical and what's not.
But when professionally writing such systems I always try to avoid workarounds if possible with resonable effort.

 

Could you post your my.conf and maybe the mysql.log if you have logging enabled?
Normally the mysql connection requires a "ping" from time to time to keep connection active and as you have set SELECT 1 for query it should not affect performance in any way.
Maybe check if you closing the connection earlier in some statement or it gets some statement error or maybe one table is crashed. Just some ideas.