Delay Executing First Query After Not Using For A While

[brunofontaine]

For over a year now, I keep getting the following issue several times a day. I only see this happening when using SQLYog. If I use ODBC in scripts, it always seems to work fine.

 

When I first open a connection, accessing data is very fast.

I can do as many queries as I want and it remains fast.

 

If I stop using it for a while (15 to 30 minutes) and then try to execute any query against the same connection, it shows the status message “Executing Query(s)…” and then takes about 20 seconds before showing me the results. Once it showed me the results, everything is back to full speed again until I pause for another 15 to 30 minutes.

 

To avoid this delay, I can close the connection and re-establish it. I should not have to do that.

This issue happens on any of our workstations and whether I use local MySQL providers or Google Cloud MySQL, which seem to point to an issue with SQLYog application or installation settings.

 

 

How can I fix this once and for all?

[peterlaursen]

Please try to use the option to define a ‘keep-alive’ interval for the connecton.  Please see atached image.

 

[attachment=1959:alve.jpg]

 

 

 

This is a network issue that we (or anybody else) have not really been able to explain.  A new connection is fast but a reconnect is extremely slow. I believe that you would exerience the same with any client using persistent connection (PHP and ODCB don’t for instance) runnng from same machine

 

We have a (very) old blog about this:

http://blog.webyog.com/2009/09/02/%E2%80%9Cmysql-server-has-gone-away%E2%80%9D-part-2-session-timeout/

(when this blog was written the ‘Session Idle Timeout’ setting had just been added, but the ‘keep-alive’ interval setting is newer).

 

Also SQLyog documentation explains this.  Here is a link to and a quote from the online version (but same is available from  help .. help -menu): 

https://static.webyog.com/docs/SQLyog/Direct_Connection_SQLyog_MySQL_Front_End.htm

 

Session Idle Timeout: The option to define timeout for the session (different from the global setting) is possible with MySQL servers from 5.0 and up.  However, most users will need not to care about it – not even if server timeout setting is low. SQLyog will reconnect if connection was lost since last query was run. Most often user does not even notice. However, we have reports of situations where the network takes very long time to process such reconnect request. In this situation setting the timeout from the client will prevent the situation. Also SSH-users connecting to SSH servers/daemons that are slow to establish connection and where MySQL has a low timeout setting can use this option with advantage.

Keep-Alive Interval: In some cases, setting a high session idle timeout does not prevent disconnection. This option sets a time interval at which mysql_ping() will be executed. This should only be used if setting a high session timeout does not prevent disconnection. Also the interval specified should be the highest possible interval which still prevents disconnection.

 

[Author]

Posts