Laravel Lazy Read/Write Database Connection

Zara 4 is a globally distributed image compression application with image processing servers located around the world. As a result the connection between our servers is often over vast distances with high latency connections between nodes.

High latency network connections introduced a number of performance challenges for our service. The problem in a nutshell is how to maintain a consistent global database whilst being able to process images quickly.

To speed up our servers we decided to maintain a local database replica for each region our servers were operating in. The idea is, although writing data to our central database will still be slow, by maintaining database read replicas we can significantly speed up reading data from the database.

The Problem

Whilst implementing database replication into one of our Laravel applications, we encountered an odd feature with the Laravel MySQL database driver. Although a local database replica with very low latency had been established, there was still a significant delay (over 500ms) when performing a simple read from the database.

After debugging and looking more carefully at how the Laravel MySQL driver actually works, we found the problem is caused by the Laravel database driver itself. Although Laravel implements lazy database connecting, it does not distinguish between the individually specified ‘read’ and ‘write’ connections. So when the first ‘read’ query (SELECT) is run, Laravel connects to both the local ‘read’ replica database and the remote (high latency) ‘write’ database. The result is a delay caused by connecting to the ‘write’ database even though the connection is not required since only a read query is ever executed.

The Solution

To solve this issue we have implemented a slightly modified MySQL database driver for Laravel (tested with Laravel 5.1) called lazy-mysql which only connects to the individually specified ‘read’ and ‘write’ database connections as required. In other words the ‘read’ database connection is not established until the first read query (SELECT) is run, and the ‘write’ database connection is not established until the first write query (INSERT, UPDATE, DELETE) is run.

As a result the high latency ‘write’ database connection is not established when only performing ‘read’ queries, which delivers significant performance improvements.

See Lazy MySQL project on GitHub

Results

Using the standard Laravel MySQL driver with a local MySQL read replica database and a remote MySQL write master database, took in excess of 500ms to connect and read a single record.

By enabling persistent connections with the PDO::ATTR_PERSISTENT => true option for the standard Laravel MySQL driver; the time to connect and read a single record was reduced to around 100ms to 150ms

By switching the database driver to lazy-mysql, the time to connect and read a single record was reduced to around 7ms to 15ms

The lazy-mysql database driver cannot overcome the delay caused when writing data to the remote master write database; however for requests that only ever read from the local read replica database, it can deliver significant performance improvements.