Pool resource leak / issue?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Pool resource leak / issue?

Post by LarsOberg » Wed 23 May 2012 01:19

Hello,

There seems to be an issue with dotConnect for MySQL v6.80.332.0. We have an application that runs in an unattended mode 24x7, with frequent and heavy MySQL interactions against a couple of MySQL servers. It runs just fine for 2-3 days, after which I get this exception:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.MySql.MySqlConnection.Open()

I have tried calling MySqlConnection.ClearAllPools(true) every 10 minutes, but that did not help. When the exception happens, there seems to be no way to call MySqlConnection.Open() without getting this exception (again, ClearAllPools does not help), other than calling it with "pooling=false" in the connection string.

I cannot reproduce this at will, but please look into what could be causing this issue and try to address it. Or maybe you could email me a debug version of the DLLs that would provide you with more data when it happens. I am now running with pooling turned off, but that is very slow and I really need to be able to use pooling.

Thanks,
Lars

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Fri 25 May 2012 11:25

The exception "System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." occurs when the max pool size is reached and all connections are used. This exception can occur in case you don't close a connection after using it somewhere in your code.

You can increase the max pool size, if necessary. You can also monitor the pool state via dbMonitor.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Fri 25 May 2012 14:19

We are closing and disposing every connection, I have already verified that.

Also, I have tried from the VS debugger to manually call ClearAllPools() when this exception occurs, and then try to execute Open connection again, but still get the same exception.

If it is a matter of max pool size being reached, why would ClearAllPools() not handle this??

Lars

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Tue 29 May 2012 11:40

We have reproduced the issue with the pool not being cleared after calling the methods ClearAllPools or ClearPool. We will investigate it and notify you about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Thu 31 May 2012 10:26

LarsOberg wrote:System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
This exception occurs when the number of open connections is equal to the MaxPoolSize property value. Herewith, there are no unused connections in the pool, othewise, when opening a connection, it would have been taken from the pool and the exception would not have occurred.
LarsOberg wrote:We are closing and disposing every connection, I have already verified that.
This exception can occur when you use simultaneously more connections than it is specified in the MaxPoolSize value, i.e. if you have MaxPoolSize = 100, and you have 100 open connections, all of them are used and none of them has closed yet, then when opening the 101st connection, we will get the exception specified. In this case ClearAllPools will not help, as this property clears the pool of unused connections present in the pool, but doesn't influence the open connections.

We can offer you several workarounds:
1) If you use simultaneously the same number of connections on the average (e.g. 10) and sometimes, e.g. once or twice a day, a peak value of e.g. 200 simultaneous connections occurs, we advise you to set the MaxPoolSize value to about 20, and at the peaks open these connections with an additional parameter 'Pooling=false;' inside the catch block;

2) If peaks with lots of simultaneous connections occur oftener, you can increase the MaxPoolSize property value to the number of connections at the peak. Therefore, after a peak you will have e.g. 200 connections in the pool, and about 10 of them will be used. In this case you can call the ClearAllPools method every hour (this is a conditional value, and another timespan can be more optimal for you), not to keep a lot of extra open connections.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Thu 31 May 2012 16:50

Pinturiccio wrote:This exception occurs when the number of open connections is equal to the MaxPoolSize property value. Herewith, there are no unused connections in the pool, othewise, when opening a connection, it would have been taken from the pool and the exception would not have occurred.
I am using the default MaxPoolSize (100) and I do not have anywhere near that many connections open at any one time. As far as I understand it, you identify a unique connection by the exact connection string, so when a connection is opened and there already is a connection with an identical connection string in the pool, the connection should be taken from the pool. Please confirm - is this correct?

I ran DBMonitor to check out the pool usage, and the unique connections peaked at around 20 (and these were not open at the same time - just in the pool). I did this by "Save Log to text file" and counting the number of unique connection strings in the "Open Connection" lines. I also did a count of total number of "Open Connections" lines as well as total number of "Close connection Complete" lines, and I got 1472 for both (which confirms that I am not leaving connections open).

Also, this app is very "repetitive" - it just grabs orders from a database and sends some inventory feeds, etc., so the number of connections it opens is fairly constant - it opens, uses and closes the same connections over and over (so the connections in the pool should stay around 20). And remember that the "pool exception" comes after the app has been running for 2-3 days; if it were a matter of the app opening up more than 100 connections, the exception would have come within the first hour (since everything after that is just a repetition). So there is probably some kind of leak somewhere that makes it eventually get to this state.

Please advise.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Fri 01 Jun 2012 12:44

