Как вставить 1M+ строк в MySQL?

Question

[BigCrazyCat]

Сайт на Yii2. PHP 7.2, MySQL 5.7.
Использую следующий код для вставки новой строки:

$command = Yii::$app->db->pdo->prepare("INSERT INTO product (
    gs_id, available, shop_id, product_key,
    article, category_id, picture, thumbnail,
    original_picture, name, description, brand_id,
    model, old_price, price, url, destination_url
 ) VALUES (
    :gs_id, :available, :shop_id, :product_key,
    :article, :category_id, :picture, :thumbnail,
    :original_picture, :name, :description, :brand_id,
    :model, :old_price, :price, :url, :destination_url
)", [\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]);

$command->bindParam(':gs_id', $gs_id);
$command->bindParam(':available', $available);
...
$gs_id = 1;
$available = 1;
...
$command->execute();

Уже на 200000-ой записи процесс Апача потреблят около 1.5 Гб оперативной памяти. Без сохранения в БД - ~100 Мб. Только сохранение данных, обработки после нет.
Есть идеи?

Comments

[Chupaka]

Т.е. если закомментировать вызов $command->execute(); — потребление памяти резко снижается?

[BigCrazyCat]

Chupaka, ага.

Answer 1

[Stalker_RED]

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Или если из дампа
mysql -u dbLogin -p dbName < somefile.sql

Comments

[BigCrazyCat]

Реализовал через LOAD DATA INFILE на чистом PHP. Yii2 в аналогичном процессе потребляет намного больше памяти.

[Stalker_RED]

BigCrazyCat, вообще это можно писать прямо в консоли mysql, совсем без участия php.

[BigCrazyCat]

Stalker_RED, я понимаю, но это лишь часть скрипта. Перед загрузкой в БД идет получение и обработка данных, после загрузки обновление конфигурации.

Answer 2

[Fixid]

Используйте bulk insert
Хардкор вариант: сгенерировать bulk insert и записать в файл. Потом потоком перенаправить в mysql

Comments

[BigCrazyCat]

Пробовал. Тогда приходиться временно хранить значения в массиве и в конце записывать в БД. Потребление памяти такое же или больше, даже при использовании SplFixedArray. Грешу на БД.

[Fixid]

BigCrazyCat, пишите в файл вместо памяти, потом вычитывайте и передавайте в mysql. одним большим запросом.
Как часто надо проводить эту процедуру?

[BigCrazyCat]

Fixid, раз в месяц.

[Fixid]

BigCrazyCat, тогда в чем проблема записать файл и из файла загрузить в mysql? Оверхеда по памяти не будет

[BigCrazyCat]

Fixid, проверю.

Answer 3

[Wol_fi]

Ну во первых - забудьте про использование php при таких количествах данных. Php не самый оптимальный вариант когда кол-во оперативной памяти ограничено. Здесь гораздо лучше использовать тот же mysql-client или mysqldump. Естественно вставлять лучше одним запросом. Во вторых - саму СУБД тоже надо подготовить к записи такого кол-ва данных. Этого обычно нигде не пишут, но mysql не пригоден для использования "из коробки". Для начала можно попробовать отключить бинарный лог(если нет необходимости в репликации и т.п.). Это освободит драгоценные иопсы на ЖД, особенно если на сервере присутствует не только одна база данных. Если таблица innodb(или его форк) - то настроить необходимые переменные этого движка(количество потоков записи, размеры всяческих буферов, логирование транзакций и т.д.) исходя из возможностей сервера. Все вместе по итогу даст и прирост скорости выполнения запроса и оптимизацию использования серверных ресурсов.

Answer 4

[BigCrazyCat]

Реализовал через LOAD DATA INFILE, но c потреблением памяти стало еще хуже.
Теперь на 50000 строк приходится столько же, как раньше на 250000.
Код:

$dataString .= "$gs_id|$available|$shop_id|$product_key|$article|$category_id|$picture|$thumbnail|$original_picture|$name|$description|$brand_id|$model|$old_price|$price|$url|$destination_url\n";
$dataPath = str_replace('\\', '/', Yii::$app->basePath)  . '/products/products.txt';
$dataFile = fopen($dataPath, 'w');
fwrite($dataFile, $dataString);
fclose($dataFile);
unset($dataString, $dataFile);

Yii::$app->db->createCommand('SET GLOBAL local_infile = true;')->execute();
Yii::$app->db->createCommand("
            LOAD DATA LOCAL INFILE '$dataPath'
            INTO TABLE product
            FIELDS TERMINATED BY '|'
            (
                gs_id,available,shop_id,product_key,article,category_id,
                picture,thumbnail,original_picture,name,description,brand_id,
                model,old_price,price,url,destination_url
            )
 ")->execute();

Comments

[xenm]

Так у вас файл сначала вся строка генерируется в скрипте. Конечно для нее памяти нужно много. Попробуйте в цикле записывать по несколько строк и после записи очищать все переменные. Ну и попробуйте выполнить запрос LOAD DATA.. из mysql клиента и посмотрите на расход памяти там

[Dmitry Bay]

А еще Debug отключи, он много есть.

[BigCrazyCat]

xenm, база данных отлично справляется в одиночку, если загружать файл через клиент. А вот в связке с Апачем при использовании Yii2 память первого постоянно забита. И это случается при добавлении данных в БД.

[BigCrazyCat]

xenm, сделал запись каждые 25000 строк. При обработке данных и записи в файл использованная память на превышает значения 100 Мб. Как только начинается запись в БД, буквально каждую секунду +50-100 Мб.

[BigCrazyCat]

Dmitry Bay, не помогло.

[Dmitry Bay]

BigCrazyCat, это не решение, это один из пунктов.

[BigCrazyCat]

Dmitry Bay, не помогло = никак не повлияло на потребление памяти.