cancel
Showing results for 
Search instead for 
Did you mean: 

Dashboard - Export orders as CSV - Crashing with large order table

SOLVED

Dashboard - Export orders as CSV - Crashing with large order table

I'm having an issue with my orders table - It's quite large - circa 250k orders

 

In the dashboard, when I select (for example) the top 5 orders, and hit export (see attached screenshot) - the whole thing hangs... MySQL is obviously overloaded, and eventually, the app eventually crashes.

 

This happens on my hosting (dedicated box, 16gb ram) and local machine / test environment....

 

Is this normal? Anyone have experience of having a large order table like this and this happening?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Dashboard - Export orders as CSV - Crashing with large order table

The problem comes from the way how data is loaded by Magento for CSV export. Because order collection does a query on the whole data set to retrieve data for CSV, and then it sorts out the results. If you don't have any filter applied to it, any MySQL server might crash, because of amount of data it has to insert into temprorary table for performing a sorting operation. 

 

 

You have only few options:

1. Increase buffer pool size

2. Rewrite collection export mechanism to fetch data in batches of 2000-3000 items. (this is more preferable, you don't need to get more RAM when you get bigger dataset.)

View solution in original post

3 REPLIES 3

Re: Dashboard - Export orders as CSV - Crashing with large order table

This isnt normal. What is your memory limit set at? Not the one in the .htaccess but if you do a <?php phpinfo(); ?> what is the server level at?

-Kris
4x Certified, Blogger @ xgento.com

Re: Dashboard - Export orders as CSV - Crashing with large order table

It's set to 4096M

 

The actual app is fine (i think)

 

The error that comes back (eventually) is 'mysql has gone away' - it's like the db gives up.

 

Re: Dashboard - Export orders as CSV - Crashing with large order table

The problem comes from the way how data is loaded by Magento for CSV export. Because order collection does a query on the whole data set to retrieve data for CSV, and then it sorts out the results. If you don't have any filter applied to it, any MySQL server might crash, because of amount of data it has to insert into temprorary table for performing a sorting operation. 

 

 

You have only few options:

1. Increase buffer pool size

2. Rewrite collection export mechanism to fetch data in batches of 2000-3000 items. (this is more preferable, you don't need to get more RAM when you get bigger dataset.)