How to correctly release Data Access Objects

system · April 3, 2009, 12:36pm

Hi all,

I have written some command line tool using Yiic to process some large amount of database records. This is working just fine.

However, I'm running into some PHP memory management issues.

For exemple, I'm using that kind of code to process records :



$dataReader=$this->connection_db->createCommand("SELECT * FROM MyTable")->query();


while(($row=$dataReader->read())!==false) {


   $command2=$this->connection_db->createCommand("SELECT * FROM MyTable2 WHERE id=:id");


   $command2->bindParam(":id",$row['Ext_Id'],PDO::PARAM_STR);


   $row2=$command2->queryRow();


   // Do some usefull stuff


   unset($row2, $command2);


}

Even if objects are released by an unset command, memory just grow on and on (can be checked by a call to memory_get_usage).

So, my question is : Is there a best practice to correctly release those DAO to avoid this issue?

Thank you for your help.

DanielCarrera · April 3, 2009, 12:44pm

Does it help if you add $dataReader = null at the end? I remember seeing that in the PDO documentation, and I know that Yii uses PDO, so perhaps that will work too. But I’m just guessing.

qiang · April 3, 2009, 12:45pm

I think you can move $command2 out of the while-loop. Inside the loop, you simply bind it with different parameters.

system · April 3, 2009, 1:16pm

Quote

Does it help if you add $dataReader = null at the end? I remember seeing that in the PDO documentation, and I know that Yii uses PDO, so perhaps that will work too. But I'm just guessing.

I'll try that and let you know. Thanks.

DanielCarrera · April 3, 2009, 1:59pm

Quote

I'll try that and let you know. Thanks.

Ok. Btw, qiang is right - you should move the first two lines of $command2 out of the loop. What you have right now is very wasteful and deprives you of some of the benefits of prepared statements. Also remove the unset line. That's actually doing more harm than good. You can unset after the loop if you like. This is what I would suggest:

<?php


$dataReader=$this->connection_db->createCommand("SELECT * FROM MyTable")->query();


$command2=$this->connection_db->createCommand("SELECT * FROM MyTable2 WHERE id=:id");


$command2->bindParam(":id",$row['Ext_Id'],PDO::PARAM_STR);


while(($row=$dataReader->read())!==false) {


   $row2=$command2->queryRow();


   // Do some usefull stuff


}


unset($row2, $command2);

qiang · April 3, 2009, 2:09pm

Thanks Daniel for helping explaining.

Another thing I noticed is that you are essentially doing JOIN in PHP and in a very inefficient way. For your task, you should just issue one single JOIN SQL statement and bring back all the results you need.

DanielCarrera · April 3, 2009, 2:31pm

Quote

Another thing I noticed is that you are essentially doing JOIN in PHP and in a very inefficient way. For your task, you should just issue one single JOIN SQL statement and bring back all the results you need.

Indeed, you are right. I didn't realize that this code was just a JOIN.



<?php


