PHP » Singleton MySQL connection

Question

The following function is the old way for MySQL connections

function dbconn($autoclean = false, $mysql_close = false)
{
    global $mysql_host, $mysql_user, $mysql_pass, $mysql_db;
    mysql_connect($mysql_host, $mysql_user, $mysql_pass);
    mysql_select_db($mysql_db);

    userlogin();

    if ($autoclean)
        register_shutdown_function("autoclean");

    if ($mysql_close)
        register_shutdown_function("mysql_close");
}

Well... I've been reported by my dedicated server, something like "Use Singleton because you are spamming the mysql connections", so I code a singleton class for MySQL but the spam is still there.

<?php
    class mysql {
        private static $instance;
        private $res;
        private function __construct() 
        {
            $this->res = mysql_connect('localhost','user','password');
            mysql_select_db('database', $this->res);
        }
        public static function getinstance() {
            if(!isset(self::$instance)) {
                $c = __CLASS__;
                self::$instance = new $c;
            }
            return self::$instance;
        }
        public function getres() {
            return $this->res;
        }
    }
?>

and the function ...

function dbconn($autoclean = false, $mysql_close = false, $file = '')
{
    require_once("MySQL.php");
    $m = mysql::getinstance();
    $m->getres();

    userlogin();

    if ($autoclean)
        register_shutdown_function("autoclean");

    //if ($mysql_close)
    //  register_shutdown_function("mysql_close");
}

Thanks !

The singleton is not working.. and the spam is still there.. — Daniela Răducanu, Feb 26 '12 at 19:11
Not really sure that the host admins are talking about. You may be opening multiple connections per script if you call `dbconn()` several times, but that has nothing to do with spam, and switching to a singleton, though it may reduce the active connections, isn't going to make spam go away either. — Michael Berkowski, Feb 26 '12 at 19:12
@Michael even if this function called several times, it will use only one connection per script — Your Common Sense, Feb 26 '12 at 19:23

score 1 · Answer 1 · edited May 23 '17 at 12:20

1

The Singleton will only last for the length of a single HTTP request for a single visitor and will then be cleared - unless you do some caching. So it would seem that your connection spamming is per-request. What you want is some kind of connection pooling as discussed here: Connection pooling in PHP.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 26 '12 at 19:16

msgmash.com

1,035
5
10

score 1 · Accepted Answer · answered Feb 26 '12 at 19:17

Note that this still opens a connection per request. If your site is popular and the server is slow and misconfigured, it might lead to a big number of concurrent connections and choke the database even further. In that case I can only recommend changing the server or the hoster )

Of course, your queries can also be slow which could lead to excess load in MySQL. If your site is popular indeed.

score 0 · Answer 3 · answered Feb 26 '12 at 19:36

0

I never heard of 'spamming mysql connections'. You probably use a huge amount of mysql-connection out of the limited pool of available connections.

In such a case, persistent MySQL connections might help.

But please read the manual regarding mysql_pconnect! This hint from the documentation isn't the only pitfall:

'Note, that these kind of links only work if you are using a module version of PHP. See the Persistent Database Connections section for more information.'

and

'Persistent connections were designed to have one-to-one mapping to regular connections. That means that you should always be able to replace persistent connections with non-persistent connections, and it won't change the way your script behaves. It may (and probably will) change the efficiency of the script, but not its behavior!'

Wrongly used, the 'spamming' problem gets even bigger...

answered Feb 26 '12 at 19:36

SteAp

11,853
10
53
88

In such a case, persistent MySQL connections will make things much, much worse. – Your Common Sense Feb 26 '12 at 19:39
Please explain why. I don't propose to create an unlimited number of persistent connections... – SteAp Feb 26 '12 at 19:42
Ever used persistent connections? – Your Common Sense Feb 26 '12 at 19:45
Because persistent connectons require the server to be configured properly in order to use them (and given the depth of detail the hosting company sent to Daniela, we can be pretty much sure it isn't). Otherwise it will still create connection per request, but now they won't be killed once the script finishes. – Mchl Feb 26 '12 at 19:46
@Col.Shrapnel Yes. Why ask back? Point out the problem. – SteAp Feb 26 '12 at 19:47
@SteAp: some information is here http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/ – Mchl Feb 26 '12 at 19:57
@Mchl Thank you! Nothing new there. In fact, some time ago, optimized an bad behaving application which overly used MySQL connections and resources. As often, the problem was located in the web-application. Horrible queries, no indexes, register global to be ON assumed - to just name some. I don't argue, that persistent connections are a catch all solution. And I pointed out, that their use requires special care. The TCP FIN problem should get smaller using persistent connections (or real a connection pool, which PHP doesn't have). – SteAp Feb 26 '12 at 20:09

PHP » Singleton MySQL connection

3 Answers3