All requests requiring connection to mysql are very very slow (using Phalcon)

dependency-injection, mysql, phalcon, php

Solution

I've discovered the problem, which to me wasn't immediately apparent, so hopefully others will find this useful as well.

Every time a new query was started, the application was getting a new instance of the database adapter. The request which produced the webgrind output above had a total of 20 queries.

While re-reading Phalcon's documentation section on dependency injection I saw that services can optionally be added to the service container as a "shared" service, which effectively forces the object to act as a singleton, meaning that once one instance of the class is created, the application will simply pass that instance to any request instead of creating a new instance.

There are several methods to force a service to be added as a shared service, details of which can be found here in Phalcon's Documentation:

http://docs.phalconphp.com/en/latest/reference/di.html#shared-services

Changing the code posted above to be added as a shared service looks like this:

$di->setShared('db', function() use ($config) {
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => $config->database->host,
        "username" => $config->database->username,
        "password" => $config->database->password,
        "dbname" => $config->database->name
    ));
});

Here's what the webgrind output looks like for the same query referenced above, but after setting the database service to be added as a shared service (cost in milliseconds):

Notice that the invocation count is now 1 instead of 20, and the invocation cost dropped from 20 seconds down to 1 second!

I hope someone else finds this useful!

Problem

I've been working on converting an application of mine from CodeIgniter to Phalcon. I've noticed that [query heavy] requests that only took a maximum of 3 or 4 seconds using CI are taking up to 30 seconds to complete using Phalcon! I've spent days trying to find a solution. I've tried using all the different means of access offered by the framework including submitting raw query strings directly to Phalcon's MySql PDO adapter. I'm adding my database connection to the service container exactly like it is shown in Phalcon's INVO tutorial: ``` $di->set('db', function() use ($config) { return new \Phalcon\Db\Adapter\Pdo\Mysql(array( "host" => $config->database->host, "username" => $config->database->username, "password" => $config->database->password, "dbname" => $config->database->name )); }); ``` Using webgrind output I've been able to narrow the bottleneck down to the constructor in Phalcon's PDO adapter class (cost is in milliseconds): I've already profiled and manually tested the relevant SQL to make sure the bottleneck isn't in the database (or my poorly constructed SQL!)

Original source