Upgrade from OJS 3.0.1-1 to 3.1.1 takes a long time (1hr+)

Software Support
21

Hi @ajnyga

Ah, I think I am getting crazy from those migrations… :crazy_face: – It is so difficult to keep track and figure out when what has to be fixed and how and… :crazy_face: THANKS A LOT for thinking with me! :slight_smile:

So, I think: for the upgrade 3.1.1 I think that the lines https://github.com/pkp/ojs/blob/master/dbscripts/xml/upgrade/3.1.0_update.xml#L89-L90 would be necessary only if one is upgrading from an older OJS 3.0.x, because for those coming from OJS 2 this is already set correctly here https://github.com/pkp/ojs/blob/master/dbscripts/xml/upgrade/3.0.0_postupgrade_metrics.xml#L19.
That line was necessary earlier, when the 3.0.0_postupgrade_metrics.xml was wrong too.
But, however, I think this two SQLs do not last long, or?

And else, regarding your suggestion for having 3 smaller SQLs: I am not sure, I would need to try… The logic seems to be correct.
Would it be possible for you to test on your big data how long these 3 queries take?

Or maybe, after these lines https://github.com/pkp/ojs/blob/master/dbscripts/xml/upgrade/3.1.0_update.xml#L89-L90 are run, would it be possible to see how long these queries take:
SELECT m.assoc_id FROM metrics m, submission_file_settings sfs WHERE m.assoc_type = 531 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id
comparing to the:
UPDATE metrics m, submission_file_settings sfs SET m.assoc_id = sfs.file_id WHERE m.assoc_type = 531 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id
i.e. if maybe this huge/complicated UPDATE is making problems… ?
Hmmm…
Or would it be possible for you to give me those 3-4 DB tables (metrics, submission_file_settings, submission_supplementary_files and submission_artwork_files) from you so that I can test it?

:thinking::crazy_face:

22

I will first see if the current upgrade script ever finishes. The query has now run for 72 000 seconds. Let’s see if it is finished by tomorrow morning :joy: But that would basically mean that the upgrade runs over 30 hours and I think that is just too much even with large installations.

After that I can test the queries you did with the data I have.

23

@bozana, it took between 22 to 28 hours to run that query (not sure when it finished because I was sleeping).

So the full upgrade took something like 30-32 hours.

After thinking about this, I think that I will do the actual production upgrade with the current script. It takes long, but works. I can test the queries you had anyway to get you the numbers in case some even larger installation needs other solutions.

Thanks!

24

Omg… :hushed:
Hmmm… :thinking: