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
I finally got it to work. Starts around 56MB when I pull in the rates and after 30,944 inserts its up to 58MB. Inserting takes ~17s and looping without any changes takes ~9s.
/**
* Date comes from MSSQL database in a weird format
* - where microseconds are only 3 digits long when
* - MySQL expects 6. To counteract this we take the
* - AM/PM off the end and the first 20 characters
* - off the start, which should give us the datetime
* - without the microseconds. Concatenate this and
* - return a DateTime object.
* @param string $date
* @return DateTime
*/
private function _formatMSSQLDate($date) {
// Date is null or empty
if(!$date) return false;
// Date isn't the right length
if(26 != strlen($date)) return false;
$am_pm = substr($date, -2);
$date = substr($date, 0, 20);
return new DateTime($date.$am_pm);
}
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 = 100; // Persist and flush the properties in groups of...
$legacy_rates = $this->doctrine->mssql
->getRepository('Entity\MSSQL\TblPropertyRent')
->findAllNew();
$this->doctrine->mssql->clear();
foreach ($legacy_rates as $i => $legacy_rate)
{
// 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_rate['proprentID']);
// 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_rate['proprentID']);
$rate->setName($legacy_rate['rent_name']);
$rate->setRate($legacy_rate['rent_rate']);
// 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 _formatMSSQLDate() function to see
// - what needs to be done to the date.
$rate->setDateStart($this->_formatMSSQLDate($legacy_rate['startdate']));
$rate->setDateEnd($this->_formatMSSQLDate($legacy_rate['enddate']));
$rate->setPropertyId($legacy_rate['propertyID']);
// If override is null or 0, use default (=2)
$rate->setMinimumNights($legacy_rate['min_nights_ovride']?$legacy_rate['min_nights_ovride']:2);
$rate->setDateUpdated($this->_formatMSSQLDate($legacy_rate['dateadded']));
// Persist this feature, ready for flushing in groups of $persist_bunch
$this->doctrine->em->persist($rate);
$persist_count++;
} else {
$this->doctrine->em->detach($rate);
unset($legacy_rates[$i]);
}
// 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->doctrine->em->flush();
$this->doctrine->em->clear();
$persist_count = 0;
}
}
// Flush any remaining properties
$this->doctrine->em->flush();
}
Coupled with this function in the repository:
public function findAllNew() {
$DQL = <<< DQL
SELECT f.proprentID,
f.rent_name,
f.rent_rate,
f.startdate,
f.enddate,
f.propertyID,
f.min_nights_ovride,
f.dateadded
FROM Entity\MSSQL\TblPropertyRent f
DQL;
try{
$rates = $this->_em
->createQuery($DQL)
->getArrayResult();
} catch(\Exception $e){
$rates = false;
}
return $rates;
}