What is "unusually high" for the # of connections to MySQL?

I was wondering what would be considered "unusually high" for the # of
connections to a MySQL Server? Also, if a high number of these are in
"sleep" mode,does it make a difference?

We have a web site (a few, actually) and MySQL (Version
5.0.67-community-nt-log) running on a WS08 server, and several times now,
we have basically had the web site "crash" on us. One tech thought that it
may be the # of connections. I have seen between 100 to 125 connections or
so at one time 98% of them all from the same user. This is from our asp.net
web application that we're using for testing. The app basically becomes
unresponsive, but I'm not 100% convinced that this is a MySQL problem. The
site does not even seem to be serving up pages when it gets into this
"mode".

Also, there are other web sites on this same server (not being used a lot at
all), and these sites all seem to come up just fine. There are no connection
issues with the pages or with the data in those applications.

My main questio is this. Is 100 to 125 unusually high? I have implemented
a connection pool into my connection string in hopes that this will resolve
the problem. Here is that string:

uid=usernamer;password=password;Server=127.0.0.1;port=3306;D atabase=mydatabase;Allow
Zero Datetime=true;pooling=true; max pool size=10; min pool size=3

Someone else suggested this string, but after implementing it and
re-starting the server, we still had the same problem. My plan is to move
the app to a WS03 server tonight in hopes that the issue is the O/S.

Can anyone fill me in?

Thanks,
Jesse



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What is "unusually high" for the # of connections to MySQL?

--0016e64095685195b0048075229a
Content-Type: text/plain; charset=ISO-8859-1

It depends, but 100 is not strange at all, particularly if you have sleeping
connections
(usually due to slow page loading (ajax?) and/or persistent connections from
the app)
and any number of connections cannot crash a server, can make it slow or
unusable, but not crash it.
Watch the app, you could have for loops banging the database, a not
optimized app can kill cause a D.O.S.(=bad) of MySQL.

Anyway the point is another.
I think you cant afford guessing, it will take a huge amount of effort to
try to guess why it crashes.
Find the more information you can enabling all the logging possible, put
server parameters under graphing,
the more information you have on the crash, the less you will need to guess.
Watch, cpu(load, context switches), ram(usage,swapping), IO.

Guess less, know more.


Claudio

2010/2/26 Jesse

> I was wondering what would be considered "unusually high" for the # of
> connections to a MySQL Server? Also, if a high number of these are in
> "sleep" mode,does it make a difference?
>
> We have a web site (a few, actually) and MySQL (Version
> 5.0.67-community-nt-log) running on a WS08 server, and several times now,
> we have basically had the web site "crash" on us. One tech thought that it
> may be the # of connections. I have seen between 100 to 125 connections or
> so at one time 98% of them all from the same user. This is from our
> asp.net web application that we're using for testing. The app basically
> becomes unresponsive, but I'm not 100% convinced that this is a MySQL
> problem. The site does not even seem to be serving up pages when it gets
> into this "mode".
>
> Also, there are other web sites on this same server (not being used a lot
> at all), and these sites all seem to come up just fine. There are no
> connection issues with the pages or with the data in those applications.
>
> My main questio is this. Is 100 to 125 unusually high? I have implemented
> a connection pool into my connection string in hopes that this will resolve
> the problem. Here is that string:
>
> uid=usernamer;password=password;Server=127.0.0.1;port=3306;D atabase=mydatabase;Allow
> Zero Datetime=true;pooling=true; max pool size=10; min pool size=3
>
> Someone else suggested this string, but after implementing it and
> re-starting the server, we still had the same problem. My plan is to move
> the app to a WS03 server tonight in hopes that the issue is the O/S.
>
> Can anyone fill me in?
>
> Thanks,
> Jesse
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--0016e64095685195b0048075229a--

Re: What is "unusually high" for the # of connections to MySQL?

