Too many connection while using a persistent connection

Question

I have a small blog with more than 400 online visitors per minutes.

Since there is many connection request needed I'm using a persistent connection to reuse it when it's possible, here is my connection class:

<?php
    class DatabaseConnection {

        var $currCon;

        public function connect() {
            require_once '/config.php';

            try {

                $this->currCon = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD, array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", 
                    PDO::ATTR_PERSISTENT => true
                ));

            } catch (Exception $e) {
                header('HTTP/1.1 503 Service Temporarily Unavailable');
                header('Status: 503 Service Temporarily Unavailable');
                header('Retry-After: 300');
                die("Can't connect to MySQL database<br />: " . $e);
            }
        }

        public function disconnect() {
            // Unused for PERSISTENT connections
            // $this->currCon = null;
            // unset($this->currCon);
        }

        public function getDatabaseConnection() {
            return $this->currCon;
        }
    }
?>

The problem is that I got many errors which said Too many connection and That's interesting because I'm using persistent connection model!

Also I changed my MySQL service config and increasing max_connection value from 150 to 500 but the problem still persist!

Any ideas to how avoid from this error!?

are you creating new object for DatabaseConnection each time? if so then each instance of DatabaseConnection class will have separate $currCon which means for each instance there will be new connection to database. I suggest using singleton approach to solve your issue. — lihsus, Apr 01 '14 at 13:48
@lihsus Yes I create new instance per request, So you suggest me to use Singleton? — Ali, Apr 01 '14 at 13:53
Singleton won't make number of connections less than number of requests. It just can help you to make sure you you are using no more than one connection per request — Your Common Sense, Apr 01 '14 at 13:55
yes, make the connect() function static : `public static function connect()`, and create a private constructor.. `private function __construct()` — lihsus, Apr 01 '14 at 13:55
if you are sure that connect() is called only once per application, then singleton will help nothing. — Your Common Sense, Apr 01 '14 at 14:10
I found another thread that discusses similar issue http://stackoverflow.com/questions/8086059/too-many-open-mysql-connections — lihsus, Apr 01 '14 at 14:15
@NullPointer, yes you should also make `$currCon` private static. — lihsus, Apr 01 '14 at 14:18

score 1 · Answer 1 · answered Apr 01 '14 at 13:47

1

make it
```
PDO::ATTR_PERSISTENT => FALSE
```
most likely this will be enough.
Also make sure you are calling DatabaseConnection::connect() only ONCE

answered Apr 01 '14 at 13:47

Your Common Sense

156,878
40
214
345

I follow this answer and still get `Too many connections` error! Any ideas? – Ali Apr 02 '14 at 19:35
It's hart to tell just out of the head. Most likely you are still connecting more than once per application. Also there could be slow scripts that fail to release the connection. It could be that your changes to mysql config didn't applied actually. However, even 150 should be enough for the 400 per minute (unless extensive ajax-based pinging is used) – Your Common Sense Apr 03 '14 at 09:08

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

In such type of application the better approach would be not to use a persistent connection at all. If the MySQL server is on the same machine, the benefit of the persistent connection is minor.

This has been already discussed here https://stackoverflow.com/a/5323928/1800369

edited May 23 '17 at 10:31

Community

1
1

answered Apr 01 '14 at 13:48

Plamen Nikolov

2,643
1
13
24

Too many connection while using a persistent connection

2 Answers2