Slow parent::__construct in extended mysqli class

Question

I'm extending mysqli in a database class. I've noticed that calling the parent constructor takes nearly 2 seconds. It is possible, I suppose, that it is my environment since I am developing on my desktop prior to deployment. But that does not seem very likely to me.

Environment:

• OS - Windows 7 Pro
• WAMP server
• Apache/2.2.17 (Win32)
• PHP 5.3.4
• MySql ver 5.1.53
• NetBeans IDE 6.9.1

The code in question:

class DGMysqliLink extends MySQLi
{
    public function __construct($aDSN)
    {
        // Construct the DSN
        $aDSN['dbhost'] = (empty($aDSN['dbhost']))?ini_get("mysqli.default_host"):$aDSN['dbhost'];
        $aDSN['dbuser'] = (empty($aDSN['dbuser']))?ini_get("mysqli.default_user"):$aDSN['dbuser'];
        $aDSN['dbpass'] = (empty($aDSN['dbpass']))?ini_get("mysqli.default_pw"):$aDSN['dbpass'];
        $aDSN['dbname'] = (empty($aDSN['dbname']))?'':$aDSN['dbname'];
        $aDSN['dbport'] = (empty($aDSN['dbport']))?ini_get("mysqli.default_port"):$aDSN['dbport'];
        $aDSN['dbsock']= (empty($aDSN['dbsock']))?ini_get("mysqli.default_socket"):$aDSN['dbsock'];

        // Instantiate the object by invoking the parent's constructor.
        // This takes nearly 2 seconds
        parent::__construct($aDSN['dbhost'],$aDSN['dbuser'],$aDSN['dbpass'],
                            $aDSN['dbname'],$aDSN['dbport'],$aDSN['dbsock']);

        // If there are any errors, deal with them now
        if($this->connect_error){/* Do some stuff */}
    }
}

Why does calling this constructor take so long and how can I fix it?

Answer 1

There is no way to fix this, it's simply the overhead of TCP/IP and several layers of client and server code talking to each other. However, you can use persistent connections. They are available since PHP 5.3. To activate them, just prefix the hostname with "p:", as in "p:localhost" in your connection statement. Also enable it in PHP.INI: mysqli.allow_persistent = On

Other than that, it's all automatic.

Just as an example, my pages had an average loading time of 1.3 seconds. When I had a main page loaded, making 4 or 5 AJAX calls to fill form fields, this time quickly jumped to 7 or 8 seconds. Each ajax call meant a new PHP script, which in turn made a new connection to the database. Well, I think the problem here is clear.

Using persistent connections allows PHP to reuse already opened connections, saving all the time spent on opening and closing a new connection for each script. In my case, approximately 1 second has been cut from each connection, greatly improving performance.

There are some details, however, regarding bad effects of "dirty" connections being reused. They are explained in the manual, but the short message here is: be tidy with your database connections and commands, and make them persistent. This is the way to go if performance is a must.

Answer 2

are you using tcp connections? There'll likely be DNS delays to resolve both the hostname you're connecting to, and possibly reverse-DNS delays on the mysql end of things.

e.g. if the mysql user account you're using is user@example.com, then MySQL will have to do a reverse DNS lookup for the IP you're connecting from to see if example.com matches that IP address.