11

Last days I am testing and doing benchmarks in order to find out how long it takes to upgrade Magento DB when we upgrade between major Magento versions.

I am testing kind of large DB. This DB contains only vital data and all unnecessary tables are truncated (log tables, import batches and etc.)

Where is the bottle neck?

The catalog data is not large but sales history tables are gigantic. I did couple of dry runs and noticed that adding / dropping indexes, adding / removing columns take ages. Basically I observed what MySql was doing … it was creating temporary tables like crazy (this obviously increased time for upgrade)

Plan of attack:

I see a possibility how to save some time but it needs preparation.

  1. Use DB version in which large sales tables are empty.
  2. Run Magento upgrade.
  3. Import the data in sales tables.
  4. Run some data-upgrade scripts again because new column were created during upgrade and I have to fill them with values.

My hope is that with step (3) I could reduce time which was wasted of creating temporary table every time when indexes was added / dropped and column was added / dropped.

What the community thinks about this approach? Do you see any risk for data inconsistency when the shop goes live after upgrade?

5
  • I'm migrating this to meta as there isn't one true answer for this, but it's a very interesting discussion.
    – philwinkle Mod
    Commented Sep 23, 2015 at 16:15
  • 3
    I doubt that the question is hard to find here for normal users, therefore I think it might be better at magento.se I neither think it is off topic there nor wrong...
    – Fabian Blechschmidt
    Commented Sep 24, 2015 at 10:16
  • How big is your database? (with all the data)
    – brentwpeterson
    Commented Sep 25, 2015 at 15:41
  • 1
    @FabianBlechschmidt "what does the community think of this approach" is not a good fit for the regular site. It solicits opinion, not fact.
    – philwinkle Mod
    Commented Sep 25, 2015 at 16:54
  • Its not only interesting but also very important so it should be remain in magento.
    – Jarnail S
    Commented Oct 6, 2015 at 13:31

2 Answers 2

Reset to default
11

I think you'd find this to be a very interesting approach to what you're trying to do:

https://github.com/magento-ecg/Magento-Upgrade-Replay

ECG (Magento Expert Consulting Group) created this module to aggregate all db changes to then replay in bulk. This is really helpful for certain upgrade scenarios such as 1.10EE to 1.12EE which had a tremendous number of changes for the sales tables. If you had a very very large sales_flat_order table you would have found that there were dozens of small db migration scripts that added one column at a time.

If you have a very large table it copies the contents of the table into a table with the new schema and swaps. This is very inefficient to do dozens and dozens of times over and over; especially when all of the statements could be aggregated and take place all at once.

Hope that helps! Best of luck!

1

Try it with other hardware. I know you asked not directly for this. But maybe it is faster than your research.

Updating from 1.4 to 1.7 took 18 hours with our shop on a dedicated server with SAS disks (RAID 10). Also after emptying all tables not needed (logs etc.).

Running the update on a local dedicated server with a RAID 0 and SSD reduced time to 5 hours.

Today I would run it on upcloud.com*. Their server are faster than our local SSD system.

*I'm not sponsored or related to them :)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .