PDO Connect intermittently slow

Question

I have the following script to connect to the database:

$db = new PDO("mysql:dbname=dbname;host=127.0.0.1;charset=utf8mb4", 'root', '',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'"));
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I am using xampp and intermittently it will take 3+ seconds to load the above (When it isn't doing the above it loads instantly).

I have verified it is the above by using XDebug, please see screenshots below:

In google developer tools the TTFB (Time to First Byte) also matches the time it takes to connect to the database.

Things I have tried:

Setting the ServerName property in http.conf to ServerName 127.0.0.1:80
Using host 127.0.0.1 instead of localhost in the connect script
In http.conf setting the HostnameLookups to Off
In http.conf change the Listen port to 8080 (Listen 127.0.0.1:8080) this one just caused it to say localhost refused to connect though
Disabling IPv6 in regedit
Changed the host file to have the records 127.0.0.1 localhost and 127.0.0.1 127.0.0.1
Disabling XDebug doesn't make a difference
I have BitDefender Anti Virus installed and have tried disabling that
Adding skip-name-resolve to my.ini
Adding bind-address="127.0.0.1" to my.ini
Flushing the DNS Cache

And probably other things I am forgetting.

I am using Google Chrome but have the same issues with all browsers.

How can I make the connection to the database consistently fast instead of hanging for 3+ seconds?

is it just me that uses new PDO($dsn, $usr, $pass);? so many options? xD — treyBake

Eden Reich Eden Reich · Accepted Answer · 2018-04-13T20:23:26

This is rather just a suggestion, not a solution. The speed of connecting to your database may vary and depend on the hardware you are using. I have noticed you running it locally.

I am not sure how fast you want it to be, yet if you want to reduce the connection speed time, you should consider using the singleton pattern for subsequent connections.

Generally it should look like the following snippet:

Class Database
{
    protected static $connection = null;

    public static function connect ()
    {
         if (! is_null (self::$connection)) {
               return self::$connection;
         }

         self::$connection = new PDO("mysql:dbname=dbname;host=127.0.0.1;charset=utf8mb4", 'root', '' ", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'"));
         $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

         return self::$connection;
    }
}

Database::connect ()->query ('some random query'); // this will still take 3 sec
Database::connect ()->query ('some random query'); // this will run faster

P.S this won't avoid the speed for the first connection, yet sense the connection is left open for subsequent connections it will be fast enough. Please also read the pros and cons of singleton pattern before adopting it.

PDO Connect intermittently slow

1 Answers