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
– Daniela Răducanu, Commented Feb 26, 2012 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
– Michael Berkowski, Commented Feb 26, 2012 at 19:12
@Michael even if this function called several times, it will use only one connection per script — Your Common Sense
– Your Common Sense, Commented Feb 26, 2012 at 19:23

a sad dude · Accepted Answer · 2012-02-26 19:17:12Z

1

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.

answered Feb 26, 2012 at 19:17

a sad dude

2,79020 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:20:04Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Feb 26, 2012 at 19:16

msgmash.com

1,0356 silver badges10 bronze badges

Comments

SteAp · Accepted Answer · 2012-02-26 19:36:36Z

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, 2012 at 19:36

SteAp

12k12 gold badges56 silver badges96 bronze badges

7 Comments

Your Common Sense Over a year ago

In such a case, persistent MySQL connections will make things much, much worse.

SteAp Over a year ago

Please explain why. I don't propose to create an unlimited number of persistent connections...

Your Common Sense Over a year ago

Ever used persistent connections?

Mchl Over a year ago

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.

SteAp Over a year ago

@Col.Shrapnel Yes. Why ask back? Point out the problem.

|

Collectives™ on Stack Overflow

PHP » Singleton MySQL connection

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related