PDO MYSQL_ATTR_USE_BUFFERED_QUERY Не влияет

У меня есть следующий примерный код (полный код - 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 гб.

Тестирование 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

и вести себя так же.

Источник

3 ответа

Решение

В 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 (Это говорит о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

и так далее.

Источник
Другие вопросы по тегам