MySQL Error “Too many connections”

问题:

I am using MySQL 5.0 for a site that is hosted by GoDaddy (linux).

I was doing some testing on my web app, and suddenly I noticed that the pages were refreshing really slowly. Finally, after a long wait, I got to a page that said something along the lines of "MySQL Error, Too many connections...", and it pointed to my config.php file which connects to the database.

It has just been me connecting to the database, no other users. On each of my pages, I include the config.php file at the top, and close the mysql connection at the end of the page. There may be several queries in between. I fear that I am not closing mysql connections enough (mysql_close()).

However, when I try to close them after running a query, I receive connection errors on the page. My pages are PHP and HTML. When I try to close a query, it seems that the next one won't connect. Would I have to include config.php again after the close in order to connect?

This error scared me because in 2 weeks, about 84 people start using this web application.

Thanks.

EDIT:

Here is some pseudo-code of my page:

 require_once('../scripts/config.php');

 <?php
    mysql_query..

    if(this button is pressed){
       mysql_query...
    }
    if(this button is pressed){
       mysql_query...
    }
    if(this button is pressed){
       mysql_query...
    }
 ?>
 some html..
 ..
 ..
 ..
 ..
 <?php
   another mysql_query...
 ?>
 some more html..
 ..
 ..
 <?php mysql_close(); ?>

I figured that this way, each time the page opens, the connection opens, and then the connection closes when the page is done loading. Then, the connection opens again when someone clicks a button on the page, and so on...

EDIT:

Okay, so I just got off the phone with GoDaddy. Apparently, with my Economy Package, I'm limited to 50 connections at a time. While my issue today happened with only me accessing the site, they said that they were having some server problems earlier. However, seeing as how I am going to have 84 users for my web app, I should probably upgrade to "Deluxe", which allows for 100 connections at a time. On a given day, there may be around 30 users accessing my site at a time, so I think the 100 would be a safer bet. Do you guys agree?

回答1:

Shared-hosting providers generally allow a pretty small amount of simultaneous connections for the same user.

What your code does is :

• open a connection to the MySQL server
• do it's stuff (generating the page)
• close the connection at the end of the page.

The last step, when done at the end of the page is not mandatory : (quoting mysql_close's manual) :

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

But note you probably shouldn't use persistent connections anyway...

Two tips :

• use mysql_connect insead of mysql_pconnect (already OK for you)
• Set the fourth parameter of mysql_connect to false (already OK for you, as it's the default value) : (quoting the manual) :

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

---

What could cause the problem, then ?

Maybe you are trying to access several pages in parallel (using multiple tabs in your browser, for instance), which will simulate several users using the website at the same time ?

If you have many users using the site at the same time and the code between mysql_connect and the closing of the connection takes lots of time, it will mean many connections being opened at the same time... And you'll reach the limit :-(

Still, as you are the only user of the application, considering you have up to 200 simultaneous connections allowed, there is something odd going on...

---

Well, thinking about "too many connections" and "max_connections"...

If I remember correctly, max_connections does not limit the number of connections you can open to the MySQL Server, but the total number of connections that can bo opened to that server, by anyone connecting to it.

Quoting MySQL's documentation on Too many connections :

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should set a larger value for this variable.

So, actually, the problem might not come from you nor your code (which looks fine, actually) : it might "just" be that you are not the only one trying to connect to that MySQL server (remember, "shared hosting"), and that there are too many people using it at the same time...

... And if I'm right and it's that, there's nothing you can do to solve the problem : as long as there are too many databases / users on that server and that max_connection is set to 200, you will continue suffering...

As a sidenote : before going back to GoDaddy asking them about that, it would be nice if someone could validate what I just said ^^

回答2:

I had about 18 months of dealing with this (http://ianchanning.wordpress.com/2010/08/25/18-months-of-dealing-with-a-mysql-too-many-connections-error/)

The solutions I had (that would apply to you) in the end were:

1. tune the database according to MySQLTuner.
2. defragment the tables weekly based on this post

Defragmenting bash script from the post:

#!/bin/bash

# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e `use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND
Data_free > 0` | grep -v '^+' | sed 's,t,.,' )"

for fragment in $FRAGMENTED_TABLES; do
  database="$( echo $fragment | cut -d. -f1 )"
  table="$( echo $fragment | cut -d. -f2 )"
  [ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;
  OPTIMIZE TABLE $table;" > /dev/null 2>&1
done

回答3:

Make sure you are not using persistent connections. This is usually a bad idea..

If you've got that .. At the very most you will need to support just as much connections as you have apache processes. Are you able to change the max_connections setting?

回答4:

Are you completely sure that the database server is completely dedicated to you?

Log on to the datbase as root and use "SHOW PROCESSLIST" to see who's connected. Ideally hook this into your monitoring system to view how many connections there are over time and alert if there are too many.

The maximum database connections can be configured in my.cnf, but watch out for running out of memory or address space.

回答5:

If you have shell access, use netstat to see how many sockets are opened to your database and where they come from.

On Linux, type:

netstat -n -a |grep 3306

On windows, type:

netstat -n -a |findstr 3306

回答6:

The solution could one of these, i came across this in a MCQA test, even i did not understood which one is right!

Set this in my.cnf "set-variable=max_connections=200"

Execute the command "SET GLOBALmax_connections = 200"

Use always mysql_connect() function in order to connect to the mysql server

Use always mysql_pconnect() function in order to connect to the mysql server

回答7:

Followings are possible solutions:

1) Increase the max connection setting by setting the global variable in mysql.

set global max_connection=200;

Note: It will increase the server load.

2) Empty your connection pool as below :

FLUSH HOSTS;

3) check your processList and kill specific processlist if you don't want any of them.

You may refer this :-

article link