Link to home
Start Free TrialLog in
Avatar of djsoltan
djsoltan

asked on

mysql: too many connections : sleep connections

hi, we recently started getting the error msg " too many connections " on our pages with data generated from mysql.
i went to whm and checked the mysql processes and i see alot of processes for the db (soltan_events) that are sleep...
does this look normal? i think something is wrong here,,, can anyone help and give advice?
thanks

here is a sample from mysql processes
---------------------------------------

690      soltan_events      localhost      soltan_events      Sleep      2975            NULL
4165      soltan_events      localhost      soltan_events      Sleep      8449            NULL
5053      soltan_events      localhost      soltan_events      Sleep      1001            NULL
6745      soltan_events      localhost      soltan_events      Sleep      8306            NULL
10602      soltan_events      localhost      soltan_events      Sleep      559            NULL
11638      soltan_events      localhost      soltan_events      Sleep      1036            NULL
11877      soltan_events      localhost      soltan_events      Sleep      1022            NULL
14312      soltan_events      localhost      soltan_events      Sleep      2962            NULL
16702      soltan_events      localhost      soltan_events      Sleep      2964            NULL
19478      soltan_events      localhost      soltan_events      Sleep      1212            NULL
21727      soltan_events      localhost      soltan_events      Sleep      7465            NULL
24498      soltan_events      localhost      soltan_events      Sleep      954            NULL
26481      soltan_events      localhost      soltan_events      Sleep      1195            NULL
27657      soltan_events      localhost      soltan_events      Sleep      991            NULL
34192      soltan_events      localhost      soltan_events      Sleep      1668            NULL
43058      soltan_events      localhost      soltan_events      Sleep      5651            NULL
--------------------
Avatar of hernst42
hernst42
Flag of Germany image

If connections are made through a webserver and php make sure not too use mysql_pconnect which will leave the connection open, which can lead to a lot of connection to the mysql-server. Can you give more details about the environment where that happens?
Avatar of djsoltan
djsoltan

ASKER

hi,,, thank you for the quick reply,

here is the live script running

http://bia2.com/events/

ill look into the code right now to see what it is using,,

thanks
You might change the following setting in your php.ini
mysql.max_persistent
To a value of 1 or 2 so only 1 or 2 mysql-connections are kept per process Or set
mysql.allow_persistent = Off
to disallow persistent connections. A connection should still be able, but you don't need to change code, just a php-setting.

See http://www.php.net/mysql
ok, i found this,,,
it gues it is using the mysql_pconnect at one line

--------------------------
if (!isset($DB_DIE_ON_FAIL)) { $DB_DIE_ON_FAIL = true; }
if (!isset($DB_DEBUG)) { $DB_DEBUG = true; }

function db_connect($dbhost, $dbname, $dbuser, $dbpass) {
/* connect to the database $dbname on $dbhost with the user/password pair
 * $dbuser and $dbpass. */

      global $DB_DIE_ON_FAIL, $DB_DEBUG, $CONFIG;

      if (! $dbh = @mysql_pconnect($dbhost, $dbuser, $dbpass)) {
            echo "<h3>Database error encountered</h3>";
            if ($DB_DEBUG) {
                  echo "<li><strong>".ucwords($CONFIG['dbsystem'])." DB Error</strong>: ". mysql_error();
            }

            if ($DB_DIE_ON_FAIL) {
                  echo "<p>This script cannot continue until the stated errors are fixed!";
                  ob_end_flush();
                  exit;
            }
            
      }

      if (! @mysql_select_db($dbname)) {
            echo "<h3>Database error encountered</h3>";
            if ($DB_DEBUG) {
                  echo "<li><strong>".ucwords($CONFIG['dbsystem'])." DB Error</strong>: ". mysql_error();
            }

            if ($DB_DIE_ON_FAIL) {
                  echo "<p>This script cannot continue until the stated errors are fixed!";
                  ob_end_flush();
                  exit;
            }
      }

      return $dbh;
}

function db_disconnect() {
/* disconnect from the database, we normally don't have to call this function
 * because PHP will handle it */
--------------------------------------
thanks, i see what you mean,,, but,,, our users do so many things at once,,, like,,, they open up a music page,,, at the same time post on bulletin board or browse pictures and .... and i think that would not be a good option to go after...
can you please look at the code and let me know if there is a tweak i can do to fix it in the code?

thank you :)
ASKER CERTIFIED SOLUTION
Avatar of hernst42
hernst42
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
omg,,, thank you very much,,, it fixed it,,, yeyyyyyyyyyyyyyyyyyyyyyy
can i by any chance have your email address,,, i wanted to see if you would do any freelancing or not.
thank you very much,,, :D
email can be read from my profile, but currently not doing any freelancing jobs.
ok,,, thank you again for your time and excellent response :D