this might be a complaint, or a rant, or something like that.

For some reasons there is about 200 megabytes of data I would like to import into the new web application I am writing.

I thought doing so is easy with propel. Well it actually is, wouldn’t PHP have some strange issues. Perhaps they are propel issues but I guess PHP is also to blame.

I do a mysql_query against my old database and initialize a new propel connection for the new one. I loop over all rows for that table. 63000 rows. it failed with out of memory some times, until I found a memory setting that would allow that loop to complete. hold tight: it is 1GB. The data processed inside this loop weighs 50MB.

I am not really doing complex things. I new my Propel objects, invoke a few setters and then save() them. Using the same connection for all inserts brings already a lot of performance. but using the Propel Objects leaks memory each loop. But more interesting, If I just loop and comment all the propel stuff I also leak memory. or more precise, PHP does. Why does an empty loop over mysql data leak memory? is there an internal loop status object that keeps track of each iteration?

Okay the bulk of the memory leak goes credit to propel. It seems to be that the crossreferenced table-, database- and column maps never free the memory. The PHP garbage collector is unable to collect cyclic island references, so they stay in memory. There are some tickets open in propel trac, so perhaps this will get improved. The only thing I could do now is to explicitly unset all variables I am using myself to limit memory usage and to give PHP that gigabyte for the import. This will be a one time operation, but I wonder if there might be a better way to reduce memory consumption. And yes I would like to keep using the Propel objects.

Here some leaking sample code. The style is not perfect. its just a quick snippet. not that I am using the basePeer::doInsert because I needed here to preserve the ID (which is removed by the save() method); also its a bit faster.

mysql_connect("legacy-db", "olduser", "oldpw");
$data = mysql_query("select oid,title,text from db.table");
$max = mysql_num_rows($data);
 
$databaseManager = new sfDatabaseManager();
$databaseManager->initialize();
$con = Propel::getConnection(EntryPeer::DATABASE_NAME);
try {
  $con->begin();
  $i=0;
  while($row=mysql_fetch_row($data)){
    $i++;
    if ($i % 100 == 0) echo ($i."/".$max."\n");
    $e = new Entry();
    $e->setId($row[0]);
    $e->setTitle(utf8_encode($row[1]));
    $e->setBody(utf8_encode($row[2]));
    $crit=$e->buildCriteria();
    $crit->setDbName(EntryPeer::DATABASE_NAME);
    BasePeer::doInsert($crit, $con);
    unset($e);
    unset($crit);
    unset($row);
  }
  $con->commit();
} catch(PropelException $e) {
  $con->rollback();
  throw $e;
}
mysql_free_result($data);