------=_NextPart_000_0010_01CAB649.AF4D5870
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thank you so much for the reply. I think we may have stepped outside of =
the MySQL realm now, but here is what I know:

* At least a couple times, recycling the application pool started things =
right up, but that did not always work.
* When this is going on, I cannot even get to a page itself, let alone =
execute a function that runs a query.
* One time when this happened, we moved the entire app to an OLD WS03 =
server. It had only 2 GB, I believe, and it ran like champ after that. =
Due to "circumstances beyond our control", we had to move it back to the =
WS08 server, and here we are again with the same problem.
* I can log on to the server, no problem. I can also log on to MySQL =
and run queries. I would think that if the database server were the =
problem, I would not be able to do that.
* Do do frequently get errors when this is occurring. These are asp.net =
errors. here are a few of those:
MySql.Data.MySqlClient.MySqlException: error connecting: Timeout =
expired
System.IndexOutOfRangeException: Could not find specified column in =
results
Object reference not set to an instance of an object
System.IO.IOException: Unable to write data to the transport =
connection: An existing connection was forcibly closed by the remote =
host
42000You have an error in your SQL syntax; check the manual that =
corresponds to your MySQL server version for the right syntax to use =
near ''SHOW VARIABLE''=20
Key cannot be null

The list goes on. As you can see, the errors are all over the board. =
Some make sense, some do not. For instance, the "you have an error in =
your sql" does not, because this same area of code works perfectly Many =
times throughout the day, and I or no one else has changed it. Plus, the =
one stating ''SHOW VARIABLE'' makes no sense at all. I have not =
executed such a function in my code.

Thanks,
Jesse
----- Original Message -----=20
From: Claudio Nanni=20
To: Jesse=20
Cc: mysql@lists.mysql.com=20
Sent: Thursday, February 25, 2010 6:28 PM
Subject: Re: What is "unusually high" for the # of connections to =
MySQL?


It depends, but 100 is not strange at all, particularly if you have =
sleeping connections
(usually due to slow page loading (ajax?) and/or persistent =
connections from the app)
and any number of connections cannot crash a server, can make it slow =
or unusable, but not crash it.
Watch the app, you could have for loops banging the database, a not =
optimized app can kill cause a D.O.S.(=3Dbad) of MySQL.


Anyway the point is another.
I think you cant afford guessing, it will take a huge amount of effort =
to try to guess why it crashes.
Find the more information you can enabling all the logging possible, =
put server parameters under graphing,
the more information you have on the crash, the less you will need to =
guess.
Watch, cpu(load, context switches), ram(usage,swapping), IO.


Guess less, know more.




Claudio


2010/2/26 Jesse

I was wondering what would be considered "unusually high" for the # =
of connections to a MySQL Server? Also, if a high number of these are =
in "sleep" mode,does it make a difference?

We have a web site (a few, actually) and MySQL (Version =
5.0.67-community-nt-log) running on a WS08 server, and several times =
now, we have basically had the web site "crash" on us. One tech thought =
that it may be the # of connections. I have seen between 100 to 125 =
connections or so at one time 98% of them all from the same user. This =
is from our asp.net web application that we're using for testing. The =
app basically becomes unresponsive, but I'm not 100% convinced that this =
is a MySQL problem. The site does not even seem to be serving up pages =
when it gets into this "mode".

Also, there are other web sites on this same server (not being used =
a lot at all), and these sites all seem to come up just fine. There are =
no connection issues with the pages or with the data in those =
applications.

My main questio is this. Is 100 to 125 unusually high? I have =
implemented a connection pool into my connection string in hopes that =
this will resolve the problem. Here is that string:

=
uid=3Dusernamer;password=3Dpassword;Server=3D127.0.0.1;port= 3D3306;Databa=
se=3Dmydatabase;Allow Zero Datetime=3Dtrue;pooling=3Dtrue; max pool =
size=3D10; min pool size=3D3

