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

[alexjamesbrown]

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?

[kab8609]

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?

[alexjamesbrown]

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.

[IvanChepurnyi · Accepted solution]

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.)