MySQL "Can't connect because Too many connections" ???

Gary_Kambic

Can anyone tell me why I am receiving this message when I try access my MySQL Data Base?

"Can't connect because Too many connections"

Thanks, Gary

tsinka

Hi,

the default setting of MySQL is to allow a maximum of 100 connections. You can increase that value with the configuration parameter max_connections in my.cnf.

Thomas

trooper

Depending on your scenario the use of "persistant connections" may help

Gary_Kambic

Thanks for the info.

It sounds like they are using the default. I'm going to e-mail them and request a higher limit.

Is their a limit I should request? Does 500 or 1000 sound too high. What's the downside to a higher number? More stress on the server?

tsinka

The limit depends on the server hardware, especially on the amount of installed RAM. If that variable is set to a too high value the performance of the server might become very bad.

What type of MySQL connections do you use (persistent or standard) ? There might be ways to improve the scripts depending on how you use the php MySQL functions.

Do you know which OS they use and which MySQL, apache, php versions ?

Thomas

Gary_Kambic

This is some info I received from my info() command. Maybe it will help to clarify some things.

I did email them asking what their current limit is. See below, max-persistent= Unlimted.

Maybe I should be using persistent connects. Is this going to be a mess changing from connect to pconnect? I know I have to use close_msql statements with pconnect, right?

PHP version is now 4.3.2
Linux or Apache

Linux jellyfish.bizland-cgi.com 2.2.19-immunix #2 Wed May 9 01:56:10 EDT 2001 i686

Active Persistent Links 0

Active Links 0

Client API version 3.23.32

MYSQL_MODULE_TYPE external

MYSQL_SOCKET /var/lib/mysql/mysql.sock

MYSQL_INCLUDE -I/usr/include/mysql

MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

Directive Local Value Master Value
mysql.allow_persistent On On
mysql.connect_timeout -1 -1
mysql.default_host mysqldb mysqldb
mysql.default_password no value no value
mysql.default_port no value no value
mysql.default_socket no value no value
mysql.default_user no value no value
mysql.max_links Unlimited Unlimited
mysql.max_persistent Unlimited Unlimited
mysql.trace_mode Off Off

trooper

No you don 't close pconnect connections in mysql

Weedpacket

Though note what is said on this page.

Gary_Kambic

OK. I read the article you suggested. I don't think pconnect is the way to go for my situation. Let me explain and tell me if I'm wrong.

Right now my hosting company probably has the limit of connects set to the default 100. I will try to get them to raise the limit.

I could potentially have 1,000s of different people trying to run my PHP scripts and equally the MySQL tables I have associated with them.

Is there any way around this message "Too many connections" or will the folks just have to refresh and try the page again?

What can I do in the future to prevent this, going with my own server that can handle this number of connections?

Thanks

Weedpacket

Originally posted by Gary Kambic
Is there any way around this message "Too many connections" or will the folks just have to refresh and try the page again?

A couple of thoughts occur to me.

First, minimise the amount of time spent connected - put off connecting as long as possible, do all (or at least as much as possible) of the database interaction in one go (so that the connection isn't sitting idle between queries), and close the connection as soon as you're done (instead of leaving it to be closed automatically when the script ends). If that means gathering results into arrays now and using them later on in the appropriate place in the script - there's no harm in doing so.

Second is of course to make sure your queries are as streamlined as you can manage; that they only return exactly what you need; minimise the amount of stuff that has to be sent through the connection. Other techniques to speed up the queries themselves (such as indexing) should be experimented with.

If you've got pages that are only updated occasionally, yet still drawn from the database, consider caching them: when the table is updated, a static copy of the updated portion of the page is saved to the filesystem (as static HTML, say, though PHP could be included); when the page is requested, the saved portion is included instead - and that's one less spot where you need a database connection.

And after all this, remember that if you're on a shared server chances are that yours isn't the only site trying to connect at any given time. Maybe someone else is the dbms hog.

tbach2

I had been getting this error too, and found that my problem was the setting for wait_timeout. The default setting for wait_timeout is 28800 (or 8 hours).

wait_timeout The number of seconds the server waits for activity on a not interactive connection before closing it.

I found that I was getting all my connections waiting for timeout, so I changed the value to 300, and all the problems went away 🙂

weedpacket-- thanks for the reference on pconnect, I'm gonna have to digest that a bit more...

Gary_Kambic

Thanks for all the info.

How did you set the wait_timeout variable?

Is this something my Web Hosting service has to do?

tbach2

It is in my.conf (usually /etc/my.conf)

set-variable = wait_timeout=300

Here's a reference for better understanding of this issue.

Gary_Kambic

I don't have access to the directory. I'll have to contact my web hosting service. Thanks for the info. g

Gary_Kambic

Weedpacket --- Let me ask you a question:

After some research, I have discovered that it is important to close the data base when I am done with the script. However, when I include the close statement, I receive the following error:

PHP Warning: mysql_close(): no MySQL-Link resource supplied in /hermes/data11/b364/tnevni/protection.php on line 157
PHP Warning: mysql_close(): no MySQL-Link resource supplied in /hermes/data11/b364/tnevni/protection.php on line 157

Several people have tried to resolved this error but with no luck. Do you have any thoughts as to why I am receiving this error? Thanks, Gary --- PHP version 4.3.2

Here is the script code:

$user = "user";
$password = "password";
$database = "database";
$host = "mysqldb";
mysql_connect($host,$user,$password) or die ("Can't connect because [ 001 ]".mysql_error());
mysql_select_db($database) or die ("Can't connect because [ 002 ]".mysql_error());

...

mysql_close();