A few thousands simple queries seem to clog SQL server (for a while)

  • Thread starter Thread starter Patrick Questembert
  • Start date Start date
P

Patrick Questembert

I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1 Gamma on
Windows XP. The summary of the problem is this: I am doing a series of
queries (no updates) using the OleDB DataReader, and after a few thousands
queries, the SQL server seems to be clogged and starts failing all requests.
Then, after a minute or two, things return to normal. It is as if my series
of queries exhausted a resource on the server which time heals.

I am a newcomer to OleDB and SQL so it is possible that I am misusing the
OleDB .NET objects. Or am I expecting too much from MySQL? Details below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS mysum
FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no specific
error:

System.Data.OleDb.OleDbException: No error information available:
E_FAIL(0x80004005).
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

Note that I am always closing any DataReader I am using, but leaving the
OleDBConnection open (and sometimes also reuse the OleDbCommand object on
it).
The first error materializes at random times (but always after a fair number
of queries, maybe 10,000). When that first error happens, most subsequent
queries fail as well, including attempts to re-open the connection to the
database. Even a completely different application talking directly to the
server, such as the mysql utility, stops working (fails to connect to the
database).
Then, usually, waiting a few minutes helps and things get back to normal.

I went so far as inserting a 5 minutes delay in my code any time I would get
an error ... and it completely solves the problem (although hardly a
real-life solution)!

