OutOfMemoryException and connection pooling
Posted: Wed 16 Sep 2009 18:26
I will try and explain the situation as best I can. I think I know the problem, but not sure what to do about this currently. We are getting an outofmemoryexception and the following will cause it. Note in the information I am using below, we are using big binary files. I'm referring to say 20-30 mb.
I have created a test app to simulate the behavior also.
When the app first runs we have some calls that in turn call other things and the long and short is that we are probably adding 4 items to the connection pool by doing this. See SomeOp method below.
After that occurs, I am doing a loop that reads a big binary file and then does an update in the db. Once it hits the 3rd or 4th loop it will generally give me an OutOfMemoryException. As I'm assuming things here since I don't know for sure, I'm expecting the connection pool has these 4 connections out there from the way SomeOp works. Now, when my loop is processing these big files, it starts going down these connection pool objects. The problem is since we're using such big data, the pools in memory start to take up way too much memory. And this in turn gives us our OutOfMemoryException.
I may be totally off base here, but my question is assuming we need to use connection pooling is there any alternative aside from not allowing that many items to get in the connection pool? And/Or what can we do? We are currently on the 4.0.20 version.
internal IDataReader RetrieveSections()
{
string query = string.Format("Select * From {0}",
"soapware_customizations_customstringsections");
PgSqlConnection connection = GetConnectionPgSql();
IDbCommand command = GetCommand(connection, query);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
private void SomeOp()
{
using (RetrieveSections())
{
using (RetrieveSections())
{
using( RetrieveSections())
{
using( RetrieveSections())
{
}
}
}
}
}
I have created a test app to simulate the behavior also.
When the app first runs we have some calls that in turn call other things and the long and short is that we are probably adding 4 items to the connection pool by doing this. See SomeOp method below.
After that occurs, I am doing a loop that reads a big binary file and then does an update in the db. Once it hits the 3rd or 4th loop it will generally give me an OutOfMemoryException. As I'm assuming things here since I don't know for sure, I'm expecting the connection pool has these 4 connections out there from the way SomeOp works. Now, when my loop is processing these big files, it starts going down these connection pool objects. The problem is since we're using such big data, the pools in memory start to take up way too much memory. And this in turn gives us our OutOfMemoryException.
I may be totally off base here, but my question is assuming we need to use connection pooling is there any alternative aside from not allowing that many items to get in the connection pool? And/Or what can we do? We are currently on the 4.0.20 version.
internal IDataReader RetrieveSections()
{
string query = string.Format("Select * From {0}",
"soapware_customizations_customstringsections");
PgSqlConnection connection = GetConnectionPgSql();
IDbCommand command = GetCommand(connection, query);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
private void SomeOp()
{
using (RetrieveSections())
{
using (RetrieveSections())
{
using( RetrieveSections())
{
using( RetrieveSections())
{
}
}
}
}
}