locked
Performance issue opening MySql connection RRS feed

  • Question

  • User2145831744 posted

    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

    Monday, January 23, 2017 6:21 PM

All replies

  • User-359936451 posted

    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?

    Thursday, January 26, 2017 2:45 PM