PHP/MySQL Multiple Users Trying to Insert more than 10,000 records at a time only works for one user

user_7553865 0
Viewed 61

I have an app where users can import records via CSV to the Database.

Problem is that when there are too many records (probably around 5000?) the system will basically only work for the first user who sent the request and the requests from the other users will be sent but no data will actually be inserted into the database and the users are forced to wait until the first person has finished and then can insert their data.

Currently this process is currently done by a for loop inserting a record one at a time.

What would be the correct way to implment this issue so users can insert records simultaneously?

mysql php
1 Answers

There's are a few things you can do to help solving this kind of problem,

Use LOAD DATA instead of INSERT

Using LOAD DATA can be 20x faster then a typical INSERT See https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Use multiple rows insert

Using multiple rows insert reduce the roundtrip latency and makes bulk insertion faster.

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

Use transactions and SET autocommit=0;

Disabing auto commit make insertions lot faster, however after running your inserts you MUST execute COMMIT command.

Improve user experience with queues

For avoiding user to have to wait until the end of insertion, you can queue the insert instead of doing it syncronously:

  1. Your user send the data to be inserted, the data is stored in a temporary folder
  2. You insert that data into a queue
  3. You give as response something like "data being processed"
  4. You need now a queue worker, some piece of code that constantly check the queue for data to be inserted and do it
  5. You might also have to allow user to check if his data was already processed.

There are more ways to improve insert performance, like disabling foreign key and primary key check. For more advices see https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html

user_34293230
Related