Practical connection limits MySQL 5.1/5.5

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

Hey All,

Can anyone provide some guidance as to what the practical connection limits
to MySQL 5.1/5.5 are under linux?

We're running a ruby on rails application that establishes 50 to 100
connections to our database upon startup resulting in around 1,000
persistent db connections. I've been told to expect anywhere from 5 - 10x
our current transaction volume and I'm trying to predict where we're going
to top out. The servers are pretty beefy so I don't have a problem
reserving memory for connections if that's what it takes but was more
concerned about other problems that might be caused by having so many
connections.

I have started looking at doing connection concentration using MySQL Proxy
Funnel but it doesn't look like it's been updated in a while so I'm not sure
how far I'll get.

Thanks

--e0cb4e43cccb2127e204a0d3cc49--

Re: Practical connection limits MySQL 5.1/5.5

--------------enig60856150B2F3C716C3B69227
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 13.04.2011 23:50, schrieb Jeff Lee:
> Hey All,
>=20
> Can anyone provide some guidance as to what the practical connection li=
mits
> to MySQL 5.1/5.5 are under linux?
>=20
> We're running a ruby on rails application that establishes 50 to 100
> connections to our database upon startup resulting in around 1,000
> persistent db connections.=20

depends on how much RAM the box has
remind that every connection needs some MB for buffers

> I've been told to expect anywhere from 5 - 10x
> our current transaction volume and I'm trying to predict where we're go=
ing
> to top out. =20

i can not image why 1000 connections are needed in a real world applicati=
on
throw away the aüülication if it does not support connection-pooling =
in 2011

> The servers are pretty beefy so I don't have a problem
> reserving memory for connections if that's what it takes but was more
> concerned about other problems that might be caused by having so many
> connections

even if you have enough memory why will you throw it away for a
unusual connection count instead use the RAm for innodb-buffer-pool,
query-cache, key-buffers?




--------------enig60856150B2F3C716C3B69227
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2mHM8ACgkQhmBjz394AnkxwACfbuBwHWhGu3EVRdGQ0XLg 5+qb
T5cAoJENMKmvSYh9ee6ohy8Df31cgdbv
=yQJf
-----END PGP SIGNATURE-----

--------------enig60856150B2F3C716C3B69227--

RE: Practical connection limits MySQL 5.1/5.5

--_ecc5d321-c0f4-4842-9b7f-d29263c764ed_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


i agree with harald

if you're using Java you should consider pooling your database connections =
with DBCP
http://commons.apache.org/dbcp/

feel free to ping me for implementation details

takk=2C
Martin =20
______________________________________________=20
Note de d=E9ni et de confidentialit=E9
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAte=
s pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sa=
tisfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9=
e ou la copie de ceci est interdite. Ce message sert =E0 l'information seul=
ement et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant =
donn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..




> Date: Wed=2C 13 Apr 2011 23:59:43 +0200
> From: h.reindl@thelounge.net
> To: mysql@lists.mysql.com
> Subject: Re: Practical connection limits MySQL 5.1/5.5
>=20
>=20
> Am 13.04.2011 23:50=2C schrieb Jeff Lee:
> > Hey All=2C
> >=20
> > Can anyone provide some guidance as to what the practical connection li=
mits
> > to MySQL 5.1/5.5 are under linux?
> >=20
> > We're running a ruby on rails application that establishes 50 to 100
> > connections to our database upon startup resulting in around 1=2C000
> > persistent db connections.=20
>=20
> depends on how much RAM the box has
> remind that every connection needs some MB for buffers
>=20
> > I've been told to expect anywhere from 5 - 10x
> > our current transaction volume and I'm trying to predict where we're go=
ing
> > to top out. =20
>=20
> i can not image why 1000 connections are needed in a real world applicati=
on
> throw away the aüülication if it does not support connection-pooling =
in 2011
>=20
> > The servers are pretty beefy so I don't have a problem
> > reserving memory for connections if that's what it takes but was more
> > concerned about other problems that might be caused by having so many
> > connections
>=20
> even if you have enough memory why will you throw it away for a
> unusual connection count instead use the RAm for innodb-buffer-pool=2C
> query-cache=2C key-buffers?
>=20
>=20
>=20
=