Someone else suggested this string, but after implementing it and =
re-starting the server, we still had the same problem. My plan is to =
move the app to a WS03 server tonight in hopes that the issue is the =
O/S.

Can anyone fill me in?

Thanks,
Jesse=20


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dclaudio.nanni@gmail.com





--=20
Claudio

------=_NextPart_000_0010_01CAB649.AF4D5870--

Re: Re: What is "unusually high" for the # of connections to MySQL?

--0016e6d99c2cf3043404807bc4ac
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes

Hi Jesse,

as you can see the most relevant is connection timed out, you could focus
on this, this problem is typical of ODBC.
this can happen because you use the persistent connection pool in your DSN
(ODBC).
So I would start focusing on the connection time out.
I could say raise the timeout time but you could always meet the problem
again,
you have to see if you can set the driver (odbc) to refresh the connection
automatically when it expires.
Also in the code if you trap the error you can refresh the connection from
the code.

This is just where I would start.

let me know

Claudio


Il giorno 26/feb/2010 00.38, Jesse ha scritto:







> Thank you so much for the reply. I think we
> may have stepped outside of the MySQL realm now, but here is what I
> know:



> * At least a couple times, recycling the
> application pool started things right up, but that did not always
> work.

> * When this is going on, I cannot even get to a
> page itself, let alone execute a function that runs a query.

> * One time when this happened, we moved the entire
> app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like
> champ after that. Due to "circumstances beyond our control", we had to
> move it
> back to the WS08 server, and here we are again with the same
> problem.

> * I can log on to the server, no problem. I
> can also log on to MySQL and run queries. I would think that if the
> database server were the problem, I would not be able to do that.

> * Do do frequently get errors when this is
> occurring. These are asp.net errors. here are a few of those:

> MySql.Data.MySqlClient.MySqlException:
> error connecting: Timeout expired


> System.IndexOutOfRangeException: Could not find specified column in
> results


> Object
> reference not set to an instance of an object


> System.IO.IOException:
> Unable to write data to the transport connection: An existing connection
> was
> forcibly closed by the remote host


> 42000You
> have an error in your SQL syntax; check the manual that corresponds to
> your
> MySQL server version for the right syntax to use near ''SHOW VARIABLE''



> Key
> cannot be null



> The
> list goes on. As you can see, the errors are all over the board. Some make
> sense, some do not. For instance, the "you have an error in your sql"
> does not,
> because this same area of code works perfectly Many times throughout the
> day, and I or no one else has changed it. Plus, the one stating ''SHOW
> VARIABLE'' makes no sense at all. I have not executed such a
> function in my code.



> Thanks,

> Jesse


> ----- Original Message -----

> From:
> Claudio
> Nanni

> To: Jesse

> Cc: mysql@lists.mysql.com

> Sent: Thursday, February 25, 2010 6:28
> PM

> Subject: Re: What is "unusually high" for
> the # of connections to MySQL?



> It depends, but 100 is not strange at all, particularly if
> you have sleeping connections
> (usually due to slow page loading (ajax?) and/or persistent connections
> from the app)

> and any number of connections cannot crash a server, can make it slow or
> unusable, but not crash it.


> Watch the app, you could have for loops banging the database, a not
> optimized app can kill cause a DOS(=bad) of MySQL.



> Anyway the point is another.

> I think you cant afford guessing, it will take a huge amount of effort to
> try to guess why it crashes.

> Find the more information you can enabling all the logging
> possible, put server parameters under graphing,

> the more information you have on the crash, the less you will need to
> guess.

> Watch, cpu(load, context switches), ram(usage,swapping), IO.




> Guess less, know more.







> Claudio





> 2010/2/26 Jesse jc@msdlg.com>

> I was wondering what would be considered "unusually high"
> for the # of connections to a MySQL Server? Also, if a high number of
> these are in "sleep" mode,does it make a difference?

