应用程序在提取数据时挂起

Question

I have noticed several times. we have a front end application created in php. it can extract data from MySQL database into excel file. when I extract data if data is less (about 1000 rows) it creates an excel file normally size of 1-2 MB. but application hangs or gives connection time out whenever I extract more data (about 100000 rows). do I have to change some parameters in php.ini file? I think this might be an error of memory allocation. but not sure which parameter to change.

Answer 1

Look into PHP's output buffer. You can send data as it becomes available.

Look at this for reference: http://php.net/manual/en/function.ob-start.php and this as an example http://www.andrew-kirkpatrick.com/2013/08/output-csv-straight-to-browser-using-php/

Edit: Just a thought, you can monitor the status of your database (in case it's the issue here). In your MySQL client you can execute this command show processlist; repeatedly to see what state the DB is in. If your script times out after the query has successfully completed, you can rule out a long running query.

Also if you're concerned about memory, you shouldn't put all your data in one variable. Really what you should be doing is writing out as you get information in. Seeing your code would help, but basically something along the lines of:

while ($row = mysql_fetch_assoc($result)) {
    echo "<td>{$result['firstname']}</td>";
}

is much better than:

$rows = array();
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}