To Prestashop: Did you ever try to Upgrade a LARGE Database?

[xertion]

We have a large Prestashop Database where the .sql file is about 300 MB, where we have millions of database entries.

 

Example of our database:

• 30,000 ps_address

• 105,000 ps_cart_product

• 300,000 ps_connections

• 25,000 ps_customer

• 270,000 ps_guest

• 1,500,000 ps_pagenotfound

• 350,000 ps_search_index

etc.

 

I have tried the 1-Click Auto Upgrade 4 times and I've tried Manual Upgrading in a Local Setting aswell. Each time the Upgrader is having large issues with Updating the Database from 1.4.8.2 to 1.5.4.1.

 

We have now set memory limit to 2 GB, We've set Max Times to 300 and 600 Seconds, Tried 2 Different Server Environments, without any results.

 

When using the Auto Upgrader the Auto Upgrader runs through all the files fine, but when updating the Database it takes an incredible long time and then we get the following error: "Internal Server Error " jqXHR. Now please correct me if I'm wrong, but the reason for this is because of some kind of Time Out or Fatal Error occuring during Prestashop trying to update the Database.

 

We have checked both apache2 error.log and mysql error.log to find what issues are causing these errors. The first time we found out that it ran out of memory (And therefore increased from Prestashop recommended 128MB to 2 GB) and the next time we found out that mysql gave an error about the Log Size.

 

After fixing these issues we have not found any more Fatal errors in the error.logs.

 

When I instead try the Manual Update and run /install/upgrade/upgrade.php it just loads, loads and loads without returning any error really. When I check settings.inc.php file after running the manual upgrade, I can see that Prestashop changed the _PS_VERSION_ to 1.5.4.1 from 1.4.8.2 so the Upgrade File _IS_ doing some things.

 

My personal guess is that your Upgrade Tool is NOT efficient for large databases. I would love to get a solution on this, so please either correct my faults or help us be able to upgrade a "real business" website instead of only being able to upgrade websites with sub 1000 customers.

Edited July 3, 2013 by xertion (see edit history)

[bellini13]

The issues you encounter appear to be resource limitations on the host, and not necessarily an issue with Prestashop. By your own admittance, you have a large store, and it should not come as a surprise that to deal with a large amount of data, would take both memory and time.

 

I can suggest that you purge the contents of the 3 connection tables, and perhaps the page view table. That will significantly reduce the size of the database. These tables are used mainly in the stats area for visitor statistics, and you are much better off using a tool like google analytics for this. Also, you should probably question why you have 1.5 million page not found records, and if you "need" to keep them.

 

I would steer clear of the autoupgrade and focus just on a manual upgrade. I also hope you are doing this on a test copy of the store, and not directly to the live store.