CodeIgniter Forums
DB inserts & Memory not cleared? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: DB inserts & Memory not cleared? (/showthread.php?tid=12732)

DB inserts & Memory not cleared? - El Forum - 10-29-2008

[eluser]J3roen[/eluser]
I encountered a problem when inserting lot's of data in a mysql database.

The memory used keeps increasing every insert query, for some reason
it saves some data somewhere? Shouldn't it clear the data after every insert?

Using version 1.7.

To reproduce:
Code:
CREATE TABLE `yourdatabase`.`test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`data` BLOB NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Code:
<?
class Test extends Controller {

    function Test()
    {
        parent::Controller();    
    }
    
    function index()
    {

        for($i = 0; $i < 200; $i++) {
            $data['data'] = file_get_contents("/path/to/some/file");
            
            $this->db->insert('test', $data);
            $image_id = $this->db->insert_id();

            echo round(memory_get_usage() / 1024 / 1024, 2) . "<br />";
            flush();
        }

    }
}
?&gt;

Produces:

Code:
2.8
3.42
<SNIP>
103.88
104.51

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 654047 bytes) in /home/websites/blabla/code/database/drivers/mysql/mysql_driver.php on line 488


DB inserts & Memory not cleared? - El Forum - 10-29-2008

[eluser]J3roen[/eluser]
Pure PHP example:

Code:
&lt;?
class Test extends Controller {

    function Test()
    {
        parent::Controller();    

        $link = mysql_connect('localhost', 'username', 'passwd');
    }
    
    function index()
    {

        for($i = 0; $i < 200; $i++) {
            $query = "INSERT INTO `test` (`data`) VALUES ('" . file_get_contents("/tmp/test.jpg") . "')";
            $result = mysql_db_query("yourdatabase", $query);
            
            echo round(memory_get_usage() / 1024 / 1024, 2) . "  $i <br />";
            flush();
        }

    }
}
?&gt;

In this case the memory usage stays the same and does not grow.

DB inserts & Memory not cleared? - El Forum - 10-29-2008

[eluser]J3roen[/eluser]
:red: it seems codeigniter is caching every query by default in the database class. For large amounts of data this results in a lot of overhead.

Solution:

$this->db->save_queries = false;

May be good idea to add this to the documentation? I couldn't find it myself in the documentation. Figured it out by doing a print_r on the database object.

Why is CI actually caching the queries?

DB inserts & Memory not cleared? - El Forum - 11-05-2008

[eluser]Naki[/eluser]
Thank you very much!
I had the same problem and your solution works fine ^^