Cakephp 3 - Import Large CSV to DB using file upload Error 503 Service Unavailable

Question

I will be putting everything in bullets to not make the question lengthy..

I have 800,000++ rows of CSV
Uploads it thru cakephp 3 $this->Form->upload() WORKING
Save the file in webroot for now WORKING
Open the file using box/spout WORKING
Loop thru each row and save each row to the database PARTIALLY WORKING

So everything seems to be working fine except after about 5 mins and ~200,000 records saved in the database it returns an error 503 service unavailable. I am able to save 800,000 rows on my localhost, but this error appears on the live site which is hosted on GoDaddy.

Are there any settings I can change to prevent this error from happening? Maybe increase a timeout for this specific error? (I have set_time_limit(0) and ini_set('memory_limit','-1') just to make it work. I have even set max_execution_time to a bigger number in the servers php.ini.

Not really sure what solution I can do to fix this. Any suggestion would be appreciated thanks!

Try using an optimized approach for inserting the entries into the database. Use batch insertion instead if you're inserting one by one(for better time efficiency), and you can optimize the memory by reading and processing the data in chunks. This would definitely reduce the load on the server. <stackoverflow.com/questions/63057309/…> — Aashish gaba
– Aashish gaba, Commented Jul 23, 2020 at 19:44

Kaiser Ahmed Tushar · Accepted Answer · 2020-07-24 18:22:04Z

0

If this is a one time job, then you can split the CSV data in multiple CSV. Like you can set 100,000 rows per CSV and following this you need to upload 8 to 10 CSV to save all record in live Database.

If it is dynamic from user end, you don't know how much rows that CSV will contain, you should limit the number of rows.

Or you can save the CSV file in the server (maybe in webroot) and tell user to wait and can save CSV records to Database through Cake Shell. Cake shell does not run in HTTP protocol, you don't have any execution time limit if you use set_time_limit(0) and ini_set('memory_limit','-1')

Cake shell is same like we write code in controller and model. Details: https://book.cakephp.org/3/en/console-and-shells/shells.html

We use cake shell for larger task like creating reports for 200000 users in one shot.

answered Jul 24, 2020 at 18:22

Kaiser Ahmed Tushar

461 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cakephp 3 - Import Large CSV to DB using file upload Error 503 Service Unavailable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related