我有以下粗略的代码(完整代码是 146 行,其中 90 行是字符串解析,如果需要可以添加):
ini_set('memory_limit', '7G');
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));
$db_ub->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$stmt = $db->prepare('select columns from stats where timestamp between ? and ?');
$stmt->execute(array('2020-04-25', '2020-05-25'));
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo memory_get_usage() .PHP_EOL;
echo $row['id'] . PHP_EOL;
$stmt2 = $db_ub->prepare('select somedata from users limit 1');
$stmt2->execute();
$row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
$type = !empty($row2['somedate']) ? 5 : 4;
$result = $db_ub->prepare('insert ignore into newtable (old, type) values (?, ?)');
$result->execute(array($row['id'], $type));
}
在$stmt->execute(array('2020-04-25', '2020-05-25'));
期间,我的内存消耗是.34GB
(使用ps aux | grep 'php ' | awk '{$5=int(100 * $5/1024/1024)/100"GB";}{ print;}'
来监视select
期间的消耗,并show full processlist
SQL端进行验证)。脚本进入while
后,它会跳转到 +5 GB。
测试setattribute
var_dump($db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false));
似乎它已经生效了:
bool(true)
但是当我切换缓冲或未缓冲时,行为不会改变。
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false)
和
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true)
使用echo $db->getAttribute(constant('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY'));
还会显示设置更改。
按照 https://www.php.net/manual/en/ref.pdo-mysql.php 建议将设置移动到语句而不是连接也不起作用。
$stmt = $db->prepare('select columns from stats where timestamp between ? and ?', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
我还尝试将缓冲区设置移动到连接上,而没有影响:
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
取出第二个连接似乎允许无缓冲查询按预期运行:
ini_set('memory_limit', '1G');
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
//$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));
//$db_ub->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$stmt = $db->prepare('select columns from stats where timestamp between ? and ?');
$stmt->execute(array('2019-01-25', '2019-11-25'));
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo memory_get_usage() .PHP_EOL;
echo $row['id'] . PHP_EOL;
/*
$stmt2 = $db_ub->prepare('select somedata from users limit 1');
$stmt2->execute();
$row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
$type = !empty($row2['somedate']) ? 5 : 4;
$result = $db_ub->prepare('insert ignore into newtable (old, type) values (?, ?)');
$result->execute(array($row['id'], $type));
*/
}
此用法memory_get_usage
不超过379999
.
如果我取消注释第二个连接并使其也取消缓冲,我会收到:
Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
缓冲的第二个连接按最初描述执行,执行时占用大量内存。如果ini_set('memory_limit'
高,则工作,如果低则出错。使用大型memory_limit
不是一个可行的解决方案。
正在使用 (Red Hat Enterprise Linux Server release 7.3 (Maipo)
):
php71u-pdo.x86_64 7.1.19-1.ius.centos7
已将脚本移至较新的计算机(Amazon Linux release 2 (Karoo)
):
php73-pdo.x86_64 7.3.17-1.el7.ius
并具有相同的行为。
PDO::ATTR_PERSISTENT
值不是布尔值。它标识正在使用的连接,对多个连接使用唯一值。就我而言:
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'unbuff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
$db_ub = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => 'buff', PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
您不能通过简单地运行单个查询来摆脱大部分代码:
INSERT IGNORE INTO newtable
SELECT ...,
IF(..., 5, 4)
FROM oldtable WHERE ...;
有了它,您可以摆脱7G内存问题。
如果事实证明这一次做得太多,那就把它分成几块。 请参阅此处的讨论:http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks(这谈到了DELETEs
,但它可以适应其他事物,例如您的SELECT
。
关于另一个主题:为什么select somedata from users limit 1
在循环中执行? 它似乎每次都得到相同的数据。 此外,如果没有ORDER BY
,您无法预测您将获得哪limit 1
行。
您实际上是在进行 135000000 次查询,而不是迭代超过 135000000 个对象。
将代码更改为仅执行一个查询,但对元素进行排序,就好像它们在 for 循环中一样。
$db = new PDO("mysql:host=".$dbhost.";dbname=".$dbname, $dbuser, $dbpass, array(PDO::ATTR_PERSISTENT => true));
$stmt = $db->prepare('SELECT * FROM stats ORDER BY id ASC');
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// ...
}
你甚至不需要这个if
,它是数据库本身可以更快地使用的逻辑:
if(!empty($row['id'])) {
相反:
SELECT * FROM stats WHERE id IS NOT NULL ORDER BY id ASC
我已经有一段时间没有研究PDO/MySQL,但我假设无缓冲允许您使用光标:
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
考虑到每个连接只能有一个活动查询。您基本上使用的是连接的缓冲区。
更好的选择是以地图缩减的方式仅加载小块。
SELECT * FROM stats LIMIT 100, 0
使用结果,然后
SELECT * FROM stats LIMIT 100, 100
等等。