Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Very Slow With Ssh Tunnel

forums forums SQLyog SQLyog Comments Very Slow With Ssh Tunnel

  • This topic is empty.
Viewing 9 reply threads
  • Author
    Posts
    • #11568
      tomp_gl
      Member

      Hi,

      I just purchased SQLyog Enterprise and I'm connecting to my database server using an SSH tunnel through my gateway server. The latency to my database server from my gateway server is less than 1 ms, and my latency to my gateway server is about 260 ms. Doing any database query through SQLyog is very slow – looking at History tab, I see this:

      /*[1:40:04 PM][6256 ms] SQLyog reconnected */

      /*[1:40:04 PM][ 281 ms]*/ SHOW FULL FIELDS FROM `database`.`table1`;

      /*[1:40:04 PM][ 281 ms]*/ SHOW KEYS FROM `database`.`table1`;

      /*[1:40:05 PM][ 280 ms]*/ SELECT * FROM `database`.`table1` ORDER BY 1 ASC LIMIT 0, 50;

      /*[1:40:31 PM][6256 ms] SQLyog reconnected */

      /*[1:40:31 PM][ 280 ms]*/ SHOW FULL FIELDS FROM `database`.`table1`;

      /*[1:40:31 PM][ 281 ms]*/ SHOW KEYS FROM `database`.`table1`;

      /*[1:40:32 PM][ 281 ms]*/ SELECT * FROM `database`.`table1` ORDER BY 1 DESC LIMIT 0, 50;

      /*[1:41:12 PM][6178 ms] SQLyog reconnected */

      /*[1:41:12 PM][ 265 ms]*/ DESCRIBE `database`.`table2`;

      /*[1:41:13 PM][ 281 ms]*/ SHOW INDEX FROM `database`.`table2`;

      /*[1:41:31 PM][6177 ms] SQLyog reconnected */

      /*[1:41:31 PM][ 281 ms]*/ SHOW FULL FIELDS FROM `database`.`table2`;

      /*[1:41:31 PM][ 281 ms]*/ SHOW KEYS FROM `database`.`table2`;

      /*[1:41:32 PM][ 608 ms]*/ SELECT * FROM `database`.`table2` LIMIT 0, 50;

      /*[1:41:43 PM][6178 ms] SQLyog reconnected */

      /*[1:41:43 PM][ 280 ms]*/ SHOW FULL FIELDS FROM `database`.`table2`;

      /*[1:41:43 PM][ 281 ms]*/ SHOW KEYS FROM `database`.`table2`;

      /*[1:41:44 PM][ 281 ms]*/ SHOW CREATE TABLE `database`.`table2`;

      /*[1:42:09 PM][6115 ms] SQLyog reconnected */

      /*[1:42:10 PM][ 281 ms]*/ SHOW FULL FIELDS FROM `database`.`table2`;

      /*[1:42:10 PM][ 280 ms]*/ SHOW KEYS FROM `database`.`table2`;

      /*[1:42:10 PM][ 562 ms]*/ SELECT * FROM `database`.`table2` ORDER BY 1 ASC LIMIT 0, 50;

      /*[1:42:28 PM][6240 ms] SQLyog reconnected */

      /*[1:42:29 PM][ 281 ms]*/ SHOW FULL FIELDS FROM `database`.`table2`;

      /*[1:42:29 PM][ 280 ms]*/ SHOW KEYS FROM `database`.`table2`;

      /*[1:42:30 PM][ 562 ms]*/ SELECT * FROM `database`.`table2` ORDER BY 1 DESC LIMIT 0, 50;

      /*[1:55:18 PM][6506 ms] SQLyog reconnected */

      /*[1:55:18 PM][ 280 ms]*/ SHOW FULL FIELDS FROM `database`.`table2`;

      /*[1:55:19 PM][ 281 ms]*/ SHOW KEYS FROM `database`.`table2`;

      /*[1:55:19 PM][ 562 ms]*/ SELECT * FROM `database`.`table2` LIMIT 0, 50;

      As you can see, there is a huge latency when SQLyog reconnects – and it seems to reconnect on every command I issue in the GUI. Presumably SQLyog is reconnecting over SSH every time – why is this? Is there a way to keep the SSH connection open like I would with a terminal window? What can I do to speed this up? It's really too slow to work with at the moment.

      Thanks

    • #29247
      navyashree.r
      Member

      Hi,

      Currently we are on progressing to improve this “reconnect” functions in SQLyog.

      With SSH there are many reasons this latency can occur. The ssh connection happens through ssh client plink. The reconnect happens mainly for 2 reasons,

      1. If any network issue happens between ssh server, again this plink neeed to re-initialize.

      2. Because of less wait_timeout.

      We will look into this and you could expect noticeable improvements in 8.13 Beta2.

      BTW…

      * Could you please tell us whether SQLyog & SSH are in same machine?

      * What is the MySQL version?

      * Also please tell the o/p of show variables like '%timeout'?

      Regards,

      Navya

    • #29248
      tomp_gl
      Member

      SQLyog is on my desktop, and the SSH daemon is on the remote gateway.

      MySQL version is 5.0.45-log.

      mysql> SHOW VARIABLES LIKE '%timeout%';

      connect_timeout 50

      delayed_insert_timeout 300

      innodb_lock_wait_timeout 50

      innodb_rollback_on_timeout OFF

      interactive_timeout 50

      net_read_timeout 30

      net_write_timeout 60

      slave_net_timeout 3600

      table_lock_wait_timeout 50

      wait_timeout 5

      Thanks

    • #29249
      tomp_gl
      Member

      [accidently double posted]

    • #29250
      nithin
      Member

      Thanks for quick response.

      From the output of SHOW VARIABLES LIKE '%timeout%' I understand the wait_timeout set for 5 seconds. It means if SQLyog idle for more than 5 seconds It needs reconnection for the next query to execute. With current implementation the reconnect functionality always requires reconnect to server and re-initializing plink. We already told that we are in progress of improving this reconnect operation that doesn’t need re-initializing plink(if no network issue happened) and also reconnecting the sever using MySQL APIs, and I hope this will solve the latency you are facing.

      We can provide you special build probably by 3 – 5 days once the development is over.

      I will update the status here.

    • #29251
      nithin
      Member

      As a workaround execute this query from SQLyog editor once connected to the server.

      set @@session.wait_timeout = 28800;

      The session wait_timeout can overirde the 5 second timeout set for the server. But this query is required for each time when SQLyog connected to server.

      Please tell this workaround solves the latency issue or not?

      Thanks

    • #29252
      tomp_gl
      Member

      Thanks! That fixed the problem.

    • #29253
      navyashree.r
      Member

      Hi,

      This issue is fixed and available in 8.13 beta2, publicly available!

      Please refer to:

      http://www.webyog.com/blog/2009/07/15/sqly…-been-released/

      Please let us know the status.

      Thank you.

      Regards,

      Navya

    • #29254
      Jet
      Member

      Hi, I use sqlyog v 8.82 and connec to mysql use ssh tunnel very very slowly.

      mysql> SHOW VARIABLES LIKE '%timeout%';

      +


      +


      +

      | Variable_name | Value |

      +


      +


      +

      | connect_timeout | 10 |

      | delayed_insert_timeout | 300 |

      | innodb_lock_wait_timeout | 50 |

      | innodb_rollback_on_timeout | OFF |

      | interactive_timeout | 28800 |

      | net_read_timeout | 30 |

      | net_write_timeout | 60 |

      | slave_net_timeout | 3600 |

      | table_lock_wait_timeout | 50 |

      | wait_timeout | 28800 |

      +


      +


      +

    • #29255
      peterlaursen
      Participant

      Please explain: is it the connection that establishes slowly (only) or do you experience a slowness also after connection has been established?

Viewing 9 reply threads
  • You must be logged in to reply to this topic.