I am trying to insert multiple records to MySQL table in single go.
FYI: My tech stack is Nodejs+MySQL (https://www.npmjs.com/package/mysql)
What I am currently doing is:
INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The problem with my above solution is that when I have around 50k records, the MySQL buffer explodes.
For that I changed
max_allowed_packet = 1000MB
But I am sure that is not a proper solution. I am looking for the best & optimized workaround possible in this scenario.
Any clues or hints would be highly appreciated. Thanks
For many reasons (you encountered one of them), I break up batch inserts into clumps of 100 or 1000 rows at a time. That will be within 1% of the same speed, while avoiding lots of problems.