Performance issue opening MySql connectionRSS

• • Reply
  fraserm

  None

  0 Points

  1 Post

  Performance issue opening MySql connection

  Jan 23, 2017 06:21 PM|fraserm|LINK

  Hello,

  Hoping you can help me out with some suggestions here.

  I find that when I host a simple ASP.NET form on my web hosts servers (Vidahost), I get a 5 second delay when I call to Open() the connection.

  The code I am using to narrow this issue down is below, and using asp.net tracing I see a consistent 5 second delay in between seeing the "Open Connection" and "New SQL Command" debug spew appear in the log, implying that the .Open() call is taking way too long.

  Vidahost have kindly done a Wireshark trace and report that: MySQL sends the Server Greeting.  This gets acknowledged by the web server, taking around 5ms The web server then seems to wait ~5 seconds before initiating the Login request

  If I run exactly the same code on my laptop at home, using the external IP address to their MySql DB, the whole request completes in ~0.3s!

  I`m using the latest ADO.NET driver (v.6.9.9) which gets copied to my hosted website.

  Can anyone help shed any possibilities on this?  Is there any more advanced tracing we can do to dig into the connection being opened?

      protected void Page_Load(object sender, EventArgs e)
      {
          Trace.Write("Start Page_Load");
          string strConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
  
          Trace.Write("Create null connection");
          MySqlConnection connMySql = null;
  
          try
          {
              HttpContext.Current.Trace.Write("Setup connection with connection string");
              connMySql = new MySqlConnection(strConnectionString);
  
              HttpContext.Current.Trace.Write("Open Connection");
              connMySql.Open();
  
              HttpContext.Current.Trace.Write("New SQL Command");
              MySqlCommand cmdMySql = new MySqlCommand();
              cmdMySql.Connection = connMySql;
  
              HttpContext.Current.Trace.Write("Server: " + connMySql.DataSource.ToString());
              string sql = "";
  
              HttpContext.Current.Trace.Write("Setup SQL Query");
              sql = "SELECT * FROM tblret";
              cmdMySql.CommandText = sql;
  
              HttpContext.Current.Trace.Write("Call DB");
  
              MySqlDataReader dr = cmdMySql.ExecuteReader();
  
              HttpContext.Current.Trace.Write("DB Returned");
  
              int recordsReturned = 0;
  
              while (dr.Read())
                  recordsReturned++;
  
              HttpContext.Current.Trace.Write("Parsed " + recordsReturned.ToString() + " records");
              textArea.Text = "Parsed " + recordsReturned.ToString() + " records";
          }
          catch (Exception ex)
          {
              HttpContext.Current.Trace.Write("Error: " + ex.Message);            
          }
  
      }
  

  
  Thanks!

  Fraser

• • Reply
  march11

  Contributor

  2155 Points

  2142 Posts

  Re: Performance issue opening MySql connection

  Jan 26, 2017 02:45 PM|march11|LINK

  What kind of firewalls are in place on your network, (not at home)? Can you get a connection in front of the firewall to see if you can get the same performance at  work as you do at home. At least briefly for a test.

  Are you on a large network, is there a lot of latency on the switch segment that you are working on?

  Based on your comments, it sounds like some other appliance is partially to blame. How does your laptop perform when connected at the office?