...at least I think it’s a memory leak.

I have a controller action that inserts 200,000 records into a database (populating a test database), but it fails after 90,000 or so records with the following message:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 101 bytes) in /var/www/html/sandbox/codeigniter/system/database/DB_driver.php on line 540

This is in a fresh install of CI 1.5.2 with the only modification being the following controller script (and some config/database.php edits).
Please help, this is driving me nuts! 

Hi there,

  I have a model that needs to do about 500,000 or so inserts (populating a test database), but the PHP application runs out of memory (?!)  It gives the following error after about 90,000 records:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 395 bytes) in /var/www/html/tagnet/system/database/DB_driver.php on line 523

Can anyone replicate this?

Thanks,
Bo

Here is some code that will cause the problem:
<?php
class Bug_Test_Controller extends Controller {
 
  function Bug_Test_Controller()
  {
        parent::Controller();
        $this->load->database(“test_scale”);
  }

  function run()
  {
        $sql = “INSERT INTO categories (name, description) VALUES (?,?)”;
        for ($i = 0; $i < 100000; $i++) {
            $parms = array($i, “”);
            $query = $this->db->query($sql, $parms);
        }
  }
}
?>

I believe that’s a PHP limitation and not CI’s.

You could try upping your own memory via ini_set() but many hosts don’t allow it.  You could also ask your host to bump it.

Truthfully though, your best bet is just to insert in small batches.

I guess I don’t understand…

What is being stored in memory? 
The code just loops through the queries and executes them individually.  No state should be involved, should it?
It seems like unreasonable behavior to me.

Its the nature of server-side programming.  Your host is limiting how much memory you have to do “stuff”.  You can create a file called “info.php” and put it on your server

Now visit it.  Look down it and you’ll see “memory_limit” in there.  I’m betting yours says 16M.

CI could do more if your host would let you, but it can only work within what it has.

PHP has a memory_limit usually 8MB but i think in your case is 16MB? you need to adjust you memory_limit in your php.ini to accommodate your queries. You can also try Bulk Queries in populating your database for speed.

I realize that a PHP application has a memory limit, but I would like to know why it is eating more memory for every SQL query it executes.

i.e., What is CI storing for each SQL query it executes?  ...and can I turn it off?

...or is it the PHP MySQL driver that is storing something?

I guess it could be database transactions if you have them enabled.  You could try disabling them, but even if you did these database inserts in direct PHP without CodeIgniter, you’d see the same thing.  CI may (I truthfully am not sure) add some additional overhead, but it would be wrong to blame what you are experiencing here on CI.

brighama - 07 March 2007 09:13 PM

I realize that a PHP application has a memory limit, but I would like to know why it is eating more memory for every SQL query it executes.

i.e., What is CI storing for each SQL query it executes?  ...and can I turn it off?

...or is it the PHP MySQL driver that is storing something?

because in your script you are looping queries using PHP stacking up data = bytes, IMHO your 200,000 queries running in PHP querying can consumed 16MB allowed memory_limit?

Derek Allard - 07 March 2007 09:16 PM

I guess it could be database transactions if you have them enabled.  You could try disabling them, but even if you did these database inserts in direct PHP without Code Igniter, you’d see the same thing.  CI may (I truthfully am not sure) add some additional overhead, but it would be wrong to blame what you are experiencing here on CI.

Yup, but there should be no blame here on both PHP and CI because PHP has already given you the options to increase your memory_limit also.

OK, I humbly concede that it is neither CI nor PHP that is to blame here.  My bad posting it in the bugs forum.

...but I’m still ignorant:  does anyone know what is in those 16M?

(Turning off transactions had no effect.)

Thanks again,
Bo

brighama - 07 March 2007 09:32 PM

OK, I humbly concede that it is neither CI nor PHP that is to blame here.  My bad posting it in the bugs forum.

...but I’m still ignorant:  does anyone know what is in those 16M?

(Turning off transactions had no effect.)

Thanks again,
Bo

16MB is the allowed memory_limit each php scripts you are allowed to run. It’s like your RAM you have insufficient RAM for example you have 32MB of RAM in your CPU and you want to play a 512MB Required RAM to play a game smooth or else the game will output you with “Insufficient Memory” or something.

LOL!  Nothing to be humble about.  You had a question, that’s what the forums are for!  I can’t tell you exactly what is going on in those 16M (my best guess is black magic), but I can give you an answer to your original request for help.

To do what you want, just create a smaller loop and re-run the page a bunch of times.  Why not set it to 25,000 and just press F5 a bunch of times.  Seems the fastest and easiest

OK, sorry for apologizing. 
I’ll just file it under “Unknowable DB Driver Mysteries” and move on.

Unfortunately, this needs to be automated.  Hey, I suppose I could just have the script redirect to itself a bunch of times (suppresses gag.)

I’m hazarding a guess here ... There’s 2 preg-functions + 2 str_replace in this line which to may knowledge are all - especially the str_replace function - very memory consuming functions.
Probably try using the query-method without bindings to avoid this loop. Another possibility for generating test data would generating output in the form of SQL statements (INSERT INTO ...), saving them to a .sql file and importing them using the mysql command line tool.

Cheers

brighama - 07 March 2007 10:00 PM

OK, sorry for apologizing. 
I’ll just file it under “Unknowable DB Driver Mysteries” and move on.

Unfortunately, this needs to be automated.  Hey, I suppose I could just have the script redirect to itself a bunch of times (suppresses gag.)

like I said in my previous post maybe a single Bulk Query could do the job for you. Try searching something like CSV reader on PHP Classes they might help you,..