PHP Reducing memory usage when using fputcsv to php://output

Question

We've run into a bit of a weird issue. It goes like this. We have large reams of data that we need to output to the client. These data files cannot be pre-built, they must be served from live data.

My preferred solution has been to write into the CSV line by line from a fetch like this:

 while($datum = $data->fetch(PDO::FETCH_ASSOC)) {
            $size += fputcsv($outstream, $datum, chr(9), chr(0));
 }

This got around a lot of ridiculous memory usage (reading 100000 records into memory at once is bad mojo) but we still have lingering issues for large tables that are only going to get worse as the data increases in size. And please note, there is no data partitioning; they don't download in year segments, but they download all of their data and then segment it themselves. This is per the requirements; I'm not in a position to change this as much as it would remove the problem entirely.

In either case, on the largest table it runs out of memory. One solution is to increase the memory available, which solves one problem but suggests the creation of server load problems later on or even now if more than one client is downloading.

In this case, $outstream is:

$outstream = fopen("php://output",'w');

Which seems pretty obviously not really a physical disk location. I don't know much about php://output in terms of where the data resides before it is sent to the client, but it seems obvious that there are memory issues with simply writing a manifold database table to csv via this method.

To be exact, the staging box allows about 128mb for PHP, and this call in particular was short about 40mb (it tried to allocate 40mb more.) This seems a bit odd for behavior, as you would expect it to ask for memory in smaller parts.

Anyone know what can be done to get a handle on this?

Answer 1

So it looks like the memory consumption is caused by Zend Framework's output buffering. The best solution that I came up with was this.

doing ob_end_clean() right before we start to stream the file to the client. This particular instance of ZF is not going to produce any normal output or do anything more after this point, so complications don't arise. What odd thing does happen (perhaps from the standpoint of the user) is that they really get the file streamed to them.

Here's the code:

ob_end_clean();
while($datum = $data->fetch(PDO::FETCH_ASSOC)) {
            $size += fputcsv($outstream, $datum, chr(9), chr(0));
}

Memory usage (according to the function memory_get_peak_usage(true) suggested in a ZF forum post somewhere) went from 90 megabytes down to 9 megabytes, which is what it was using here on my development box prior to any file reading.

Thanks for the help, guys!