How to lazy connection write Database as needed.

Bump - I'm having this exact problem too, I only roughly tracked down what was causing it yesterday.

I have a master database in London and a read replica database in Sydney. The web server in Sydney is very slow (despite the read replica database) even when performing read only operations because it still establishes the connection with the database in London (even though the write connection is not required). The delay is cause by the high latency connection between London and Sydney.

So ideally I'd like a way to lazily establish the write connection (so it's not established unless it's actually used)

I have found that maintaining a persistent write connection by adding: PDO::ATTR_PERSISTENT => true, to the write database options improves things significantly (assuming you're using MySQL).

Also (from reading the source) not specifying a 'charset' 'timezone' or 'strict' setting for the database configuration also helps - (HOWEVER messing with the 'strict' option in particular has caused me problems). I've got the delay down to around 100ms so far.

I'm currently using Laravel 5.1 - I don't know if this has already been addressed in a newer version?

If you are using MySQL and the MySQLi driver you can use connection pooling. This will keep a pool of open connections. This can remove the time used to open a network connection to your database. But you are still faced with the local speed of the network where the database resides. Databases do not have lazy writes/reads. You make a request and it is carried out. Also replication is not usually asynchronous (Vendor/Third party options may be available) in order to achieve consistency between replicas. None of this is a concern of Laravel.

How do you implement connection pooling with laravel? I can't seem to find any documentation. I can maintain persistent connections which brings the database connection set up time down from 500ms to around 100ms.

However there is clearly something still going on, because simply replacing the write connection host with the read replica host brings the set up time down to around 15ms. This works perfectly for read only operations, but obviously breaks if you try to write data because the database replica is read only.

I'm not worried about write operations taking longer, but the delay caused for read only operations is unnecessary. A request that only reads data from the database is delayed by establishing the connection with the write database.

I'm not worried about replica lag either because I've dealt with that in specific areas by caching locally.

Unless I'm misunderstanding something, I see no reason for the connection to the write database to be established until it is actually required. So set up the database read connection as usual, but do not connect to the write database until a write query such as INSERT or UPDATE is executed.

So you can see where the read connection is created in \Illuminate\Database\Connectors\ConnectionFactory::make

$config = $this->parseConfig($config, $name);

if (isset($config['read'])) {
    return $this->createReadWriteConnection($config);
}

return $this->createSingleConnection($config);

\Illuminate\Database\Connectors\ConnectionFactory::createReadWriteConnection

$connection = $this->createSingleConnection($this->getWriteConfig($config));

return $connection->setReadPdo($this->createReadPdo($config));

And the connection is established in \Illuminate\Database\Connectors\ConnectionFactory::createSingleConnection

$pdo = $this->createConnector($config)->connect($config);

return $this->createConnection($config['driver'], $pdo, $config['database'], $config['prefix'], $config);

I'm still playing with it at the moment - but I was looking to see if there was a way that the individual read and write connectors didn't actually connect until there was a query that required them?

I'm writing a modified database driver based on the MySQL driver - but obviously if there is a simple fix or something I've got wrong, I'd appreciate somebody letting me know!

An update for anyone who has the same problem and comes across this page.

I solved this issue by writing a modified mysql database driver called lazy-mysql

Lazy MySQL works differently to the standard Laravel database because it only establishes the connection to the read / write database as actually required. The first read query executed establishes the read database connection. The first write query executed establishes the write database connection.

The lazy-mysql driver is currently tested and in operation in a distributed image compression project I'm involved in called Zara 4. So far it's working exactly as expected.

View our blog post Laravel Lazy Read/Write Database Connection

View the project on github

Any solutions for version 8?