LarsOberg wrote:As far as I understand it, you identify a unique connection by the exact connection string, so when a connection is opened and there already is a connection with an identical connection string in the pool, the connection should be taken from the pool. Please confirm - is this correct?
Yes, you are right.
LarsOberg wrote:I also did a count of total number of "Open Connections" lines as well as total number of "Close connection Complete" lines, and I got 1472 for both (which confirms that I am not leaving connections open).
This means that you close all connections.
LarsOberg wrote:I ran DBMonitor to check out the pool usage, and the unique connections peaked at around 20 (and these were not open at the same time - just in the pool)
If you do not open the connections at the same time, you have only one connection in the pool.
According to you, you have the following algorithm:
1) You open the connection;
2) Unless this connection is closed, you don't open other connections;
3) You close this connection and it goes to the pool;
4) You open a connection with the same connection string, the connection is taken from the pool, this is followed by point 2)
5) If you open a connection with another connection string, it will belong to another pool.

If you open connections when a connection with the same connection string is already open, there will be one connection in the pool, not 20. It is likely, that a connection with the same connection string is opened in another thread, while the first connection has not been closed yet. In this case there are 2 connections in your pool. Your connections can open non-simultaneously, but when other connections have not been closed yet.
LarsOberg wrote:And remember that the "pool exception" comes after the app has been running for 2-3 days; if it were a matter of the app opening up more than 100 connections, the exception would have come within the first hour (since everything after that is just a repetition).
1. Do you open connections automatically after some period of time, or after some event, or at user requests?
2. Do you use threads in your application or just one connection can be open and unless it is closed other connections cannot be opened?
3. Could you please send us a snippet of code where you open, use and close connections, and the reasons for calling this code?
4. If it is possible, create and send us a small test project for reproducing the issue.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Fri 01 Jun 2012 14:49

Pinturiccio wrote: If you open connections when a connection with the same connection string is already open, there will be one connection in the pool, not 20. It is likely, that a connection with the same connection string is opened in another thread, while the first connection has not been closed yet. In this case there are 2 connections in your pool. Your connections can open non-simultaneously, but when other connections have not been closed yet.
I probably was unclear, so let me clarify this: The application does use more than one connection string (it connects to several databases on a couple of different MySQL servers, so there will of course be several connections in the pool - but it does not use over 100 different connections (or anywhere near that many), so the pool should not run out. What I did was to export the DB Monitor log to a file, from which I just filtered out all the lines that contained "Open connection" (since these are the lines that contain the connection strings). Then I simply extracted the connection string portion from these lines + sorted and removed duplicates from these, which gave me all unique connection strings. The total count of unique connection strings is 6 (not 20 as I said earlier, sorry).

The app is driven by an external scheduler that creates flag files that tell the app what to do (like GetOrders, SendItemFeed, SendQtyFeed, etc.). So there are a handful of actions that the app perform over and over throughout the day.

There is only one thread, but more than one connection can be opened at the same time (for example: one connection to the source database and one to the destination database).

As I mentioned in my original post, I cannot reproduce this issue in a small test app (it even takes 2-3 days of running 24x7 in the real app before it happens). Maybe a debug-build of your DLLs that gives you more data when it happens would help?

Please advise.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Tue 05 Jun 2012 15:52

Bump?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Wed 06 Jun 2012 12:12

LarsOberg wrote:The total count of unique connection strings is 6 (not 20 as I said earlier, sorry)
Every unique connection string has a separate connection pool. If you have 6 unique connection strings, then you have 6 connection pools and every connection pool has MaxPoolSize=100 by default. It is not how many connections with unique connection strings you have opened simultaneously, it is how many connections with the same connection string you have opened.

We have tested possible variants when connections are opened and closed, when connections are reset by the server and other situations, but for now we didn't manage to reproduce the issue.

Could you please send us the dbMonitor log text file. If it is really large, send us the log for several hours before the issue occurred.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Thu 07 Jun 2012 01:38

I have uploaded the log file as requested. Please let me know what you find.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Pool resource leak / issue?

Post by Pinturiccio » Thu 07 Jun 2012 14:04

We have answered you by e-mail.

park
Posts: 8
Joined: Tue 08 Jun 2010 01:45

Re: Pool resource leak / issue?

Post by park » Thu 21 Feb 2013 05:58

Can you please post the information that you emailed here in the forum so that other users with a similar problem may also benefit.

LarsOberg
Posts: 41
Joined: Tue 11 Oct 2005 18:45

Re: Pool resource leak / issue?

Post by LarsOberg » Thu 21 Feb 2013 06:14

It has been a while now, but if I remember correctly, this turned out to be a problem with a stored procedure that I was calling, which caused an exception to be thrown (and silently eaten by the app), which in turn caused the connection not to be closed properly. So it was nothing wrong with Devart's component.

park
Posts: 8
Joined: Tue 08 Jun 2010 01:45

Re: Pool resource leak / issue?

Post by park » Thu 21 Feb 2013 06:16

Thanks for the reply. Appreciate it.

Post Reply