[OT] max_connections in MySQL

Once in a while I'm getting the "Too many connections" error when accessing
MySQL from PHP. Till now I've been using the default 100 connections limit
and in this cases a "show processlist" query shows that there're 100
connections in use; most of them are in "Sleep" status.

The only idea I've been able to think of is increasing the limit to 250
connections. I just wonder if that's the right solution. It's a Red Hat 9
server with Celeron processor at 1300MHz and 250MB of RAM. How many
simultaneous connections could this machine hold? Is it normal due to the
amount of visits or I must look for another problem?

Thank you in advance for any clue,

--
-- Álvaro G. Vicario - Burgos, Spain
-- Thank you for not e-mailing me your questions
--

Alvaro G Vicario <alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote:
> Once in a while I'm getting the "Too many connections" error when accessing
> MySQL from PHP. Till now I've been using the default 100 connections limit
> and in this cases a "show processlist" query shows that there're 100
> connections in use; most of them are in "Sleep" status.

Are you using persistent connections (mysql_pconnect)? Unless you have
good reason to: don't. When used each httpd pid will keep open 1
connection, if there are more than 100 you are in trouble :)

--

Daniel Tryba

*** Daniel Tryba escribió/wrote (Wed, 6 Oct 2004 15:36:28 +0000 (UTC)):
> Are you using persistent connections (mysql_pconnect)? Unless you have
> good reason to: don't. When used each httpd pid will keep open 1
> connection, if there are more than 100 you are in trouble :)

Thank you very much for the tip. You are right, the biggest site hosted in
the server uses mysql_pconnect() and the PHP manual is rather clear about
it:

"Using persistent connections can require a bit of tuning of your Apache
and MySQL configurations to ensure that you do not exceed the number of
connections allowed by MySQL."

Even worse:

"the connection to the SQL server will not be closed when the execution of
the script ends. Instead, the link will remain open for future use
(mysql_close() will not close links established by mysql_pconnect())."

I guess that explains why most connections are just sleeping.

I'll pump the maximum to 250 (I don't have control over all hosted sites)
and I'll check carefully the usage of mysql_pconnect() in my own sites.


--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--

Alvaro G Vicario <alvaro_QUITAR_REMOVE@telecomputeronline.com>
wrote in message news:<ystqzfhvxbjx.7jurzmhub710.dlg@40tude.net>...
>
> Once in a while I'm getting the "Too many connections" error when accessing
> MySQL from PHP. Till now I've been using the default 100 connections limit
> and in this cases a "show processlist" query shows that there're 100
> connections in use; most of them are in "Sleep" status.
>
> The only idea I've been able to think of is increasing the limit to 250
> connections. I just wonder if that's the right solution. It's a Red Hat 9
> server with Celeron processor at 1300MHz and 250MB of RAM. How many
> simultaneous connections could this machine hold? Is it normal due to the
> amount of visits or I must look for another problem?

If I remember correctly, a MySQL connection requires about 50 kilobytes
on the server side. You current pool of 100 connections, therefore, takes
up about 5 megabytes. Increasing max_connections to 250 will require
about 12.5 megabytes of memory, or additional 7.5 megabytes compared to
what you have allocated now. Looks like you are quite able to spare
that much...

Cheers,
NC

Alvaro G. Vicario <kAlvaroNOSPAMTHANKS@terra.es> wrote:
>
> I'll pump the maximum to 250 (I don't have control over all hosted sites)
> and I'll check carefully the usage of mysql_pconnect() in my own sites.
>

Be sure to balance the maximum number of apache children with the
maximum number of mysql connections. IIRC connecting to a mysql server
is so fast that persistant connections aren't really necessary.

--

Daniel Tryba

*** Daniel Tryba wrote/escribió (Wed, 6 Oct 2004 20:25:50 +0000 (UTC)):
> Be sure to balance the maximum number of apache children with the
> maximum number of mysql connections. IIRC connecting to a mysql server
> is so fast that persistant connections aren't really necessary.

Most of the connections where used by only two web sites. I've been trying
non-persistent connections in my web site and I couldn't notice any
performance difference. So I've decided to increase the maximum (I think I
can hold the memory usage), monitor for a while the number of connections
(I've written a shell script) and disallow persisten connections from PHP
at php.ini:

mysql.allow_persistent=Off

This way other apps (like Postfix) can still use persistent links. Apache
can hold 150 simultaneous clients. I hope I'll never see this error again.
Thank you very much for your valuable help.


--
-- Álvaro G. Vicario - Burgos, Spain
-- Thank you for not e-mailing me your questions
--

Daniel Tryba <news_comp.lang.php@canopus.nl> wrote in message news:<ck13ds$cm0$1@news.tue.nl>...
> Alvaro G Vicario <alvaro_QUITAR_REMOVE@telecomputeronline.com> wrote:
> > Once in a while I'm getting the "Too many connections" error when accessing
> > MySQL from PHP. Till now I've been using the default 100 connections limit
> > and in this cases a "show processlist" query shows that there're 100
> > connections in use; most of them are in "Sleep" status.
>
> Are you using persistent connections (mysql_pconnect)? Unless you have
> good reason to: don't. When used each httpd pid will keep open 1
> connection, if there are more than 100 you are in trouble :)

My experience proved the other. For us, pconnect solved max
connection problem. Also, that wasn't help much when hit reached to
the max; migrating to MySQL InnoDB and then Postgre solved the issue.

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com

*** R. Rajesh Jeba Anbiah escribió/wrote (8 Oct 2004 22:28:58 -0700):
>> Are you using persistent connections (mysql_pconnect)? Unless you have
>> good reason to: don't. When used each httpd pid will keep open 1
>> connection, if there are more than 100 you are in trouble :)
>
> My experience proved the other. For us, pconnect solved max
> connection problem. Also, that wasn't help much when hit reached to
> the max; migrating to MySQL InnoDB and then Postgre solved the issue.

Since I disabled persistent connections my average open connections has
decreased from 50+ to half a dozen. I guess each server is a different
world :)

--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--