$dataReader=$this->connection_db->createCommand("


	SELECT * FROM MyTable


	LEFT JOIN MyTable2 ON MyTable.Ext_Id = MyTable2.id")->query();


while(($row=$dataReader->read())!==false) {


   // Do some usefull stuff


}

juban, you probably want to look at this SQL tutorial to learn about joins. In particular, I don’t know if you need a LEFT JOIN or an INNER JOIN. If you can get away with it, an INNER JOIN is better because MySQL has more room to optimize it.

system · April 3, 2009, 3:17pm

Quote

Thanks Daniel for helping explaining.

Another thing I noticed is that you are essentially doing JOIN in PHP and in a very inefficient way. For your task, you should just issue one single JOIN SQL statement and bring back all the results you need.

Thanks Daniel and Qiang.

Please, don't try to anlayse the exemple code too close, I'm perfectly aware this is a dumb one.

I wanted to put it as simple as I could to explain the issue.

However, my initial question remains the same :

What is the best way to destroy a DAO? (the null thing doesn't help).

system · April 4, 2009, 10:17am

Looks like I was a bit confused

Ok. So, let’s start over with a brand new exemple (I hope to be clear this time )

So, my yiic command code look like the following :



class TestCommand extends CConsoleCommand {


    public function run($args)


    {


        $countries = array ("AD", "AE", "AF", "AG", "AI", "AL", "AM", "AN", "AO", "AQ", "AR", "AS", "AT", "AU", "AW", "AZ");


		$command=Yii::app()->db->createCommand("SELECT * FROM rdn_countries WHERE iso=:country");


		foreach ($countries as $country) {


			$command->bindParam(":country",$country,PDO::PARAM_STR);


			$row=$command->queryRow();


			// Do something usefull


			echo "n".memory_get_usage();


		}


    }





}

Running this will lead to following output :

847840

849208

849816

850416

850984

851568

852096

852752

853272

853864

854456

854984

855536

856096

856656

857224

(Once again, I know I could use a SQL query with an "iso IN (…)" statement to retrive all records at once. I'm using a simple query just for the exemple purpose.)

As you can see, the memory usage is just growing every time I call the DAO object. My guess is that the "$row" variable is not correctly released by the PHP garbage collector. Maybe, it's a PDO related issue.

In this exemple, this is not a big deal. However, if my country array was 30000 entries long, it could lead to serious memory leak issues.

Any ideas?

DanielCarrera · April 4, 2009, 10:46am

Btw, you should move the bind command out of the loop.

You know what you could do? Rewrite the query using the IN command and test again. That would tell us whether the memory consumption actually has anything to do with DAO or whether it is caused by something else. From your code, it is not clear that the memory increase is DAO's fault.

qiang · April 4, 2009, 11:42am

Two things to try:

Rewrite these code in pure PDO to make sure if Yii introduces any memory leak.
Run with more countries and see if the memory consumption keeps growing. Sometimes, internal buffer could take some memory.

system · April 4, 2009, 12:31pm

Quote

Btw, you should move the bind command out of the loop.

You know what you could do? Rewrite the query using the IN command and test again. That would tell us whether the memory consumption actually has anything to do with DAO or whether it is caused by something else. From your code, it is not clear that the memory increase is DAO's fault.

Thanks for your suggestion Daniel.

Sure, the "IN" command will partially solve the issue (as I mentioned it).

Anyway, in many more complicated cases, that is unfortunately not an option.

I did many tests using different methods and clearly, there is a memory issue using either ActiveRecord or DAO objects. However, it is not clear to me if this is a PDO problem or a Yii implementation issue.

system · April 4, 2009, 12:45pm

Quote

Two things to try:

Rewrite these code in pure PDO to make sure if Yii introduces any memory leak.
Run with more countries and see if the memory consumption keeps growing. Sometimes, internal buffer could take some memory.

Thanks for your answer Qiang.

I did not test the code with pure PDO but I did using old fashion MySQL PHP commands and, as long as I unset the recordsets results inside the foreach loop, I didn't got the memory problem at all. I'll try it with PDO and let you know
Yes, I did that kind of thing with large amount of data and ran into many PHP "Out of memory" errors (I was able to fill up all available memory). So yes, the memory consumption keeps growing.

DanielCarrera · April 4, 2009, 12:54pm

Quote

Anyway, in many more complicated cases, that is unfortunately not an option.

I know. I just meant as a test to isolate the cause of the problem.

Quote

I did many tests using different methods and clearly, there is a memory issue using either ActiveRecord or DAO objects. However, it is not clear to me if this is a PDO problem or a Yii implementation issue.

I definitely expect ActiveRecord to require more memory and be generally slower. All that "auto-magic" has a cost. But I do not expect DAO to be slower than PDO. DAO is supposed to be only a very thin layer around PDO, and I've looked at the DAO source code and indeed it's just a thin layer.

Can you compare DAO vs straight PDO?

Edit: This is also the reason why I don't use ActiveRecord in my programs. I do everything with DAO or PDO (and I enjoy writing SQL anyways).

system · April 4, 2009, 1:21pm

Daniel, you’re right, I don’t use ActiveRecords for intensive SQL processes. That’s why I use DAO instead for the same reasons you mentioned

For simple CRUD operations and from the MVC perspective however, AR just rocks !!! That's one of masterpieces of the Yii Framework.

Anyway, as thin as DAO layer can be, I did what Qiang suggested and rewrote the code using pure PDO as followed:



class TestCommand extends CConsoleCommand {


    public function run($args)


    {


        $countries = array ("AD", "AE", "AF", "AG", "AI", "AL", "AM", "AN", "AO", "AQ", "AR", "AS", "AT", "AU", "AW", "AZ");


		$dbh = new PDO(Yii::app()->db->connectionString, Yii::app()->db->username, Yii::app()->db->password);


		$stmt = $dbh->prepare("SELECT * FROM rdn_countries WHERE iso=:country");


		$stmt->bindParam(':country', $country);


		foreach ($countries as $country) {


			$stmt->execute();


			$row = $stmt->fetch();


			echo "n".memory_get_usage();


		}


    }


}

The related output is:

800456

801232

801304

801328

801376

801448

I did further tests using large amount of data and found that memory doesn't grow infinitely (the garbage collector works ok in that case).

So, I maybe wrong, but seems to me that is a DAO issue. For some reason, some object reference are not released the way they should.

I will try to do more tests and try to detect where the problem comes from in the DAO class. I'll keep you informed if I find usefull information.

Thank you both again for you help.

DanielCarrera · April 4, 2009, 1:55pm

Quote

So, I maybe wrong, but seems to me that is a DAO issue. For some reason, some object reference are not released the way they should.

Indeed. That's a very useful test you just did. It points to a place for potential improvement in DAO. Btw, I assume that you also re-did the test with DAO with the bindParam outside the loop. Right? Just making sure that the problem is not the bindParam. And you also checked that the two tests do the same thing, right?

Just making sure…

system · April 4, 2009, 2:06pm

Quote

So, I maybe wrong, but seems to me that is a DAO issue. For some reason, some object reference are not released the way they should.

Indeed. That's a very useful test you just did. It points to a place for potential improvement in DAO. Btw, I assume that you also re-did the test with DAO with the bindParam outside the loop. Right? Just making sure that the problem is not the bindParam. And you also checked that the two tests do the same thing, right?

Just making sure…

Yes I did Daniel. Doesn't make any difference.

In fact, from what I could guess from the memory usage output using the DAO version compare to the pure PDO one, I think that the memory manager allocates new room each time the $row variable is filled, as if a new variable reference was inited each time. I should examine the DAO implementation closely to identify the place where that occurs.

Qiang, any clue?

qiang · April 4, 2009, 5:20pm

Try defining YII_DEBUG to be false. Internally, for each query, a trace statement will be executed which will record the SQL statement being executed.

I examined the DAO code and couldn't find anything obvious that could cause memory leak.

DanielCarrera · April 4, 2009, 6:13pm

juban, could you try the following experiment?: After your $stmt->fetch() enter the following command:

$stmt->closeCursor()

I would be interested to know if that recreates the memory leak with PDO. So the code you would test is:

<?php


class TestCommand extends CConsoleCommand {


    public function run($args)


    {


      $countries = array ("AD", "AE", "AF", "AG", "AI", "AL", "AM", "AN", "AO", "AQ", "AR", "AS", "AT", "AU", "AW", "AZ");


      $dbh = new PDO(Yii::app()->db->connectionString, Yii::app()->db->username, Yii::app()->db->password);


      $stmt = $dbh->prepare("SELECT * FROM rdn_countries WHERE iso=:country");


      $stmt->bindParam(':country', $country);


      foreach ($countries as $country) {


         $stmt->execute();


         $row = $stmt->fetch();


         $stmt->closeCursor();


         echo "n".memory_get_usage();


      }


    }


}

The reason I ask is that, looking at the DAO code, the main differences I see between the PDO version and the DAO version is that DAO runs Yii::trace() and $stmt->closeCursor(). So, those are the most likely candidates for a memory leak. Setting YII_DEBUG to false like qiang will test if the problem is Yii::trace(). Adding $stmt->closeCursor() to the PDO version will test if that's where the problem is.

Cheers.

system · April 4, 2009, 6:37pm

Quote

Try defining YII_DEBUG to be false. Internally, for each query, a trace statement will be executed which will record the SQL statement being executed.

I examined the DAO code and couldn't find anything obvious that could cause memory leak.

You are absolutely right Qiang!

In fact, I was running the script using the yiic helper which is set by default to use "define('YII_DEBUG',true)".

Turning the debug option off resolve the memory issue

Conclusion: I will always setup my own entry script in the future…

Thank you for your help. Yii rocks!