CODEWITHSUNDEEP
phpmemorymysqliecho
JJGong
JJGong

Reputation: 129

Why does PHP allocate much more memory than needed?

I have a table with ~70k records and is ~12 MiB(MB) in size.

require_once('connection.php');
$sql = "SELECT * FROM BlahBlah";
$result = $con->query($sql);
$data = array();
while($row = $result->fetch_assoc()) {
  $data[] = $row;
}
echo json_encode($data);

When I do a while fetch_assoc() loop in PHP to store the result and echo back all the rows in the table in Json format to javascript, I get Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes).

I understand that more than 12 MiB of memory is definitely required, but allocating 10 times as much memory as needed throws me off. I have one other query before this and it only grabs a 151 KB table.

EDIT: I've read the blog post below to understand why PHP needs so much overhead.

One solution is to set the memory limit to unlimited, but that seems like a horrible approach as my database grows at ~10k records per day. Is there any other way to output all the rows of a table from MySQL in Json format to the client? I'm generating google map markers on the client side from the rows obtained

Upvotes: 2

Views: 465

Answers (1)

Arnial
Arnial

Reputation: 1441

I had same problem as you. I solve it by creating json string manually and sending it to user by parts as soon as possible (in batches of course).

require_once('connection.php');
$sql = "SELECT * FROM BlahBlah";
$result = $con->query($sql);


$json_str = '['; // we will send this to user.
$count = 0; // current number of fetched rows.
$max_count = 100; // max number of rows that we store in memmory bofore sending to client.

$quote = ''; 
// $quote will be used to concatenate json strings
// it's empty only on first iteration

while($row = $result->fetch_assoc()) {
  $json_str .= $quote . json_encode( $row );
  $quote = ',';
  $count++;
  if( $count >= $max_count ){
     echo $json_str;  //send all data that we have for now
     // if you don't want to echo it you can save it to file.

     $json_str = '';  //clear string (prevent memory growing) 
     $count = 0; 
  } 
}
echo $json_str .']';

UPDATE: Also there are other ways to reduce memory usage

  1. You can use SplFixedArray for $data to store all rows (in my practice it was 20-30% more efficient than php Array).
  2. You can json encode rows before adding them to $data. This is funny but json strings may be more memory efficient than Array.

But this methods are less memory efficient and slower.

Upvotes: 1

Related Questions