EllisLab text mark
Advanced Search
2 of 2
Prev
1
2
   
Active Record large result memory exhausted
 
Juan Ignacio Borda
Posted: 11 May 2012 11:00 AM   [ Ignore ]   [ # 16 ]   [ Rating: 0 ]
Avatar
Joined: 2010-05-20
24 posts

Here a controller to raise the error

<?php
//----file name: /controllers/testdb.php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Testdb extends CI_Controller {

    public function index() {
        echo "<h1>DB Memmore Exhaustion TEST (128Mb)</h1>";
        echo "<ol>";
        echo "<li><a href='testdb/create_db'>Create a table with 4k records</a></li>";
        echo "<li><a href='testdb/run_test'>Run test</a></li>";
        echo "</ol>";
    }
    public function Run_test() {
        $query=$this->db->get('ci_mem_test');
        
        foreach($query->result() as $row){//<---- This line will exhaust 128Mb
            
        }
    }
    public function Create_db() {
        //---load dbforge
        set_time_limit(3600);
        $this->load->dbforge();

        $this->dbforge->drop_table('ci_mem_test');
        $this->dbforge->add_field('id');
        $this->dbforge->create_table('ci_mem_test');
        $object=new stdClass();
        $object->id=0;
        for($i=1;$i<=400000;$i++){
            $this->db->insert('ci_mem_test',$object);
        }
        //$this->output->enable_profiler(TRUE);
    }

}

/* End of file testdb.php */
/* Location: ./application/controllers/testdb.php */ 
 
CroNiX
Posted: 11 May 2012 12:16 PM   [ Ignore ]   [ # 17 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
3796 posts

It would be better to create a detailed issue on github if you really want this looked at.  If you come up with a solution (which would more likely be integrated than just creating an issue), issue a pull request.

 Signature 
 
Mat-Moo
Posted: 11 May 2012 02:48 PM   [ Ignore ]   [ # 18 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-20
397 posts

OK had a quick poke around and tried your code, CI seems to cache the whole result to memory then serve from memory it looks like, e.g. (my sql driver)

function result_object()
 {
  if (count($this->result_object) > 0)
  {
   return $this->result_object;
  }

  // In the event that query caching is on the result_id variable
  // will return FALSE since there isn't a valid SQL resource so
  // we'll simply return an empty array.
  if ($this->result_id === FALSE OR $this->num_rows() == 0)
  {
   return array();
  }

  $this->_data_seek(0);
  while ($row = $this->_fetch_object())
  {
   $this->result_object[] = $row;
  }

  return $this->result_object;
 } 

I don’t see anything obvious to avoid this, so while pagination is not what you want, it seems processing records 10000 at a time would probably solve your issue… Or process the query result with standard mysql commands.

 Signature 

Mat-Moo
Image moo - the easy to use image library!
MatMoo.com!
E-Mail Remind - Free reminders by email
Printfetti - Your photos on confetti!
 
CroNiX
Posted: 11 May 2012 03:14 PM   [ Ignore ]   [ # 19 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
3796 posts

You don’t really need ‘pagination’, just use a limit with an offset.  It’s not very efficient to retrieve millions of rows even if you have enough ram.  Best to do it in batches of 5000 or so freeing the result in between and using the offset/limit.  Surely you aren’t displaying that many records, just processing of some sort.  This allows you plenty of free resources left over for other operations, like visitors on the website and whatnot.

 Signature 
 
Juan Ignacio Borda
Posted: 11 May 2012 09:36 PM   [ Ignore ]   [ # 20 ]   [ Rating: 0 ]
Avatar
Joined: 2010-05-20
24 posts

@CroNiX: did you ever used another abstraction layer besides the one included on CI? ADODB for example can do this same thing with less than 8mb.
ADODB or PDO will not bring your results into ram! (nor should CI)

So as pointed before, I don’t having problems getting the job done, my problem is that I’m trying to do it with CI and I wanted fixed, for the all of us to use it the way we want (you can paginate or get small results if you want and I will be able to process my 1M monster table)

If you wan’t i can provide a place on a public server of my own to run both tests, one with CI and one with PDO or ADODB

by the way this issue can be located at:

https://github.com/EllisLab/CodeIgniter/issues/1351#issuecomment-5661625
 
doughoman
Posted: 22 October 2012 04:38 PM   [ Ignore ]   [ # 21 ]   [ Rating: 0 ]
Joined: 2012-10-22
1 posts

In case anyone else comes across this problem, my solution has been to call the php mysql function directly when iterating over the result set. So we’ll use CI to run the query and get the result driver object that contains the result id, then call the php mysql function directly passing the result id.  I think that should allow us to process the result set using the record pointer without using CI which reads all rows into memory.


$rs = $this->db->query($sql);

while($row = mysql_fetch_array($rs->result_id)) {
....
}

 
 
Juan Ignacio Borda
Posted: 24 October 2012 07:35 AM   [ Ignore ]   [ # 22 ]   [ Rating: 0 ]
Avatar
Joined: 2010-05-20
24 posts

Great practical solution doughoman, but breaks the Active Record philosophy.

doughoman - 22 October 2012 04:38 PM

In case anyone else comes across this problem, my solution has been to call the php mysql function directly when iterating over the result set. So we’ll use CI to run the query and get the result driver object that contains the result id, then call the php mysql function directly passing the result id.  I think that should allow us to process the result set using the record pointer without using CI which reads all rows into memory.


$rs = $this->db->query($sql);

while($row = mysql_fetch_array($rs->result_id)) {
....
}
 
Narf
Posted: 24 October 2012 10:13 AM   [ Ignore ]   [ # 23 ]   [ Rating: 0 ]
Avatar
Joined: 2012-01-09
117 posts

CodeIgniter indeed keeps all queries in its memory, mostly for debugging purposes and profiling. If you want to change that behaviour do one of the following:

// Solution 1
$this->db->save_queries = FALSE;

// Solution 2 (database configuration)
$db['default']['save_queries'] = FALSE; 

... furthermore, CodeIgniter 3 will have a new method to aid in bypassing the result caches as well:

https://github.com/EllisLab/CodeIgniter/commit/3020b24545381a72add33d67a488a7e39674ec7e
 
2 of 2
Prev
1
2