Database tables to skip when doing dumps and inserts

[mirkh]

Describe the issue or problem
We have a production server and a test server. When we upgrade to a new version of OJS we first upgrade the test server. Then we first make a dump of the production database, and insert it to the test server. The dump doesn’t take too long, but the insert of the dump takes 2-3 days, even if I turn off foreign key checks and similar.

We have quite a large installation with many journals, and it’s more than 15 years old. The following tables are very large:

120M sessions.ibd
152M event_log_settings.ibd
528M submission_search_keyword_list.ibd
636M comments.ibd
7.7G metrics.ibd
8.7G submission_search_object_keywords.ibd

Do you have any suggestions how to insert dumps a bit faster without affecting the site’s content?

My idea is to dump some of these tables separately and create them as empty tables to start with, and then insert their data after the upgrade has been finished. But of course that does not work if the table format changes.

Which of these tables are suitable to keep separate, and are there any that I can just discard the content of?

What application are you using?
We are at the moment on OJS 3.3.0.15 and planning to upgrade to OJS 3.5.some-version.

[asmecher]

Hi @mirkh,

I’ll comment on a few of these:

• sessions: If you remove the contents of this table, it’ll effectively force all users to have to log in again. There would be no other repercussions.
• event_log_settings and metrics: I don’t recommend omitting these. Upgrade scripts might interact with them, so your test upgrade might not behave the same way as a production upgrade.
• submission_search_keyword_list and submission_search_object_keywords: This is part of the full-text search engine – along with submission_search_objects, which wasn’t on your list. You can skip the contents of all 3, and re-generate them if you want using tools/rebuildSearchIndex.php.
• comments: I suspect this is the public comments from OJS 2.x, and more than likely just contains spam comments. OJS 3.3.0-x doesn’t use this table anymore, so you should be able to drop it. (Please take a backup first, though.)

Regards,
Alec Smecher
Public Knowledge Project Team

[mirkh]

Thank you Alec,
This is very helpful.
Kind regards, Maria