How to prevent out of memory when inserting a million rows in mysql with php

Question

I built in Laravel a script that reads the JSON file line by line and imports the contents into my database.

However, when I run the script, I get an error from memory after inserting about 80 thousand records.

mmap() failed: [12] Cannot allocate memory mmap() failed: [12] Cannot allocate memory PHP Fatal error: Out of memory (allocated 421527552) (tried to allocate 12288 bytes) in /home/vagrant/Code/sandbox/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php on line 1758 mmap() failed: [12] Cannot allocate memory PHP Fatal error: Out of memory (allocated 421527552) (tried to allocate 32768 bytes) in /home/vagrant/Code/sandbox/vendor/symfony/debug/Exception/FatalErrorException.php on line 1 

I created a kind of temporary queue to only capture collected items every 100, but that didn't matter.

This is what the part of my code that does the inserts looks like:

 public function callback($json) { if($json) { $this->queue[] = [ 'type' => serialize($json['type']), 'properties' => serialize($json['properties']), 'geometry' => serialize($json['geometry']) ]; if ( count($this->queue) == $this->queueLength ) { DB::table('features')->insert( $this->queue ); $this->queue = []; } } } 

These are the actual inserts ( DB::table('features')->insert( $this->queue ); ) that cause an error, if I leave them I can perfectly iterate over all rows and output them without any problems with performance.

I suppose I could allocate more memory, but I doubt it would be a solution, because I am trying to insert 3 million records, and currently it does not work after 80K with 512 MB of allocated memory. Also, I really want to run this script on a low-budget server.

The time taken to execute this script is not a concern, so if I could somehow slow down the insertion of records, this would be a solution that I could agree to.

Answer 1

In my project, I installed itsgoingd / clockwork package . This is a habit that I have encountered because it is such a useful tool. (I recommend that all Laravel developers read this to check it out!)

Of course, this package logs requests for debugging purposes, and thus the culprit has eaten all of my memory.

Disabling the package by removing the service provider's link in my configuration resolved the issue.

Welcome @ dbushy727 for giving me a push in the right direction to figure this out.

Answer 2

If you are using MySQL 5.7+, it has a function to import data from a JSON file using LOAD DATA INFILE .

 LOAD DATA INFILE 'afile.json' INTO TABLE atable (field1, field2, ....); 

If you are using a lower version of MySQL, you first need to convert JSON to CSV format. For example, using https://github.com/danmandle/JSON2CSV

 LOAD DATA INFILE 'afile.csv' INTO TABLE atable (field1, field2, ....); 

See LOAD DATA INFILE documentation .

Answer 3

Make sure the query log is disabled. If query logging is enabled, even if you free the $ this-> queue, the query log grows until it reaches an unmanageable size and you reach the limit of your memory.

To check whether query logging is enabled or disabled, use the database facade as follows:

 DB::logging() 

If this returns true, then your log is turned on, and your problem. If this returns false, then this is another configuration that holds your requests.

Answer 4

About the problem, I think you should use the message queue (Gearman, Rabbit ...) to fix this problem.

Extract all entries and click on the queue. The queue will be processed in turn