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 19:04	Modified:	13 Nov 2007 12:44
Reporter:	William Pierce
Status:	Closed
Category:	Connector/Net	Severity:	S4 (Feature request)
Version:	5.0.7	OS:	Microsoft Windows (Windows 2003 Server Standard Edition)
Assigned to:		Target Version:
Triage:	D5 (Feature request)

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.