I would appreciate feedback on:
- is this likely to be a problem with my code? I mean, should ANYTHING I do
in my code cause a separate application like mysql.exe to fail?
- is it a MySQL 4.1 Gamma robustness issue? Should I stay away from MySQL
for "serious" applications?
- as I mentioned, nothing I am trying to do in the program at the point of
error seems to help (except sleeping for a couple of minutes) - MySQL seems
to be simply unreachable for a while, busy dealing with memory leaks or
whatever. But for the more normal cases in the future where such an error
could be transient, any good practices (such as closing + re-opening the
connection to the database?

Much obliged,
Patrick
 
Patrick said:
I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1 Gamma on
Windows XP. The summary of the problem is this: I am doing a series of
queries (no updates) using the OleDB DataReader, and after a few thousands
queries, the SQL server seems to be clogged and starts failing all
requests. Then, after a minute or two, things return to normal. It is as
if my series of queries exhausted a resource on the server which time heals.

I am a newcomer to OleDB and SQL so it is possible that I am misusing the
OleDB .NET objects. Or am I expecting too much from MySQL? Details below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS mysum
FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no specific
error:
Click to expand...
Note that I am always closing any DataReader I am using, but leaving the
OleDBConnection open (and sometimes also reuse the OleDbCommand object on
it).
Click to expand...

You're not explaining if the queries are executed sequential or in threads
(i.e. a test application for stresstests). It is however always good practise
to close the connection when you don't need it anymore. It will then be given
back to the pool. That is, if MySql's OleDb driver supports objectpooling of
course.

I wouldn't be surprised if closing the connection after you're done would
solve the problem.

Frans.

ps: there are some .NET providers for MySql, why aren't you using these?
 
There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does this
usually indicate?

Responding to Frans's comment: my code is not using threads, I am pretty
sure I close each and every data reader before opening a new one. I do keep
the connection to the database open for the duration of the application: I
think it makes sense, for performance reasons, since I keep calling methods
sequentially on the same database (same table actually).

However, unlike the data reader object, I don't dispose of the OleDbCommand
objects in any specific way (I just let the objects go out of scope when I
return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000 rows,
using LIMIT to look only at 60 or so matching rows. I have read in MSDN's
useful "Best pratices for ADO.NET" something about data readers needing to
be emptied when closed before all data was consumed. I don't think it is
relevant here as I do consume all returned rows. And even if I didn't, I
guess it would only be a performance issue.

Thanks!
Patrick
 
Hi Patrick,

257 TIME_WAITS and 2 out of 3306 established? LOL !!! Ok I thought so;
especially since you mentioned that the server becomes unresponsive
completely .. as if it doesn't even exist.

Before reading this answer further, do read this article I once posted on my
blog
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/06/09/15893.aspx

That explains what TIME_WAIT is, and why is that such a pain in the butt,
and why there isn't really a solution around it.

Well, there is a solution though - and that is to do chunky, not chatty
interfaces. You are connecting multiple times to the database, and closing
like a good boy as ado.net best practices recommend. Actually, another
recommendation other than closing the connection, is also to try and
minimize the number of times you connect. And when you do, try and get all
the data you can in one shot. True that sort of goes against the principle
of "Connect soon as you can", but this is why all this stuff is more of a
black art (which is why we will never loose jobs).

Essentially what I am saying is, try and minimize this spitfire of
connections. Try creating a d/b layer which has some stickiness to the
connections, but yes it does disconnect cleanly and soon as it can - but
soon as it can doesn't mean connect before your work is done.

Another possible solution is to try and connect via a means other than
TCP/IP. I am not sure if MySql will let you do it, but look into it :).

Hope that helped.

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik



Patrick Questembert said:
There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does
this usually indicate?

Responding to Frans's comment: my code is not using threads, I am pretty
sure I close each and every data reader before opening a new one. I do
keep the connection to the database open for the duration of the
application: I think it makes sense, for performance reasons, since I keep
calling methods sequentially on the same database (same table actually).

However, unlike the data reader object, I don't dispose of the
OleDbCommand objects in any specific way (I just let the objects go out of
scope when I return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000 rows,
using LIMIT to look only at 60 or so matching rows. I have read in MSDN's
useful "Best pratices for ADO.NET" something about data readers needing to
be emptied when closed before all data was consumed. I don't think it is
relevant here as I do consume all returned rows. And even if I didn't, I
guess it would only be a performance issue.

Thanks!
Patrick

Sahil Malik said:
Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik
Click to expand...
Click to expand...
 
Wow ...
From the way you describe the issue in your blog, if you are right, then one
would think this would be a well-known issue, of which the team who
implement(ed) MySQL is aware. If so, shouldn't there be a server
configuration parameter to control how long to keep these connections alive?
They do go away with time, I just need to make it happen (much) faster.

Your suggestion to grab more data each time, such as using the OleDb DataSet
object rather than the DataReader, would certainly help a lot here but
ideally I would rather see to it that the problem doesn't materialize again
the next time I have a app making frequent queries.

Thanks,
Patrick

Sahil Malik said:
Hi Patrick,

257 TIME_WAITS and 2 out of 3306 established? LOL !!! Ok I thought so;
especially since you mentioned that the server becomes unresponsive
completely .. as if it doesn't even exist.

Before reading this answer further, do read this article I once posted on
my blog
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/06/09/15893.aspx

That explains what TIME_WAIT is, and why is that such a pain in the butt,
and why there isn't really a solution around it.

Well, there is a solution though - and that is to do chunky, not chatty
interfaces. You are connecting multiple times to the database, and closing
like a good boy as ado.net best practices recommend. Actually, another
recommendation other than closing the connection, is also to try and
minimize the number of times you connect. And when you do, try and get all
the data you can in one shot. True that sort of goes against the principle
of "Connect soon as you can", but this is why all this stuff is more of a
black art (which is why we will never loose jobs).

Essentially what I am saying is, try and minimize this spitfire of
connections. Try creating a d/b layer which has some stickiness to the
connections, but yes it does disconnect cleanly and soon as it can - but
soon as it can doesn't mean connect before your work is done.

Another possible solution is to try and connect via a means other than
TCP/IP. I am not sure if MySql will let you do it, but look into it :).

Hope that helped.

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik



Patrick Questembert said:
There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does
this usually indicate?

Responding to Frans's comment: my code is not using threads, I am pretty
sure I close each and every data reader before opening a new one. I do
keep the connection to the database open for the duration of the
application: I think it makes sense, for performance reasons, since I
keep calling methods sequentially on the same database (same table
actually).

However, unlike the data reader object, I don't dispose of the
OleDbCommand objects in any specific way (I just let the objects go out
of scope when I return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000 rows,
using LIMIT to look only at 60 or so matching rows. I have read in MSDN's
useful "Best pratices for ADO.NET" something about data readers needing
to be emptied when closed before all data was consumed. I don't think it
is relevant here as I do consume all returned rows. And even if I didn't,
I guess it would only be a performance issue.

Thanks!
Patrick

Sahil Malik said:
Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik


I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1 Gamma
on Windows XP. The summary of the problem is this: I am doing a series
of queries (no updates) using the OleDB DataReader, and after a few
thousands queries, the SQL server seems to be clogged and starts failing
all requests. Then, after a minute or two, things return to normal. It
is as if my series of queries exhausted a resource on the server which
time heals.

I am a newcomer to OleDB and SQL so it is possible that I am misusing
the OleDB .NET objects. Or am I expecting too much from MySQL? Details
below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS
mysum FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no
specific error:

System.Data.OleDb.OleDbException: No error information available:
E_FAIL(0x80004005).
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

Note that I am always closing any DataReader I am using, but leaving
the OleDBConnection open (and sometimes also reuse the OleDbCommand
object on it).
The first error materializes at random times (but always after a fair
number of queries, maybe 10,000). When that first error happens, most
subsequent queries fail as well, including attempts to re-open the
connection to the database. Even a completely different application
talking directly to the server, such as the mysql utility, stops
working (fails to connect to the database).
Then, usually, waiting a few minutes helps and things get back to
normal.

I went so far as inserting a 5 minutes delay in my code any time I
would get an error ... and it completely solves the problem (although
hardly a real-life solution)!

I would appreciate feedback on:
- is this likely to be a problem with my code? I mean, should ANYTHING
I do in my code cause a separate application like mysql.exe to fail?
- is it a MySQL 4.1 Gamma robustness issue? Should I stay away from
MySQL for "serious" applications?
- as I mentioned, nothing I am trying to do in the program at the point
of error seems to help (except sleeping for a couple of minutes) -
MySQL seems to be simply unreachable for a while, busy dealing with
memory leaks or whatever. But for the more normal cases in the future
where such an error could be transient, any good practices (such as
closing + re-opening the connection to the database?

Much obliged,
Patrick
Click to expand...
Click to expand...
Click to expand...
 
It is indeed a well known issue, and SQL Server, WebServices, all such
platforms that rely on such communication have some way or the other of
getting around this problem. I am not sure what exactly MySql supports. Do
you have the option of switching to MSDE?

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik




Patrick Questembert said:
Wow ...
From the way you describe the issue in your blog, if you are right, then
one would think this would be a well-known issue, of which the team who
implement(ed) MySQL is aware. If so, shouldn't there be a server
configuration parameter to control how long to keep these connections
alive? They do go away with time, I just need to make it happen (much)
faster.

Your suggestion to grab more data each time, such as using the OleDb
DataSet object rather than the DataReader, would certainly help a lot here
but ideally I would rather see to it that the problem doesn't materialize
again the next time I have a app making frequent queries.

Thanks,
Patrick

Sahil Malik said:
Hi Patrick,

257 TIME_WAITS and 2 out of 3306 established? LOL !!! Ok I thought so;
especially since you mentioned that the server becomes unresponsive
completely .. as if it doesn't even exist.

Before reading this answer further, do read this article I once posted on
my blog
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/06/09/15893.aspx

That explains what TIME_WAIT is, and why is that such a pain in the butt,
and why there isn't really a solution around it.

Well, there is a solution though - and that is to do chunky, not chatty
interfaces. You are connecting multiple times to the database, and
closing like a good boy as ado.net best practices recommend. Actually,
another recommendation other than closing the connection, is also to try
and minimize the number of times you connect. And when you do, try and
get all the data you can in one shot. True that sort of goes against the
principle of "Connect soon as you can", but this is why all this stuff is
more of a black art (which is why we will never loose jobs).

Essentially what I am saying is, try and minimize this spitfire of
connections. Try creating a d/b layer which has some stickiness to the
connections, but yes it does disconnect cleanly and soon as it can - but
soon as it can doesn't mean connect before your work is done.

Another possible solution is to try and connect via a means other than
TCP/IP. I am not sure if MySql will let you do it, but look into it :).

Hope that helped.

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik



Patrick Questembert said:
There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does
this usually indicate?

Responding to Frans's comment: my code is not using threads, I am pretty
sure I close each and every data reader before opening a new one. I do
keep the connection to the database open for the duration of the
application: I think it makes sense, for performance reasons, since I
keep calling methods sequentially on the same database (same table
actually).

However, unlike the data reader object, I don't dispose of the
OleDbCommand objects in any specific way (I just let the objects go out
of scope when I return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000 rows,
using LIMIT to look only at 60 or so matching rows. I have read in
MSDN's useful "Best pratices for ADO.NET" something about data readers
needing to be emptied when closed before all data was consumed. I don't
think it is relevant here as I do consume all returned rows. And even if
I didn't, I guess it would only be a performance issue.

Thanks!
Patrick

Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik


I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1 Gamma
on Windows XP. The summary of the problem is this: I am doing a series
of queries (no updates) using the OleDB DataReader, and after a few
thousands queries, the SQL server seems to be clogged and starts
failing all requests. Then, after a minute or two, things return to
normal. It is as if my series of queries exhausted a resource on the
server which time heals.

I am a newcomer to OleDB and SQL so it is possible that I am misusing
the OleDB .NET objects. Or am I expecting too much from MySQL? Details
below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS
mysum FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no
specific error:

System.Data.OleDb.OleDbException: No error information available:
E_FAIL(0x80004005).
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

Note that I am always closing any DataReader I am using, but leaving
the OleDBConnection open (and sometimes also reuse the OleDbCommand
object on it).
The first error materializes at random times (but always after a fair
number of queries, maybe 10,000). When that first error happens, most
subsequent queries fail as well, including attempts to re-open the
connection to the database. Even a completely different application
talking directly to the server, such as the mysql utility, stops
working (fails to connect to the database).
Then, usually, waiting a few minutes helps and things get back to
normal.

I went so far as inserting a 5 minutes delay in my code any time I
would get an error ... and it completely solves the problem (although
hardly a real-life solution)!

I would appreciate feedback on:
- is this likely to be a problem with my code? I mean, should ANYTHING
I do in my code cause a separate application like mysql.exe to fail?
- is it a MySQL 4.1 Gamma robustness issue? Should I stay away from
MySQL for "serious" applications?
- as I mentioned, nothing I am trying to do in the program at the
point of error seems to help (except sleeping for a couple of
minutes) - MySQL seems to be simply unreachable for a while, busy
dealing with memory leaks or whatever. But for the more normal cases
in the future where such an error could be transient, any good
practices (such as closing + re-opening the connection to the
database?

Much obliged,
Patrick
Click to expand...
Click to expand...
Click to expand...
 
I actually started out with MSDE, but:
- MSDE install was so quiet that I was left wondering if it actually
installed - I was told nothing about where the data files are etc
- I also could find no admin tools or utilities to test the MSDE health
- then any attempt I made to connect to MSDE failed

I still don't know if MSDE was really installed or not.

In contrast, MySQL 4.1 setup is simple while still letting you know what
choices it's making and where the data files are, and comes with simple
utilities and guidelines how to test the server is working and it's really
easy to play with a command-line tool that's included to create simple
tables and add data to them.

Note that I am NOT biased against Microsoft tools and products - I worked
there 8 years (in the NT dev team) :-)

Sahil Malik said:
It is indeed a well known issue, and SQL Server, WebServices, all such
platforms that rely on such communication have some way or the other of
getting around this problem. I am not sure what exactly MySql supports. Do
you have the option of switching to MSDE?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik




Patrick Questembert said:
Wow ...
From the way you describe the issue in your blog, if you are right, then
one would think this would be a well-known issue, of which the team who
implement(ed) MySQL is aware. If so, shouldn't there be a server
configuration parameter to control how long to keep these connections
alive? They do go away with time, I just need to make it happen (much)
faster.

Your suggestion to grab more data each time, such as using the OleDb
DataSet object rather than the DataReader, would certainly help a lot
here but ideally I would rather see to it that the problem doesn't
materialize again the next time I have a app making frequent queries.

Thanks,
Patrick

Sahil Malik said:
Hi Patrick,

257 TIME_WAITS and 2 out of 3306 established? LOL !!! Ok I thought so;
especially since you mentioned that the server becomes unresponsive
completely .. as if it doesn't even exist.

Before reading this answer further, do read this article I once posted
on my blog
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/06/09/15893.aspx

That explains what TIME_WAIT is, and why is that such a pain in the
butt, and why there isn't really a solution around it.

Well, there is a solution though - and that is to do chunky, not chatty
interfaces. You are connecting multiple times to the database, and
closing like a good boy as ado.net best practices recommend. Actually,
another recommendation other than closing the connection, is also to try
and minimize the number of times you connect. And when you do, try and
get all the data you can in one shot. True that sort of goes against the
principle of "Connect soon as you can", but this is why all this stuff
is more of a black art (which is why we will never loose jobs).

Essentially what I am saying is, try and minimize this spitfire of
connections. Try creating a d/b layer which has some stickiness to the
connections, but yes it does disconnect cleanly and soon as it can - but
soon as it can doesn't mean connect before your work is done.

Another possible solution is to try and connect via a means other than
TCP/IP. I am not sure if MySql will let you do it, but look into it :).

Hope that helped.

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik



There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does
this usually indicate?

Responding to Frans's comment: my code is not using threads, I am
pretty sure I close each and every data reader before opening a new
one. I do keep the connection to the database open for the duration of
the application: I think it makes sense, for performance reasons, since
I keep calling methods sequentially on the same database (same table
actually).

However, unlike the data reader object, I don't dispose of the
OleDbCommand objects in any specific way (I just let the objects go out
of scope when I return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000
rows, using LIMIT to look only at 60 or so matching rows. I have read
in MSDN's useful "Best pratices for ADO.NET" something about data
readers needing to be emptied when closed before all data was consumed.
I don't think it is relevant here as I do consume all returned rows.
And even if I didn't, I guess it would only be a performance issue.

Thanks!
Patrick

Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik


I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1
Gamma on Windows XP. The summary of the problem is this: I am doing a
series of queries (no updates) using the OleDB DataReader, and after a
few thousands queries, the SQL server seems to be clogged and starts
failing all requests. Then, after a minute or two, things return to
normal. It is as if my series of queries exhausted a resource on the
server which time heals.

I am a newcomer to OleDB and SQL so it is possible that I am misusing
the OleDB .NET objects. Or am I expecting too much from MySQL?
Details below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS
mysum FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no
specific error:

System.Data.OleDb.OleDbException: No error information available:
E_FAIL(0x80004005).
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

Note that I am always closing any DataReader I am using, but leaving
the OleDBConnection open (and sometimes also reuse the OleDbCommand
object on it).
The first error materializes at random times (but always after a fair
number of queries, maybe 10,000). When that first error happens, most
subsequent queries fail as well, including attempts to re-open the
connection to the database. Even a completely different application
talking directly to the server, such as the mysql utility, stops
working (fails to connect to the database).
Then, usually, waiting a few minutes helps and things get back to
normal.

I went so far as inserting a 5 minutes delay in my code any time I
would get an error ... and it completely solves the problem (although
hardly a real-life solution)!

I would appreciate feedback on:
- is this likely to be a problem with my code? I mean, should
ANYTHING I do in my code cause a separate application like mysql.exe
to fail?
- is it a MySQL 4.1 Gamma robustness issue? Should I stay away from
MySQL for "serious" applications?
- as I mentioned, nothing I am trying to do in the program at the
point of error seems to help (except sleeping for a couple of
minutes) - MySQL seems to be simply unreachable for a while, busy
dealing with memory leaks or whatever. But for the more normal cases
in the future where such an error could be transient, any good
practices (such as closing + re-opening the connection to the
database?

Much obliged,
Patrick
Click to expand...
Click to expand...
Click to expand...
 
I can't stop laughing reading how you describe the MSDE install. You do have
a good point there.

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik


Patrick Questembert said:
I actually started out with MSDE, but:
- MSDE install was so quiet that I was left wondering if it actually
installed - I was told nothing about where the data files are etc
- I also could find no admin tools or utilities to test the MSDE health
- then any attempt I made to connect to MSDE failed

I still don't know if MSDE was really installed or not.

In contrast, MySQL 4.1 setup is simple while still letting you know what
choices it's making and where the data files are, and comes with simple
utilities and guidelines how to test the server is working and it's really
easy to play with a command-line tool that's included to create simple
tables and add data to them.

Note that I am NOT biased against Microsoft tools and products - I worked
there 8 years (in the NT dev team) :-)

Sahil Malik said:
It is indeed a well known issue, and SQL Server, WebServices, all such
platforms that rely on such communication have some way or the other of
getting around this problem. I am not sure what exactly MySql supports.
Do you have the option of switching to MSDE?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik




Patrick Questembert said:
Wow ...
From the way you describe the issue in your blog, if you are right, then
one would think this would be a well-known issue, of which the team who
implement(ed) MySQL is aware. If so, shouldn't there be a server
configuration parameter to control how long to keep these connections
alive? They do go away with time, I just need to make it happen (much)
faster.

Your suggestion to grab more data each time, such as using the OleDb
DataSet object rather than the DataReader, would certainly help a lot
here but ideally I would rather see to it that the problem doesn't
materialize again the next time I have a app making frequent queries.

Thanks,
Patrick

Hi Patrick,

257 TIME_WAITS and 2 out of 3306 established? LOL !!! Ok I thought so;
especially since you mentioned that the server becomes unresponsive
completely .. as if it doesn't even exist.

Before reading this answer further, do read this article I once posted
on my blog
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2004/06/09/15893.aspx

That explains what TIME_WAIT is, and why is that such a pain in the
butt, and why there isn't really a solution around it.

Well, there is a solution though - and that is to do chunky, not chatty
interfaces. You are connecting multiple times to the database, and
closing like a good boy as ado.net best practices recommend. Actually,
another recommendation other than closing the connection, is also to
try and minimize the number of times you connect. And when you do, try
and get all the data you can in one shot. True that sort of goes
against the principle of "Connect soon as you can", but this is why all
this stuff is more of a black art (which is why we will never loose
jobs).

Essentially what I am saying is, try and minimize this spitfire of
connections. Try creating a d/b layer which has some stickiness to the
connections, but yes it does disconnect cleanly and soon as it can -
but soon as it can doesn't mean connect before your work is done.

Another possible solution is to try and connect via a means other than
TCP/IP. I am not sure if MySql will let you do it, but look into it :).

Hope that helped.

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik



There are indeed 257 connections stuck in TIME_WAIT right now (and
increasing)! Only 2 connections on 3306 show as established. What does
this usually indicate?

Responding to Frans's comment: my code is not using threads, I am
pretty sure I close each and every data reader before opening a new
one. I do keep the connection to the database open for the duration of
the application: I think it makes sense, for performance reasons,
since I keep calling methods sequentially on the same database (same
table actually).

However, unlike the data reader object, I don't dispose of the
OleDbCommand objects in any specific way (I just let the objects go
out of scope when I return from the method), for example:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
OleDbDataReader myReader = catCMD.ExecuteReader();
myReader.Read() ... myReader.Close();
catCMD.ExecuteNonQuery();
[ leave scope, do nothing special to the catCMD object ]

Note that many of the queries are SELECT's on a table with 200,000
rows, using LIMIT to look only at 60 or so matching rows. I have read
in MSDN's useful "Best pratices for ADO.NET" something about data
readers needing to be emptied when closed before all data was
consumed. I don't think it is relevant here as I do consume all
returned rows. And even if I didn't, I guess it would only be a
performance issue.

Thanks!
Patrick

Do a netstat-n on your client or server machine, do you see a lot of
connections stuck in TIME_WAIT?

- Sahil Malik
You can reach me thru my blog
http://www.dotnetjunkies.com/weblog/sahilmalik


I am using the VS.NET 2003 OleDB objects and C# against MySQL 4.1
Gamma on Windows XP. The summary of the problem is this: I am doing a
series of queries (no updates) using the OleDB DataReader, and after
a few thousands queries, the SQL server seems to be clogged and
starts failing all requests. Then, after a minute or two, things
return to normal. It is as if my series of queries exhausted a
resource on the server which time heals.

I am a newcomer to OleDB and SQL so it is possible that I am
misusing the OleDB .NET objects. Or am I expecting too much from
MySQL? Details below.

After successfully running a few thousands queries of the type:

OleDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = SELECT t1.mysum FROM (SELECT SUM(negative) AS
mysum FROM myTable WHERE time >='2003-08-22 16:00:00') AS t1;
myReader = catCMD.ExecuteReader();

at some point, the ExecuteReader command stops working, with no
specific error:

System.Data.OleDb.OleDbException: No error information available:
E_FAIL(0x80004005).
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

Note that I am always closing any DataReader I am using, but leaving
the OleDBConnection open (and sometimes also reuse the OleDbCommand
object on it).
The first error materializes at random times (but always after a
fair number of queries, maybe 10,000). When that first error
happens, most subsequent queries fail as well, including attempts to
re-open the connection to the database. Even a completely different
application talking directly to the server, such as the mysql
utility, stops working (fails to connect to the database).
Then, usually, waiting a few minutes helps and things get back to
normal.

I went so far as inserting a 5 minutes delay in my code any time I
would get an error ... and it completely solves the problem
(although hardly a real-life solution)!

I would appreciate feedback on:
- is this likely to be a problem with my code? I mean, should
ANYTHING I do in my code cause a separate application like mysql.exe
to fail?
- is it a MySQL 4.1 Gamma robustness issue? Should I stay away from
MySQL for "serious" applications?
- as I mentioned, nothing I am trying to do in the program at the
point of error seems to help (except sleeping for a couple of
minutes) - MySQL seems to be simply unreachable for a while, busy
dealing with memory leaks or whatever. But for the more normal cases
in the future where such an error could be transient, any good
practices (such as closing + re-opening the connection to the
database?

Much obliged,
Patrick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top