PHP/Yii2: how to speed up the execution of ~1 million requests in a row?

Question

MySQL

Hello.
A large array of data is obtained from a third-party server through the API, which needs to be scattered across different tables. In total - one million with something of records. It is obviously impossible to try to solve the problem "on the forehead":

foreach ($array as $attributes) {
    $model = new Model();
    $model->setAttributes($attributes);
    $model->save();
}

I tried it on smaller volumes, the server can no longer cope with ~ 200,000 records, and the current million is just the beginning. Options thought such:
1. Receiving and record in parts. Uncomfortable.
2. Writing all data to each table in one query. How? Transaction?
Maybe there is something else?

Answer 1

2. Writing all data to each table in one query. How? Transaction?

Naked mysqli without any wrappers, in batches of as many inserts as a maximum will fit into . request (a 1mb line should fit),

Answer 2

Use queues.

Answer 3

I see 2 options:
1. LOAD DATA - the link has already been given above.
2. insert many rows at once:
Example:
dev.mysql.com/doc/refman/5.6/en/insert.html
+ The best thing is to tune the server so that it is ready for such a volume (a million lines - that doesn't say anything)
but how exactly does the server fail? (a million lines - that doesn't say anything)

Answer 4

You can also merge everything into a file, and then import via the console.

Answer 5

1) www.yiiframework.com/doc-2.0/yii-db-command.html#b...
2) I have seen this trick

file_put_contents(
    '/path/to/some/insert/file.sql', // куда
    'INSERT INTO table VALUES', // формируем запрос вставки с экранированными данными
    FILE_APPEND // константа, которая указывает, что добавляем в конец файла
)

Then, in the console, run the following command:
What we get: the script adds insert queries, tail -f transfers each new line directly to insert into mysql

Answer 6

https://dev.mysql.com/doc/refman/5.6/en/load-data.html
The standard way to load large volumes.
"The server can't cope" - most likely there is not enough memory, break it into pieces that fit into memory / allocate more of it if necessary through Yii

Answer 7

I propose such a simple implementation of a buffer that batch writes to the database every n rows: https://gist.github.com/marcuzy/3bff7ab350ac6741def0

Answer 8

There was a similar problem in an environment with a very weak server, they fought like this - they collected all one text, and then they loaded everything via LOAD DATA. There was an even more crutch solution through a CSV file and exec.

Answer 9

Break the base into parts, put the parts on different servers. Those. sharding or in other words horizontal scaling.
If the data is not particularly critical, you can try switching to memcache.
Topic slides. www.slideshare.net/profyclub_ru/web-20-c-9951602