MySQL Bugs: #30116: Connector 5.0.7 throws 'out of memory exception'

Bug #30116	Connector 5.0.7 throws 'out of memory exception'
Submitted:	28 Jul 2007 17:04	Modified:	13 Nov 2007 11:44
Reporter:	William Pierce	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.0.7	OS:	Windows (Windows 2003 Server Standard Edition)
Assigned to:		CPU Architecture:	Any

Description:
I have a web page (bc.ashx) which is an ASP.net 2.0 httphandler page running under IIS 6.0 on Windows 2003 Server.  The machine has 2 GB of RAM and does nothing else apart from serving out this page.

Each request to this web page triggers a call to a stored procedure.  

Under stress test mode, I am invoking the handler routine about 20 times a second.  The routine runs fine for about 23 hours.  At the end of which it produces an 'out of memory exception'.

Stack trace follows:

System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
   at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
   at System.Text.StringBuilder.Append(String value)
   at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
   at MySql.Data.MySqlClient.MySqlConnectionStringBuilder.SetValue(String keyword, Object value)
   at MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String database)
   at MySql.Data.MySqlClient.MySqlConnection.Open()

How to repeat:
Step 1:  Create a dummy handler page (dummy.ashx).

Step 2:  Have the handler page invoke a stored procedure (mine had 6 parameters). 

Invoke the handler page using Microsof Web stress tool to simulate about 1200 requests each minute.

You should see this problem show up as noted.

I think I have been seeing the same problem and was able to reproduce with the following test code.

for (int i = 0; i < 100000; i++)
{
    MySqlConnection connection = new MySqlConnection(connectionString);
    connection.Open();

    MySqlCommand command = new MySqlCommand(commandString, connection);
    MySqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        System.Threading.Thread.Sleep(0);
    }

    connection.Close();
}

The connection string I was using had pooling enabled, and a charset of UTF8. The table I was querying had about 4 rows in it.

It seems to be allocations to the Large Object Heap. Whatching the LOH heap size in perfmon you can see that it doubles in size at regular intervals. The interval grows as the heap size grows. I am guessing that some List or Hashtable is constantly being added to, and each time it reaches its limit its size is doubled.

Simply switching MySql.Data.dll from 5.0.7.0 back to 5.0.2.0 shows flat LOH usage for the above test in perfmon.

Updating this to critical...There is no work-around available for me.  I cannot go back to 5.0.3 because there is a bug in 5.0.3 with sleeping connections that causes my app to die.  

When I use 5.0.7, I encounter this outofmemory exception.

-- Bill

Hi William and thanks for your report. I believe it is a duplicate of Bug#29123. What's your opinion?

Hi, Tonci:

Thanks for your prompt look at this.  I, in fact, checked the bug you referred me to.  It is NOT a duplicate of that.  I checked the code and in the bug I report, the connection string does not grow with each invocation.  

Do you see this differently?

Thanks,

William

William, if I was sure I would have marked the report that way immediately. I'll examine this problem with Tim's test case (thanks Tim).

William, both your stack trace and Tim's test case point to problem described in  Bug#29123. I would need a small but complete test case from you to change my mind regarding this problem.

Tonci,

Not sure why you believe that this is the same as the other bug.  

Here is what I did:  As my application makes each call to the db, I checked the connection string in the MySqlConnection object.  Each invocation showed exactly the same connection string -- it was not growing (as reported by the other bug).

If the other bug has other manifestations that I need to look for,  please let me know and I can research it in my app.  

I will also try to create a test case.

Thanks,

Bill

OK, glad to see someone new how to file an official bug.  I beleive this is the cause and solution:
http://forums.mysql.com/read.php?10,163036,168050#msg-168050

I apologize for the double post in one day.  Although, it looks to me like the fix for Bug#29123 is directly related, I don't believe it is sufficient to solve the underlying problem.  That is the StringBuilder MySqlConnectionStringBuilder::persistConString is not thread safe, but the caching mechanism MySqlConnection::connectionStringCache causes the underlying StringBuilder handles to be disbursed across several threads.  

Since even with a million Open calls the Bug#29123 would only result in approximately a 40MB memory requirement, it seems there is more involved.  My theory is that the underlying memory for the StringBuilder gets corrupted and becomes reported as an OutOfMemoryException.

Anyway, I have deployed a version of 5.0.7 patched with the fix for Bug#29123 to our server cluster which seems very good at generating these errors. :-)   We will know in a week or so if the patch turns out to be sufficient.

Thanks.

Ok Patrick. Let's see what happens. Waiting on your results. William, did you tried using latest sources of c/NET?

Patrick/Tonci:

Nice work!  Hope you are able to reproduce it!   I tried to reproduce it in a smaller test case (and couldn't) because for the smaller test case I used only one db (when I should have used multiple).  

I am going to rerun my test case with these multiple dbs (my original test case that failed used 5 db's).

I will let you know what I find.

By way of a clean workaround (instead of starting/stopping IIS), what I have done is that I have created an application pool dedicated to this high transaction throughput web service.   The app pool gets recycled every 8 hours which allows me to successfully execute without running out of memory.

I am hoping 5.0.8 or a new fix for this bug will obviate the need for this pool recycling.

-- William

William, while we're waiting for your simplified test case let me tell you that the idea to add the destructor to MySQLPool class and some methods to manage the pools using additional functions such as RemovePool or something similar is already implemented in our 5.2 branch. If you want to test it, you can build it from source-tree (check in trunk).

As your application moves between different databases (without shutting down) the c/NET creates multiple pools (because the connection strings changes). But as application does not restart all the pools are maintained and it is bound for server to run out of connections. That's the reason why you need to add DB change to your code to reproduce...

Verified as described.

Now William, can we lower the severity of this report to S4 please as it is, in fact, a feature request to add the destructor to MySQLPool class? Also, I don't believe this change will make it to 5.0 branch at all, probably too big a change.

Per Tonci's email....Changed to make it a feature request.

-- William

William, just bring you up to speed. This problem is actively been worked on and there are already changes in 5.2, in Connection.cs.

If you are able to build from sources, you'll find 5.2 in trunk folder of our SVN repository.

Thanks, Tonci.  I will try to build from source and check this out.

Do you know if this is going to be pushed into 5.1 connector?

-- William

I can confirm that this is fixed in the latest release (5.0.8.1)  5.0.8 was nevery released so 5.0.8.1 is the next release after 5.0.7.  I ran the code given above with 5.0.7 and used PerfMon to track the LOH.  It did double every few seconds while the same test with 5.0.8.1 completed the task with the LOH line completely flat.

A note has been added to the 5.0.9, 5.1.4, and 5.2.0 changelogs: 

        When running a stored procedure multiple times on the same
        connection, the memory usage could increase indefinitely.