Very slow opening MySQL connection using MySQL Connector for .net
I am trying to solve the problem of very long response time from MySQL when opening a connection using MySQL Connector for .net.
I have installed MySQL 5.5 running on an Azure VM (Server 2008) with -skip-name-resolve and the database user accounts host restrictions are using IP addresses. I am using the latest MySQL connector for .net in my WCF service running on Azure (in the same location as US-East, I am using a trial subscription, no affinity). My connection string in WCF service uses the internal IP of the MySQL MySQL host as the server parameter value. I also have "pooling = true; Min Pool Size = 2;" just in case (I also tried without these parameters).
With WCF tracing, the response times for a query after starting the service and processing the queries are pretty good (even though each query result is unique and therefore not cached) and I have no problem with MySQL performance hitting it frequently.
But a huge problem that I could not hack - is the time it takes to connect to the MySQL Open after any calls to the database have been made for about 3 or 4 minutes , if no database call is not made within minutes, it takes 8 or 9 seconds or more to open the connection again. ... I wrapped the actual "conn.open ()"; with trace instructions before and after the call, and this is the behavior I see over and over in the log after a few minutes of inactivity.
BTW, I also tried (and still use) the "use" style of use to ensure the MySQL connector manages the connection pool.
eg .: using (var conn = new MySqlConnection (Properties.Settings.Default.someConnectionString)) {... statements ..}
I feel like I've reached an impasse on this, so any suggestions would be greatly appreciated.
source to share
I can explain your question "the time it takes to connect to MySQL Open after no calls to the database have been made for about 3 or 4 minutes. If there are no calls to the database for a few minutes, it takes 8 or 9 seconds or more. " , Why is this happening:
Windows Azure websites use the concept of hot (active) and cold (inactive) sites, where if the sites do not have an active connection, the site experiences a cold state, which means the host's IIS process is terminated. When a new connection is made to these websites, it takes a few seconds for the site to be up and running. Although you have a MySQL server linked to this website, it takes a few more seconds to get the requested request as it takes a while for the initial IIS process to start. That's why after a few minutes of activity, the response time is longer.
You can see the following presentation for more details on Windows Azure Hot (active) and Cold (inactive) websites: http://video.ch9.ms/teched/2012/na/AZR305.pptx
Like this time, I am not sure and I don’t know how you can keep sites always hot, even if you go to a shared website or not at all. What can I suggest you write your question on the Windows Azure WebSites Forum and someone on this team will provide you with an appropriate answer.
source to share