Efficiently Storing Large Files in MySQL Database

Handling Large Files in MySQL with Laravel: A Practical Approach

Recently, I encountered a challenging scenario while working on a Laravel project that required storing a significantly large file (about 500MB) directly into a MySQL database. The file in question was a zip containing multiple PDF documents. Ideally, storing such large files on a filesystem or a cloud storage solution like Amazon S3 would be preferable, but there were specific requirements that necessitated the use of a database for storage.

The Challenge

The primary challenge arose when I attempted to upload the large file to the database using Laravel’s typical approach, which involves reading the entire file into memory and then storing it. This method quickly exhausted the allocated PHP memory with the following error:

Allowed memory size of 134217728 bytes exhausted (tried to allocate 37456160 bytes).

This clearly indicated a memory limit issue, and given that I couldn’t increase PHP’s allowed memory or the MySQL max_allowed_packet size (limited to 15MB), I had to find a way around these constraints.

Strategic Approach: Streaming and Chunking

1. Streaming Data from PHP to MySQL

To bypass the memory limit and handle the file efficiently, I decided to stream the data directly to MySQL without loading the entire file into memory. PHP supports streaming file data using handles, which can then be used to incrementally read and write the file.

Here’s a rough outline of how I approached this:

1. Create a table with a BLOB column to store the file. Given that BLOBs can handle up to 65535 bytes, for larger files like in my case, using a MEDIUMBLOB or LONGBLOB would be more appropriate as they allow more data to be stored.

CREATE TABLE files (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255),
       data LONGBLOB
   );

1. Open the file using a stream:

$fileHandle = fopen($file, 'rb');

1. Write file content in chunks to the database:

Using a prepared statement and the send_long_data method allows sending the data in packets rather than as a single query, which helps in staying within the max_allowed_packet size defined in MySQL’s configuration.

$blob = null;
   $stmt = $conn->prepare("INSERT INTO files (name, data) VALUES (?,?)");
   $stmt->bind_param('sb', $filename, $blob);
  
   while (!feof($fileHandle)) {
       $chunk = fread($fileHandle, 4096);  // reading in chunks of 4KB
       $stmt->send_long_data(1, $chunk);
   }
  
   $stmt->execute();
   fclose($fileHandle);

2. Retrieving and Streaming Data to the User

For downloading or displaying the data to the user, a similar approach of reading in chunks can be implemented. Instead of loading the entire content into memory, we read from the database in manageable segments and directly flush them to the output buffer. This method is efficient and avoids PHP memory limits.

$stmt = $conn->prepare("SELECT data FROM files WHERE id = ?");
$stmt->bind_param('i', $fileId);
$stmt->execute();
$stmt->bind_result($stream);
$stmt->fetch();
while ($chunk = fread($stream, 4096)) {
    echo $chunk;
}
fclose($stream);

Conclusion

Employing this streaming and chunking method for handling large file uploads and downloads within a Laravel application helped resolve the memory exhaustion issue effectively. It ensured that the application could handle large files seamlessly without crashing, all while adhering to the tight constraints of the server configuration.

This practical approach can serve as a guide for anyone facing similar limits in their application infrastructure, essentially allowing the efficient processing of large files within the set boundaries.