--_ecc5d321-c0f4-4842-9b7f-d29263c764ed_--

Re: Practical connection limits MySQL 5.1/5.5

----- Original Message -----
> From: "Reindl Harald"
>
> even if you have enough memory why will you throw it away for a
> unusual connection count instead use the RAm for innodb-buffer-pool,
> query-cache, key-buffers?

Maybe the application doesn't have support for connection pooling and can't be easily replaced. Maybe there's just that much clients instead of a central service. Maybe there's not just a single application that uses that database.

As usual, Harald, you fail to realise that your experience does not encompass the whole of human civilisation. You seem to have a good technical background, but it might be useful to learn to consider problems from the point of view of the people who have them, at times. It tends to be a much appreciated skill in the real world.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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: Practical connection limits MySQL 5.1/5.5

--------------enigFE4547223B2ABAC5536A39EE
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Am 14.04.2011 11:50, schrieb Johan De Meersman:
> ----- Original Message -----
>> From: "Reindl Harald"
>>
>> even if you have enough memory why will you throw it away for a
>> unusual connection count instead use the RAm for innodb-buffer-pool,
>> query-cache, key-buffers?
>=20
> Maybe the application doesn't have support for connection pooling and c=
an't be easily replaced.

http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysq l.html

> Maybe there's just that much clients instead of a central service

Maybe the OP could clarify what he really does

> Maybe there's not just a single application that uses that database.

http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysq l.html

> As usual, Harald, you fail to realise that your experience does not enc=
ompass the whole of human civilisation.=20

as usual people have questions without any information what they really d=
o

> You seem to have a good technical background, but it might be useful to=
learn to=20
> consider problems from the point of view of the people who have them, a=
t times.
> It tends to be a much appreciated skill in the real world.

this is your point of view, ok

my point of view is instead having headaches about how many connections a=
re
possible without problems to consider how many connections are really
needed and without "my.cnf" (buffer settings), any information about the
workload of the applications the whole question does not make sense


--------------enigFE4547223B2ABAC5536A39EE
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2mxW4ACgkQhmBjz394AnmxCwCgnWZ5ysWczAKcQ3ixDHVM bQp1
co4AoJe0t/s5Qiv9qUzbvewWvKff97D8
=u8RQ
-----END PGP SIGNATURE-----

--------------enigFE4547223B2ABAC5536A39EE--

Re: Practical connection limits MySQL 5.1/5.5

eBay once developed a patch for pooled threads, on top of 5.0, to resolve th=
is kind of issue so they can support 10k+ sessions(massive amount of applica=
tion need to talk to those mysql).=20
=20
Not sure whether they are merged into main version though.




Best regards
Zhuchao


ÔÚ 2011-4-14£¬17:59£¬Reindl Harald д=
µÀ£º

> Am 14.04.2011 11:50, schrieb Johan De Meersman:
>> ----- Original Message -----
>>> From: "Reindl Harald"
>>>=20
>>> even if you have enough memory why will you throw it away for a
>>> unusual connection count instead use the RAm for innodb-buffer-pool,
>>> query-cache, key-buffers?
>>=20
>> Maybe the application doesn't have support for connection pooling and can=
't be easily replaced.
>=20
> http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysq l.html
>=20
>> Maybe there's just that much clients instead of a central service
>=20
> Maybe the OP could clarify what he really does
>=20
>> Maybe there's not just a single application that uses that database.
>=20
> http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysq l.html
>=20
>> As usual, Harald, you fail to realise that your experience does not encom=
pass the whole of human civilisation.=20
>=20
> as usual people have questions without any information what they really do=

>=20
>> You seem to have a good technical background, but it might be useful to l=
earn to=20
>> consider problems from the point of view of the people who have them, at t=
imes.
>> It tends to be a much appreciated skill in the real world.
>=20
> this is your point of view, ok
>=20
> my point of view is instead having headaches about how many connections ar=
e
> possible without problems to consider how many connections are really
> needed and without "my.cnf" (buffer settings), any information about the
> workload of the applications the whole question does not make sense
>=20

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