MYSQL Insert - import json file with lots of data - how?

Question

PHP

Continuing the topic - I import data received via API from a partner into the MySQL database.
It gives the data in JSON format. Data - information on hotels in a particular city - full - name, address, links to photos, etc.
And let's say I take Moscow - in json for importing 1301 hotels, 32 thousand records about photographs.
And now I can’t enter this information into the database - there is not enough power or something, or some settings are not the same somewhere.
I do the import on VPS
Xeon 2600 series, 1 core
1024 MB RAM
32 GB SSD
Import the next
api remote, get the file through file_get_contents, then json_decode
and then go through the received array with the for loop.
In each iteration - insert for adding a hotel
then a few more inserts for hotel options into the next separate tables, and the biggest one is an
insert with many rows for photos.
And so - the server does not seem to take out dropping out with a 500 error. the load graph grows up to 97% - I suspect that because of this, this is exactly why everything is cut off.
The server set up itself - or rather deployed VESTA, for php increased the time limit to 120 and twisted the mysql config.
In general, how to be? This is only Moscow, and there are more than 30 thousand cities. it is clear that in many cities there are much fewer hotels, but there are still enough large cities.

Answer 1

From personal experience:
- "the server does not seem to be exported with a 500 error" - but what about the error log? Everything is written there, why it falls.
- the worst thing is to make requests to change data outside of an explicitly open transaction into a transactional table (InnoDB). set autocommit=0, you start a transaction and you can insert data in a loop. An implicit transaction for each request slows down the work every 100-200 times.
- @zelenin 's batch query option - haven't tried it at all, but might be a good alternative to explicit transactions.
- there is no need to do this via the web, and have the nuances associated with it - the time limits of php and the web server itself. Perform the import by running the script from the command line. I did the demons this way, they worked on the principle of "launch and forget", but that's a completely different story.
In general, ~1300 records about hotels and 32000 photos (purely url's with descriptions, probably?) - not so much data, most likely you have a bottleneck - it is the transfer of data from PHP to MYSQL.

Answer 2

500 error - this is an error in the code, what is the text of the error?
I would delegate part of the processes to other scripts. for example, you can use some kind of queue server like gearman, and send inserts to the database in the background task, and leave the script only to parse json. this will stretch everything in time, but it will reduce resource consumption and increase stability.
plus there are libs for "line-by-line" reading of json, it can also help.
and what version of php, maybe something is leaking?

Answer 3

the most banal mistake - inserts in a cycle. In the loop, you need to collect 1 large insert of the form:
insert table (id, name, value) values ​​(1, x, x), (2, x, x) and so on. And after the cycle, already execute it. If there is a lot of data, you can insert data not with one insert, but with several (1-10 records at a time).

Answer 4

Did you increase the memory for php?

Answer 5

When mass inserting or updating records in MySQL, a very strong acceleration can be obtained if you wrap a bunch of INSERTs or UPDATEs in a transaction. You can generally do the entire import in one transaction, you can cut it into several smaller transactions.
Well, in general, figure out where everything falls down for you or eats too much time and resources. Break the task into several pieces, and complete each separately.
- reading data from a remote source
- json parsing
- generating SQL commands
- executing SQL commands
Then you will understand exactly where your problem is.