I have a loop going through an old database and migrating new rows to the new database. Both databases will still be used for some time, so this is supposed to be a synchronisation that can run regularly. Ideally multiple times an hour checking for changes:
public function sync_rates()
{
// Disable runtime limit
set_time_limit(0);
$persist_count = 0; // Keep track of home many properties are ready to be flushed
$flush_count = 1; // Persist and flush the properties in groups of...
echo memory_get_usage() . "<br />";
$legacy_rates = $this->doctrine->mssql
->getRepository('Entity\MSSQL\TblPropertyRent')
->getAllIDs();
echo memory_get_usage() . " after IDs<br />";
foreach ($legacy_rates as $legacy_id)
{
echo memory_get_usage() . " in loop<br />";
// Instantiate the rate
$legacy_rate = $this->doctrine->mssql
->getRepository('Entity\MSSQL\TblPropertyRent')
->findOneBy(array(
'proprentID' => $legacy_id['proprentID']
));
// Lets see if this rate already exists in the new database. If it does, we'll just use that.
$rate = $this->doctrine->em
->getRepository('Entity\Beaverusiv\PropertyRate')
->findOneById($legacy_id);
// If the rate from the legacy database does not exist in the new database, let's add it.
if (! $rate)
{
$rate = new Entity\Beaverusiv\PropertyRate;
$rate->setId($legacy_id['proprentID']);
$rate->setName($legacy_rate->getRentName());
$rate->setRate($legacy_rate->getRentRate());
// Have to do it this way with a new DateTime object because MSSQL stores its dates
// - in a different format to MySQL. Refer to the getStartdate() function to see
// - what needs to be done to the date.
$rate->setDateStart(new DateTime($legacy_rate->getStartdate()));
$rate->setDateEnd(new DateTime($legacy_rate->getEnddate()));
$rate->setPropertyId($legacy_rate->getPropertyID());
// If override is null or 0, use default (=2)
$rate->setMinimumNights($legacy_rate->getMinNightsOvride()?$legacy_rate->getMinNightsOvride():2);
$rate->setDateUpdated(new DateTime($legacy_rate->getDateadded()));
// Persist this feature, ready for flushing in groups of $persist_bunch
$this->doctrine->em->persist($rate);
$persist_count++;
}
unset($legacy_rate);
// Don't know if I can do this! Does Doctrine need that variable after I've persisted it?
unset($rate);
// If the number of properties ready to be flushed is the number set in $flush_count, lets flush these properties
if ($persist_count == $flush_count) {
// This makes it run a LOT slower!
// Get memory under control so we don't need to do this.
$this->doctrine->em->flush();
$this->doctrine->em->clear();
$this->doctrine->mssql->clear();
$persist_count = 0;
die(); //Here so I don't have to wait long.
}
}
// Flush any remaining properties
$this->doctrine->em->flush();
}
The memory usage means it runs out of memory before even entering new rows into the table. There are 12,300 rows currently in the new and just under 40,000 in the old table.
The output currently looks like this:
1810464
16618448 after IDs
16618448 in loop
18144344 in loop
18152368 in loop
18161920 in loop
...
131038824 in loop
131046832 in loop
131054824 in loop
131062816 in loop
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8388608 bytes) in
/mnt/code/beaverusiv/vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/DateTimeType.php on line 53