PHP - Confusing Performance Issue getting data from MySQL

Question

Using PHP/MySQLi I wanted to extract some data to an array. The data is a few megabytes in size:

The code to get the table dump of this data follows:

error_reporting(E_ALL);

 $q =  "SELECT * FROM mytable";
 if (!$result = $mysqli->query($q)) {
      echo "Error: Our query failed to execute and here is why: \n";
      echo "Query: " . $query . "\n";
      echo "Errno: " . $mysqli->errno . "\n";
      echo "Error: " . $mysqli->error . "\n";
      return $ret;
    }
if ($result->num_rows === 0) {
   $ret = 0;
   return $ret;
  }

$ret = array();
while($row = $result->fetch_array()){
  array_push($ret, $row);
} 

echo  mb_strlen(serialize((array)$ret), '8bit');

When executing the following code I got:

But it said tried to allocate 28672 bytes which is nowhere near the limit. Why is this?

Your query use `SELECT *` but when you measure size use `{row_1,row_2,row_n}` Are more colmuns there? Are all those columns strings? — Juan Carlos Oropeza, Sep 18 '17 at 19:24

score 0 · Answer 1 · answered Sep 18 '17 at 19:15

0

Think of it this way.

You've got $100. You go to the candy store, and decide to buy 200 $1 candy bars, one-by-one (your while loop).

The first 100 purchases are going to go through, but the next time you try buying a $1 candy bar, you're going to get told "you tried to spend a dollar, but you've already spent $100".

PHP is reporting the amount of memory your last instruction attempted to use. In this case, it's probably the array_push($ret, $row); call.

answered Sep 18 '17 at 19:15

ceejayoz

176,543
40
303
368

Ok but the error is saying you can't spend more than $100. (tried to spend $18) – Jebathon Sep 18 '17 at 19:20
@BDillan It's telling you "You just tried to use X additional bytes of memory, but that amount puts you over your limit." Don't overthink it. – ceejayoz Sep 18 '17 at 19:21
1

@ceejayoz But why a dataset with 2 Mb cause you to reach the limit? – Juan Carlos Oropeza Sep 18 '17 at 19:24
@JuanCarlosOropeza The answer to *that* needs a bit of a dive into PHP's internals, but the TL;DR is "arrays take up a lot more space than just their raw data". https://nikic.github.io/2011/12/12/How-big-are-PHP-arrays-really-Hint-BIG.html (Plus, your query etc. are likely taking up memory space, as well. You can experiment with logging [`memory_get_usage`](http://php.net/manual/en/function.memory-get-usage.php) values to figure out where it's all going, if you like.) – ceejayoz Sep 18 '17 at 19:30
So what you are saying is that PHP cannot handle more than (lets say 2mb) of sql data? That's depressing – Jebathon Sep 18 '17 at 19:37
No, that's not what he's saying. Increase the size of the memory allocated to PHP when you hit a limit like this (if you can). It looks like your limit is currently set to 128M so try bumping it to 512M and see what happens. I would also limit the amount of data your query is retrieving. If you don't need all of the columns don't use the `SELECT *`. Depending on the amount of data each row contains the amount of data required could be very large. – Dave Sep 18 '17 at 19:43
@BDillan The article I linked has one potential approach - using a `SplFixedArray`. In general, there are better ways than iterating an entire large dataset into an array, though. What's the goal of loading it all into an array here? – ceejayoz Sep 18 '17 at 19:45

PHP - Confusing Performance Issue getting data from MySQL

1 Answers1

Linked