Long duration of upgrade process (OJS 2.4.3 to 3.1.1-4)

Software Support
1

Hi, upgrading our very large installation of OJS (523 journals, 168000 articles, 220000 users) and expecting things to take a while, but would be great to be able to speed up the process. Currently taking 66 hrs (without metrics table).

There are specifically 3 steps that take really long:

  • 20 hrs: “[code: Installer Installer::migrateArticleMetadata]” (the 20hrs is from start of upgrade process, but this item uses most of that time)

  • 18 hrs: "UPDATE submission_files SET file_stage=10 WHERE file_id IN (SELECT file_id FROM submission_galleys) "

  • 7 hrs : “UPDATE submission_files SET file_stage=17 WHERE file_id IN (SELECT style_file_id FROM article_galleys_migration)”

Reducing the time it takes on any of these 3 steps would be a great help.

2

tagging @asmecher and @bozana here.

3

Hi @makouvlei,

I can see some easy wins here just at a glance – but I don’t have an equivalently large installation handy for testing. Would you be willing to share the database?

Regards,
Alec Smecher
Public Knowledge Project Team

4

Hi Alec, thanks for your reply, I’ve been on leave but back at work now. I’ll email you a link to download the database.

Regards
Jannie van Tonder
AJOL

5

Hi Alec, just wondering if you managed to look at this?

Regards
Jannie van Tonder
AJOL

1 Like
6

@makouvlei we are facing the same problem, not taking too long as yours but some hours too…

|947px;x168px;
|947px;x168px;947×168

regards

7

Hi Ronnie, as per screenshot, this unfortunately doesn’t appear to be a priority for PKP.
image

Regards
Jannie

1 Like
8

I was wondering if an update using a join, like

update submission_files sf, submission_galleys sg set file_stage=10 where sg.file_id = sf.file_id

wouldn’t have the same effect as

update submission_files set file_stage=10 where file_id in (select file_id from submission_galleys)

but being much faster

9

Hi @ronniebrito,

Would you mind opening a PR for this change? It would need to be PostgreSQL-compatible as well, but if you’re not sure how to go about that, I can give some guidance.

Thanks,
Alec Smecher
Public Knowledge Project Team

10

@asmecher,

I think it is postgresql compatible, as it is a very simple query… but I’m not 100% sure

so yeap, I need some advice :slight_smile:

11

Hi @ronniebrito,

It is a little different, I’m afraid… you can see an example of the two syntaxes in dbscripts/xml/upgrade/3.0.0_update.xml:

<query driver="mysqli">UPDATE submission_files sf, articles_migration am SET sf.file_stage=7 WHERE am.editor_file_id=sf.file_id</query><!-- SUBMISSION_FILE_FAIR_COPY -->
<query driver="postgres7">UPDATE submission_files SET file_stage=7 FROM articles_migration am WHERE am.editor_file_id=submission_files.file_id</query><!-- SUBMISSION_FILE_FAIR_COPY -->

Those two are equivalent, but rewritten for the two different types of databases.

Thanks,
Alec Smecher
Public Knowledge Project Team

12

dear @asmecher,

the PR is at PR created to optimize upgrade time by ronniebrito · Pull Request #2453 · pkp/ojs · GitHub
there are some changes in translation files t(dont know why and did know how to remove them)

regards

1 Like
13

Hi @ronniebrito,

Thanks for contributing! I’ve added some feedback on the pull request.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
14

Hi @ronniebrito, thank you and well spotted, I’ve implemented the change on our local large installation and it’s made a big difference to the upgrade time :slight_smile:

1 Like