Требование:
У нас есть две одинаковые таблицы на двух серверах. Первая таблица на сервере, имеющая уникальные ключевые столбцы A, B, C, и мы вставляем строки Table1 в Table2, которые имеют уникальные ключевые столбцы B, C, D.
Таблица 1 содержит около 5 миллионов строк, а таблица 2 будет вставлять около 3 миллионов строк из-за различных ограничений уникальных ключевых столбцов.
Здесь необходимо выбрать все строки из Таблицы1 и вставить в Таблицу2, если в Таблице2 нет одинаковых записей, а в случае совпадения записей увеличить счетчик и обновить столбец «cron_modified_date» в Таблице2.
Версия PHP — 5.5, версия MySQL — 5.7 для этой установки, а на сервере БД установлено 6 ГБ ОЗУ.
При выполнении нижеприведенного сценария скорость обработки становится очень медленной после обработки 2 миллионов записей, и ОЗУ не освобождается, и через некоторое время вся ОЗУ используется сценарием, а после этого сценарий вообще не обрабатывается.
Как видите, я сбрасываю переменные и закрываю соединение с БД, но это не освобождает ОЗУ сервера БД. После некоторого прочтения я узнал, что сборщик мусора в PHP должен вызываться вручную, чтобы освободить ресурсы, но также не освобождает ОЗУ.
Что я здесь не так делаю и как обрабатывать миллионы записей с помощью PHP, MYSQL?
Любой другой способ освободить оперативную память при выполнении сценария и чтобы этот сценарий конкурировал с выполнением?
/* Fetch records count for batch insert*/
$queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
$rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();
$recordsPerIteration = 50000 ;
$totalCount = $rowsCount[0]['totalRecords'];
$start = 0;
gc_disable() ;
if ( $totalCount > 0 ) {
while ( $totalCount > 0 ) {
$query = "SELECT * FROM TABLE1
WHERE where created_date > = '2018-02-10'
ORDER BY suggestion_id DESC
LIMIT ".$start.",".$recordsPerIteration;
print "sql is $query" ;
$getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
$GLOBALS['db']->queryString = null;
$GLOBALS['db']->close() ;
foreach ($getAllRows as $getRow) {
$insertRow = " INSERT INTO TABLE2 (
Name,
Company,
ProductName,
Status,
cron_modified_date)
VALUE (
".$GLOBALS['db_ab']->quote($getRow['Name']).",
".$GLOBALS['db_ab']->quote($getRow['Company']).",
".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
".$getRow['Status'].",
".$GLOBALS['db_ab']->quote($getRow['created_date']).")
ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date = '".$getRow['created_date']."'" ;
$GLOBALS['db_ab']->execRaw( $insertRow ) ;
$GLOBALS['db_ab']->queryString = null;
$getRow = null;
$insertRow = null;
$GLOBALS['db_ab']->close() ;
}
gc_enable() ;
$totalCount = $totalCount- $recordsPerIteration;
$start += $recordsPerIteration ;
$getAllRows = null;
gc_collect_cycles() ;
}
}
После предложений, предоставленных @Abelikov и немногих ударил & методы следа … Наконец, приведенный ниже код работает отлично и освобождает ОЗУ после вставки каждых 50К записей.
Ниже приведены основные выводы
Клуб вставьте заявления и выполните вставки за один раз. Не выполняйте вставку одной записи в цикле.
Спасибо, ребята, за ценные предложения и помощь.
/* Fetch records count for batch insert*/$queryCount = "SELECT count(*) as totalRecords FROM TABLE1 where created_date > = '2018-02-10'";
$rowsCount = $GLOBALS['db']->execRaw( $queryCount)->fetchAll();
$recordsPerIteration = 50000 ;
$totalCount = $rowsCount[0]['totalRecords'];
$start = 0;
if ( $totalCount > 0 ) {
while ( $totalCount > 0 ) {
$query = "SELECT * FROM TABLE1
WHERE where created_date > = '2018-02-10'
ORDER BY suggestion_id DESC
LIMIT ".$start.",".$recordsPerIteration;
print "sql is $query" ;
$getAllRows = $GLOBALS['db']->execRaw( $query )->fetchAll();
$GLOBALS['db']->queryString = null;
$GLOBALS['db']->close() ;
$insertRow = " INSERT INTO TABLE2 (
Name,
Company,
ProductName,
Status,
cron_modified_date)
VALUE ( " ;foreach ($getAllRows as $getRow) {$insertRow .= (".$GLOBALS['db_ab']->quote($getRow['Name']).",
".$GLOBALS['db_ab']->quote($getRow['Company']).",
".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
".$getRow['Status'].",
".$GLOBALS['db_ab']->quote($getRow['created_date'])."),";
}
$insertRow=rtrim($insertRow,','); // Remove last ','
$insertRow.= " ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date = '".$getRow['created_date']."'" ;
$GLOBALS['db_ab']->execRaw( $insertRow ) ;
//Flushing all data to freeup RAM
$GLOBALS['db_ab'] = null ;
$GLOBALS['db'] = null ;
$insertRow = null;
$totalCount = $totalCount- $recordsPerIteration;
$start += $recordsPerIteration ;
$getAllRows = array();
$getAllRows = null;
print " \n Records needs to process ".$totalCount."\n";
}
}
1. Вставить несколько строк решения
Вы можете ускорить ваш сценарий, используя «вставить несколько строк» см. Здесь https://dev.mysql.com/doc/refman/5.5/en/insert.html
INSERT INTO tbl_name (a, b, c) ЗНАЧЕНИЯ (1,2,3), (4,5,6), (7,8,9);
Вы должны оставить только ЦЕННОСТИ в своем foreach и убрать все остальные
$insertRow = " INSERT INTO TABLE2 (
Name,
Company,
ProductName,
Status,
cron_modified_date) VALUES ";
foreach ($getAllRows as $getRow) {
$insertRow.="(".$GLOBALS['db_ab']->quote($getRow['Name']).",
".$GLOBALS['db_ab']->quote($getRow['Company']).",
".$GLOBALS['db_ab']->quote($getRow['ProductName']).",
".$getRow['Status'].",
".$GLOBALS['db_ab']->quote($getRow['created_date'])."),";
}
$insertRow=rtrim($insertRow,','); // Remove last ','
$insertRow .= " ON DUPLICATE KEY UPDATE count = (count + 1) , cron_modified_date = '".$getRow['created_date']."'" ;
$GLOBALS['db_ab']->execRaw( $insertRow ) ;
$GLOBALS['db_ab']->queryString = null;
$getRow = null;
$insertRow = null;
$GLOBALS['db_ab']->close() ;
Это поможет, только если ваше «тело» foreach обычно выполняется более одного раза
2. MySQL серверное решение
Попробуй использовать СДЕЛКУ https://dev.mysql.com/doc/refman/5.7/en/commit.html http://php.net/manual/en/pdo.begintransaction.php
Просто начните один в начале сценария и подтвердите в конце.
Зависит от вашего сервера, это может действительно помочь.
Но будь осторожен! Это зависит от ваших настроек сервера MySQL. Нужно проверить.
Если я не понял задачу, вы слишком много работаете над ней. Просто включите MySQL, чтобы сделать все работа. Нет массива, нет порции, только один SQL:
INSERT INTO table2
(Name, Company, ProductName, Status, cron_modified_date, count)
SELECT Name, Company, ProductName, Status, created_date, 1
FROM table1
ON DUPLICATE KEY UPDATE
count = count + 1
cron_modified_date = created_date;
Обратите внимание, что вы может нужно использовать псевдо-функцию VALUES()
в некоторых обновлениях.
Это позволяет избежать выборки всех (или даже 5000) строк в PHP, что, вероятно, связано с проблемой памяти. Простая переменная в PHP занимает примерно 40 байтов. MySQL предназначен для работы с любым количеством строк без потери оперативной памяти.