How Laravel maintains persistent database connection

Question

I was developing a Laravel application in LAMP setup at my local machine( my laptop).

For testing purpose, I tried to use the mysql service of Aws RDS instead my local mysql server. Found that, an API call which have only one db call with no join (query: show tables) - takes on average 12s. This is ridiculous. When I am using local mysql server, it is around than 600ms. Until now, PDO::ATTR_PERSISTANT was not enabled.

Logging in Illuminate\Database\Connectors\Connector.php [ inside createConnection() method ], I found that, this method is called for each request. This is for both mysql server.

Then, I set PDO::ATTR_PERSISTANT to true. But response time's are similar.

After looking a more closer, found this in the same file:

/**
 * Create a new PDO connection instance.
 *
 * @param  string  $dsn
 * @param  string  $username
 * @param  string  $password
 * @param  array  $options
 * @return \PDO
 */
protected function createPdoConnection($dsn, $username, $password, $options)
{
    if (class_exists(PDOConnection::class) && ! $this->isPersistentConnection($options)) {
        return new PDOConnection($dsn, $username, $password, $options);
    }

    return new PDO($dsn, $username, $password, $options);
}

And PDOConnection - which extends PDO, that is used when persistent is false - constructor is:

public function __construct($dsn, $user = null, $password = null, array $options = null)
{
    try {
        parent::__construct($dsn, $user, $password, $options);
        $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, ['Doctrine\DBAL\Driver\PDOStatement', []]);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (\PDOException $exception) {
        throw new PDOException($exception);
    }
}

It seems, setting PDO::ATTR_PERSISTANT to true has no effect. This made me confused.

1. How actually persistent database connection plays in Laravel?
2. What was the reason behind long response time (with mysql service of Aws RDS) ?

Answer 1

MySQL is a database that is known for having a lightweight connection overhead. For this reason, concerns in regards to connection reuse rarely surface, but in any case, connections in PHP are tied to processes. You really will only see the benefit of connection reuse under load, where there are enough php processes running that a sustained PHP process gets around to re-using a mysql client connection.

In other words, your mysql client connections can/will/might be closed depending on your web server configuration, even if you are using persistent mysql connections, or a new connection will need to happen if the apache process for a request does not already have a persistent connection available.

Even if you have a persistent connection available, this does NOT mean that things will be faster in your scenario, because the assumption you are making is that connection time to the RDS across the public internet is slow, when in reality, the entire mysql client connection from your localhost application across the public internet is slow. Queries will be slow, result set fetching will be slow, and overall it is going to be a poor experience.

A connection taking 12 seconds isn't something you would see if you were hosting your entire site on AWS and using RDS for your data store. It will be much closer to the type of performance you get on your localhost with a local database.

Furthermore, the entire predicate of the idea isn't good.

1. RDS is a premium priced product. You shouldn't be using that for Development at all. If you are using free tier, the database performance for anything but a tiny db is going to be pretty slow.
2. MySQL intrinsically has no network layer security. Your AWS deployment should be using VPC where the RDS is in the VPC, your PHP application servers are in the VPC.
3. There is nothing special in your app to using RDS. It looks to your application like any other mysql database with a non-localhost -h parameter.

You are trying to tune something that literally nobody would use in the real world, thus you are trying to solve a problem of your own making that is of no long term benefit to your project.

With that said, if you want to understand better why you are experiencing what you are, and how both PHP and associated resource handles work, a database that DOES have a heavyweight and time consuming connection process is Oracle. People using Oracle with PHP legitimately have to be concerned with connection reuse, because Oracle expects that a connection will be made, and then many "sessions" will occur. Although this Oracle Cookbook article is somewhat old now, it does a good job illustrating the association between PHP processes (at least with Apache and Mod_php) and database connections.

Last but not least, this topic is complicated by the fact that there are a number of different ways you can run PHP. Apache has several different configurations, which is different from IIS, which is different from Nginx/php-fpm which has become popular in recent years. This SO question/answer already addressed the specific PDO param you are trying to use, which ultimately just passes it through to the MySQL client library, along with some important caveats and concerns that explain why MySQL connections are rarely worth the potential for trouble they might cause.