1

I am trying to solve the problem of very long response times from MySQL when opening a connection using the 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 US- East, I have been using a trial subscription, no affinity set). My connection string in the WCF service is using the internal IP address of the VM hosting MySQL as the server parameter value. I also have "pooling = true;Min Pool Size=2;" just in case (I have tried without these parameters too).

When tracing the WCF the query response time once the service is running and processing requests are pretty good (even where each query result is unique and so not being cached) and I have no issues with the performance of MySQL providing it's getting hit frequently.

But the huge problem I haven't been able to crack is the length time it takes to get the connection to MySQL Open after no calls to the database have been made for about 3 or 4 minutes. If no database calls are made for a few minutes it takes 8 or 9 seconds or more to open the connection again. I wrapped the actual "conn.open();" with trace statements before and after calling, and this is the behaviour I am seeing logged time and time again after a few minutes of inactivity.

Incidentally, I have also tried (and still am using) the 'using' style of connection handling to ensure that the MySQL Connector is managing the connection pool.

e.g.: using (var conn = new MySqlConnection(Properties.Settings.Default.someConnectionString)) { ... statements ..}

I feel like I have reached a dead end on this one so any suggestions would be greatly appreciated.

AnthonyC
  • 33
  • 1
  • 4
  • I use Enterprise Library Community to manage MySQL Connector / .Net I obtained fast times – Alberto León Sep 12 '12 at 17:49
  • Hi Alberto, I have ran some more tests and the slowness only occurs when the application is running on Azure and connecting to MySQL (either also on Azure on a VM or a remote server). Connecting to SQL Server on Azure is fast. Just to check before I go checking out Enterprise Library, are you running on Azure? It seems either Azure or the MySQL Connector is dropping the connection pool. – AnthonyC Sep 13 '12 at 13:49

1 Answers1

2

I can explain your question "the length time it takes to get the connection to MySQL Open after no calls to the database have been made for about 3 or 4 minutes. If no database calls are made for a few minutes it takes 8 or 9 seconds or more to open the connection again." why it happens:

The Windows Azure websites uses concept of hot (active) and cold (inactive) sites in which if a websites has no active connection the site goes it cold state means the host IIS process exits. When a new connection is made to that websites it takes a few seconds to get the site ready and working. While you have MySQL backend associated to this website, it take a few more seconds longer to get the requested served as there is some time taken by IIS host process to get started. That is the reason after few minutes of in activity the 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

As this time, I am not sure and do not know how you can keep the websites always hot, even if moving to shared website or it is not possible at all. What I can suggest you to write your issue to Windows Azure WebSites Forum and someone from that team will provide you an appropriate answer.

AvkashChauhan
  • 20,495
  • 3
  • 34
  • 65
  • Thanks AvkashChauhan for confirming I am not going crazy! :) I have also posted this question on their VM for Azure forum. I'm a SQL Server boy at heart and have only been working with MySQL for compatibility with a client website. I'm hoping to be able to make the case now to switch us to SQL Server. – AnthonyC Sep 12 '12 at 19:50
  • @AnthonyC were you able to find a solution for this, I am facing a very similar issue http://stackoverflow.com/questions/24706027/mysql-data-mysqlclient-mysqlconnection-open-taking-a-long-time – PUG Jul 11 '14 at 21:48