0

Cross post from this question

I have a table that has 2.3 MB of data (40,000 rows) that I tried to process using MySQLi/PHP. (I calculated the byte size of the table in the linked question)

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');

The following code gives:

enter image description here

On the following line:

while($row = $result->fetch_array()){

Am I doing a wrong approach at this or am taking too many rows? Or is this an issue with PHP?

Jebathon
  • 4,310
  • 14
  • 57
  • 108
  • how is your table structured? – funilrys Sep 18 '17 at 19:46
  • @funilrys 7 columns INT – Jebathon Sep 18 '17 at 19:47
  • https://davidwalsh.name/increase-php-memory-limit-ini_set – Jared Smith Sep 18 '17 at 19:47
  • 3
    The error seems rather clear to me. Give PHP more memory. Although why you would want to push 40k rows into a PHP script is somewhat beyond me. – Boris the Spider Sep 18 '17 at 19:47
  • @BoristheSpider I don't run regular queries that take so many rows, I'm building a system that allows the user to just request that much information and consolidate it – Jebathon Sep 18 '17 at 19:49
  • 1
    Your *script* is using more memory. That is partially because a PHP array may take more space than a record in the database, but mainly because you then serialize the whole lot in a big string. This string is way larger than your table, because each field of each record will contain its name in it, so it's a very inefficient way to store data. The size displayed is 134 million bytes roughly 128MB, which may very well be the maximum size of running PHP script by your server configuration. This size is limited to make sure there is memory for multiple instances (in case of concurrent users). – GolezTrol Sep 18 '17 at 19:49
  • 3
    In short, PHP can store huge amounts of data in MySQL and read an process or even output it to your browser. However, you shouldn't try to keep it all in memory at the same time. – GolezTrol Sep 18 '17 at 19:50
  • Then why not stream it directly back to the user? – Boris the Spider Sep 18 '17 at 19:50
  • @BoristheSpider Its a reporting engine, I send an array of data to an engine that outputs the data in a stream to the user – Jebathon Sep 18 '17 at 19:51
  • 1
    Is the engine you're sending to under your control? Will it take smaller chunks, a different format (JSON/XML might be handy here), etc.? – ceejayoz Sep 18 '17 at 19:52
  • @ceejayoz can you elaborate? Yes I'm building everything. The report engine gets inputted an array of rows which it loops/parses and streams data back to the user – Jebathon Sep 18 '17 at 19:53
  • If you want to fix that one possible solution is to change the value of `memory_limit = 128M` into your `php.ini` – funilrys Sep 18 '17 at 19:54
  • 1
    That seems like an awful idea. Sorry, but it does. Anything that requires you to build the entirety of a database in memory to send it somewhere else to the turn the whole lot into one giant string is extraordinary bad design. – Boris the Spider Sep 18 '17 at 19:56
  • I'm with @BoristheSpider here - this sounds like [an XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You could raise the memory limit, chunk the data (send 10k rows at a time or something like that), store it in a more memory-efficient manner (writing to a temporary CSV with `fputcsv`, perhaps), etc., but I suspect there's fundamentally a better way to pass data to your reporting engine. – ceejayoz Sep 18 '17 at 19:57
  • @BoristheSpider 1. Not an entirety, I didn't mention that I query specific rows out of millions. Lets say I collect data every 5 seconds in some IoT infrastructure and want to see a weekly report for it. I know I can do it better but haven't thought of better solutions yet. – Jebathon Sep 18 '17 at 19:58
  • @BDillan You might consider a specific time-series database for that, or doing the reporting directly in the database, which is usually well suited for such things (with the right indexes, of course). – ceejayoz Sep 18 '17 at 19:59

0 Answers0