> We have a web
> site (a few, actually) and MySQL (Version 5.0.67-community-nt-log)
> running on a WS08 server, and several times now, we have basically had
> the web site "crash" on us. One tech thought that it may be the # of
> connections. I have seen between 100 to 125 connections or so at one
> time 98% of them all from the same user. This is from our asp.net web
> application that we're
> using for testing. The app basically becomes unresponsive, but I'm not
> 100%
> convinced that this is a MySQL problem. The site does not even seem to
> be serving up pages when it gets into this "mode".

> Also, there are
> other web sites on this same server (not being used a lot at all), and
> these
> sites all seem to come up just fine. There are no connection issues with
> the
> pages or with the data in those applications.

> My main questio is
> this. Is 100 to 125 unusually high? I have implemented a
> connection pool into my connection string in hopes that this will resolve
> the problem. Here is that
> string:

> uid=usernamer;password=password;Server=127.0.0.1;port=3306;D atabase=mydatabase;Allow
> Zero Datetime=true;pooling=true; max pool size=10; min pool
> size=3

> Someone else suggested this string, but after implementing it
> and re-starting the server, we still had the same problem. My plan is
> to move the app to a WS03 server tonight in hopes that the issue is the
> O/S.

> Can anyone fill me in?

> Thanks,
> Jesse


> --
> MySQL General Mailing List
> For list
> archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com




> --
> Claudio






--0016e6d99c2cf3043404807bc4ac--

Re: Re: What is "unusually high" for the # of connections to MySQL?

--000e0cd32f16cd0b1704807c2342
Content-Type: text/plain; charset=ISO-8859-1

I think its more on the app side that is causing the problem.
Check if the connections are getting closed on the app side after the job is
done.

Also u might to check on slow or general query log to know what is happening
on the db side.

Also check the load on the db server.

We have db's running with close to 600 connections without any issues.

regards
anandkl

On Fri, Feb 26, 2010 at 12:53 PM, wrote:

