Laravel 4.2: slow queries when using external MySQL server

When MySQL is on a different server than the application, queries are very slow. It's not about expected latency (network bottlenecks, etc.); I never had this problem when I used Codeigniter. This doesn't seem like a DNS issue either, and I ran into this issue on every machine and server I checked.

In an attempt to figure out what's going on, I wrote a simple test doing the same queries, but using different approaches:

  • PDO instance is created manually using DSN
  • a PDO instance is returned DB::getPdo()

  • query builder

Screenshot of the test results: here.

This is the code:

Route::get('/test', function(){


    $num_queries = 10;


    // manually creating a PDO instance with DSN
    Debugbar::measure('$dbh = new PDO("mysql:host=$hostname;port=$port;dbname=$db", $username, $password)', function() use ($num_queries){

        $hostname = Config::get('database.connections.mysql.host');
        $port = Config::get('database.connections.mysql.port');
        $db = Config::get('database.connections.mysql.database');
        $username = Config::get('database.connections.mysql.username');
        $password = Config::get('database.connections.mysql.password');

        $dbh = new PDO("mysql:host=$hostname;port=$port;dbname=$db", $username, $password);

        for($i = 1; $i <= $num_queries; $i++)
        {
            $sth = $dbh->prepare("SELECT * FROM users WHERE id = ?");
            $sth->execute(array($i));
            $sth->fetch(PDO::FETCH_ASSOC);
        }
    });


    // using DB::getPdo()
    Debugbar::measure('$dbh = DB::getPdo();', function() use ($num_queries){

        $dbh = DB::getPdo();

        for($i = 1; $i <= $num_queries; $i++)
        {
            $sth = $dbh->prepare("SELECT * FROM users WHERE id = ?");
            $sth->execute(array($i));
            $sth->fetch(PDO::FETCH_ASSOC);
        }
    });


    // using the query builder
    Debugbar::measure('Query builder', function() use ($num_queries){

        for($i = 1; $i <= $num_queries; $i++)
        {
            DB::table('users')->where('id', $i)->get();
        }
    });

    return "<h1>$num_queries ".($num_queries == 1 ? 'query' : 'queries')."</h1>";
});

      

Note how bad it DB::getPdo()

scales with multiple requests. Has anyone else experienced this? What's going on here? I must point out that I have no such problem when the MySQL database is on the same server as the application.

+3


source to share


1 answer


A static call uses the same PDO instance and therefore the same actual database connection behind the scenes every time.

Others create a new connection each time with the appropriate associated overhead.



Slow queries will result in something else (maybe indexes in production - debug query using EXPLAIN ).

These debug tests lead you to the wrong path.

0


source







All Articles