用Yii的console写了一个脚本更新数据库，一运行居然内存爆掉而挂了（php.ini:memory_size=128M）,感觉有点蹊跷,于是跟踪了一把,发现的确是一个问题,测试代码如下:
<?php
class testCommand extends CConsoleCommand
{
public function run($args)
{
$db = Yii::app()->db;
$cnt = 0;
$sql = "select b.bid,pv.pid,pv.prop_name,pv.vid,pv.name from brand b
join item_prop ip on b.pid = ip.parent_pid and b.vid = ip.parent_vid
join prop_value pv on ip.pid = pv.pid
where b.level = :level
group by pv.pid , pv.vid
order by b.bid";
$values = $db->createCommand($sql)->query(array(':level'=>1)); //结果集16.5w
//if has value , flag = 1 , otherwise break
$sql2 = "INSERT IGNORE INTO brand(pid,prop_name,vid,name,parent_bid,level) values(:pid,:prop_name,:vid,:name,:parent_bid,:level)";
$command = $db->createCommand($sql2);
foreach($values as $value){
$cnt++;
$command->execute(array(
':pid' => $value['pid'],
':prop_name' => $value['prop_name'],
':vid' => $value['vid'],
':name' => $value['name'],
':parent_bid' => $value['bid'],
':level' => 2,
));
if($cnt % 5000 == 0){
Yii::log("Current Pos : $cnt ...","info","command.GenerateBrand");
}
}
}
以上代码读出16.5w的数据,尝试插入另一个表,循环16.5w次,可以看到,基本就是两个prepare,然后一直execute而已,对象看起来应该只new了一次啊,CDbReader/CDbConnection/CDbCommand等,顶多new了两次(Command),运行的结果是:
内存从开始的19M=>120M,超过最大限额而爆掉…
重新用PDO方式写了一个对比脚本如下:
<?php
class testCommand extends CConsoleCommand
{
public function run($args)
{
$dbh = Yii::app()->db->getPdoInstance();
$cnt = 0;
$sql = "select b.bid,pv.pid,pv.prop_name,pv.vid,pv.name from brand b
join item_prop ip on b.pid = ip.parent_pid and b.vid = ip.parent_vid
join prop_value pv on ip.pid = pv.pid
where b.level = :level
group by pv.pid , pv.vid
order by b.bid";
$sth = $dbh->prepare($sql);
$sth->execute(array(':level'=>1)); //结果集16.5w
$sql2 = "INSERT IGNORE INTO brand(pid,prop_name,vid,name,parent_bid,level) values(:pid,:prop_name,:vid,:name,:parent_bid,:level)";
$sth2 = $dbh->prepare($sql2);
while($value = $sth->fetch()){
$cnt++;
$sth2->execute(array(
':pid' => $value['pid'],
':prop_name' => $value['prop_name'],
':vid' => $value['vid'],
':name' => $value['name'],
':parent_bid' => $value['bid'],
':level' => 2,
));
if($cnt % 5000 == 0){
Yii::log("Current Pos : $cnt ...","info","command.GenerateBrand");
}
}
}
可以看到,仅仅是把createCommand改为prepare而已,运行发现,内存从18M到了33M停住了.
本来我以为是在循环中new多次Command,可能会造成内存泄露,因为CDbConnection中的createCommand就是一直new CDbCommand,而没有释放的代码,但是以上测试发现,及时循环中没有createCommand,而只是不断execute,也会有内存泄露问题,很费解.
不知道qiang有何看法?以上测试是否有漏洞?