How to insert 1M+ rows in MySQL?

Question

Yii

Site on Yii2. PHP 7.2, MySQL 5.7.
I use the following code to insert a new line:

$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();

Already on the 200000th record, the Apache process will consume about 1.5 GB of RAM. Without saving to the database - ~ 100 Mb. Only saving data, no processing after.
Any ideas?

Answer 1

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
https://dev.mysql.com/doc/refman/8.0/en/load-data.html
Or if from dump
mysql -u dbLogin -p dbName < somefile.sql

Answer 2

Use bulk insert
Hardcore option: generate bulk insert and write to file. Then redirect the stream to mysql

Answer 3

Well, firstly - forget about using php with such amounts of data. Php is not the best option when the amount of RAM is limited. Here it is much better to use the same mysql-client or mysqldump. It is natural to interpose better one request. Secondly, the DBMS itself must also be prepared to record such a number of data. This is usually not written anywhere, but mysql is not usable out of the box. For starters, you can try to disable the binary log (if there is no need for replication, etc.). This will free up precious IOPS on the HDD, especially if there is more than one database on the server. If the table is innodb (or its fork), then set the necessary variables of this engine (the number of write streams, the sizes of all buffers, transaction logging, etc.) based on the capabilities of the server.

Answer 4

Implemented via LOAD DATA INFILE , but with memory consumption it got even worse.
Now 50,000 lines have the same number as before 250,000.
Code:

$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();