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: .
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.
source to share
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.
source to share