> Hi Jesse,
>
> as you can see the most relevant is connection timed out, you could focus
> on this, this problem is typical of ODBC.
> this can happen because you use the persistent connection pool in your DSN
> (ODBC).
> So I would start focusing on the connection time out.
> I could say raise the timeout time but you could always meet the problem
> again,
> you have to see if you can set the driver (odbc) to refresh the connection
> automatically when it expires.
> Also in the code if you trap the error you can refresh the connection from
> the code.
>
> This is just where I would start.
>
> let me know
>
> Claudio
>
>
> Il giorno 26/feb/2010 00.38, Jesse ha scritto:
>
>
>
>
>
>
>
>
> Thank you so much for the reply. I think we
>> may have stepped outside of the MySQL realm now, but here is what I
>> know:
>>
>
>
>
> * At least a couple times, recycling the
>> application pool started things right up, but that did not always
>> work.
>>
>
> * When this is going on, I cannot even get to a
>> page itself, let alone execute a function that runs a query.
>>
>
> * One time when this happened, we moved the entire
>> app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like
>> champ after that. Due to "circumstances beyond our control", we had to
>> move it
>> back to the WS08 server, and here we are again with the same
>> problem.
>>
>
> * I can log on to the server, no problem. I
>> can also log on to MySQL and run queries. I would think that if the
>> database server were the problem, I would not be able to do that.
>>
>
> * Do do frequently get errors when this is
>> occurring. These are asp.net errors. here are a few of those:
>>
>
> MySql.Data.MySqlClient.MySqlException:
>> error connecting: Timeout expired
>>
>
>
> System.IndexOutOfRangeException: Could not find specified column in
>> results
>>
>
>
> Object
>> reference not set to an instance of an object
>>
>
>
> System.IO.IOException:
>> Unable to write data to the transport connection: An existing connection
>> was
>> forcibly closed by the remote host
>>
>
>
> 42000You
>> have an error in your SQL syntax; check the manual that corresponds to
>> your
>> MySQL server version for the right syntax to use near ''SHOW VARIABLE''
>>
>
>
>
> Key
>> cannot be null
>>
>
>
>
> The
>> list goes on. As you can see, the errors are all over the board. Some make
>> sense, some do not. For instance, the "you have an error in your sql" does
>> not,
>> because this same area of code works perfectly Many times throughout the
>> day, and I or no one else has changed it. Plus, the one stating ''SHOW
>> VARIABLE'' makes no sense at all. I have not executed such a
>> function in my code.
>>
>
>
>
> Thanks,
>>
>
> Jesse
>>
>
>
> ----- Original Message -----
>>
>
> From:
>> Claudio
>> Nanni
>>
>
> To: Jesse
>>
>
> Cc: mysql@lists.mysql.com
>>
>
> Sent: Thursday, February 25, 2010 6:28
>> PM
>>
>
> Subject: Re: What is "unusually high" for
>> the # of connections to MySQL?
>>
>
>
>
> It depends, but 100 is not strange at all, particularly if
>> you have sleeping connections
>> (usually due to slow page loading (ajax?) and/or persistent connections
>> from the app)
>>
>
> and any number of connections cannot crash a server, can make it slow or
>> unusable, but not crash it.
>>
>
>
> Watch the app, you could have for loops banging the database, a not
>> optimized app can kill cause a DOS(=bad) of MySQL.
>>
>
>
>
> Anyway the point is another.
>>
>
> I think you cant afford guessing, it will take a huge amount of effort to
>> try to guess why it crashes.
>>
>
> Find the more information you can enabling all the logging
>> possible, put server parameters under graphing,
>>
>
> the more information you have on the crash, the less you will need to
>> guess.
>>
>
> Watch, cpu(load, context switches), ram(usage,swapping), IO.
>>
>
>
>
>
> Guess less, know more.
>>
>
>
>
>
>
>
>
> Claudio
>>
>
>
>
>
>
> 2010/2/26 Jesse jc@msdlg.com>
>>
>
> I was wondering what would be considered "unusually high"
>> for the # of connections to a MySQL Server? Also, if a high number of
>> these are in "sleep" mode,does it make a difference?
>>
>
> We have a web
>> site (a few, actually) and MySQL (Version 5.0.67-community-nt-log)
>> running on a WS08 server, and several times now, we have basically had
>> the web site "crash" on us. One tech thought that it may be the # of
>> connections. I have seen between 100 to 125 connections or so at one
>> time 98% of them all from the same user. This is from our asp.net web
>> application that we're
>> using for testing. The app basically becomes unresponsive, but I'm not
>> 100%
>> convinced that this is a MySQL problem. The site does not even seem to
>> be serving up pages when it gets into this "mode".
>>
>
> Also, there are
>> other web sites on this same server (not being used a lot at all), and
>> these
>> sites all seem to come up just fine. There are no connection issues with
>> the
>> pages or with the data in those applications.
>>
>
> My main questio is
>> this. Is 100 to 125 unusually high? I have implemented a
>> connection pool into my connection string in hopes that this will resolve
>> the problem. Here is that
>> string:
>>
>
>
>> uid=usernamer;password=password;Server=127.0.0.1;port=3306;D atabase=mydatabase;Allow
>> Zero Datetime=true;pooling=true; max pool size=10; min pool
>> size=3
>>
>
> Someone else suggested this string, but after implementing it
>> and re-starting the server, we still had the same problem. My plan is
>> to move the app to a WS03 server tonight in hopes that the issue is the
>> O/S.
>>
>
> Can anyone fill me in?
>>
>
> Thanks,
>> Jesse
>>
>
>
> --
>> MySQL General Mailing List
>> For list
>> archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>>
>
>
>
>
> --
>> Claudio
>>
>
>
>
>
>
>

--000e0cd32f16cd0b1704807c2342--