After updating OJS to the latest version (3.3.0.6 from 3.2.x) I have a big performance issues with mysql.The CPU occupation by mysqld is very high and the slow query logs report this query:
SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(p.date_published) AS s_pub, COUNT(*) AS count FROM submissions s JOIN publications p ON (p.publication_id = s.current_publication_id) JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=’?’ AND ps.locale=’?’) JOIN issues i ON (CAST(i.issue_id AS CHAR(?)) = ps.setting_value AND i.journal_id = s.context_id) JOIN submission_search_objects o ON (s.submission_id = o.submission_id) NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0 WHERE s.status = ? AND i.published = ? AND k0.keyword_text = ‘?’ AND i.journal_id = ? GROUP BY o.submission_id ORDER BY count DESC LIMIT ?
Before upgrade to 3.3 I had to run this command on DB
ALTER TABLE submission_files ENGINE = InnoDB
due to an error in the upgrade procedure.
Please consider that our journal started 10 years ago and has had 19 upgrade in the last 10 years, but I have never had such a serious problem.
with no success (they already exist).
According to what reported by top mysql consumes almost 400% of the CPU (the server is a 4core/8Gb).
I think the performance degradation occurred after I have changed the engine of submission_files table from myIsam to InnoDB.
Maybe I have to change all database tables (or some tables) to myIsam to InnoDB to fix the mysql performance Issue? any suggest?
In order for OJS 3.3 to work properly at all, you need to be using InnoDB everywhere. OJS 3.3 is the first version of OJS to make use of foreign keys and MyISAM tables don’t support those. I’m actually a bit surprised that you were able to install 3.3 at all, using MyISAM. There are steps in the upgrade that create these via the REFERENCES privilege and those would have failed.
In any case, how big is this installation? At PKPPS, we have servers with a hundred or so installations on them, some running 3.3, and generally do not have performance problems. These servers have a half dozen cores and upwards of 32Gb of ram.
I was able to upgrade my OJS installation to 3.3.0.6 by only changing the submission_files table engine to innoDB.
We have about 1800 submissions, The journal is hosted on a Hetzner Cloud on Ubuntu 20.04 with 4vCPU and 8Gb Ram and until OJS 3.2.x (on a less powered server with ubuntu 16.04) I have no performance problem. Please consider that we started with OJS 2.2.0.0 and the database went through 19 updates in thirteen years.
So the advice is to change all tables to innoDB?
Dear @jnugent,
thank you for your suggestion. Turning all the tables into innodb brought the cpu usage back to normal levels. But I wonder if the upgrade script has “forgotten” other operations on the db as well. Is there any way to check?
If the upgrade completed successfully with your conversion to the submission_files table you are probably fine. I suspect the performance issue you were encountering was due to the fact that MyISAM tables do not support row level locking and updating that table requires a full table lock which can cause performance bottlenecks because while it is locked it can’t be used for anything, including general SELECT queries.
With InnoDB you’ll probably notice general improvements and stability because the sessions table is often a crash culprit with MyISAM since it is updated on every page request.
Dear @jnugent and forum users,
I changed all tables in innodb, and the situation goes better but I have still problems.
In the review submission stage have big problems. If I try to add a reviewer, the search among users is very slow, and often the files to attach to the review are completely absent.
In the attached image you can see other strange things, like the json encoded (I suppose) data. I tried a clean ojs 3.3.0.7 installation, and then export only data (no structure) from my database and insert into the clean ojs database, but I have this error: #1452 - Cannot add or update a child row: a foreign key constraint fails (ojs_copy.publication_galleys, CONSTRAINT publication_galleys_submission_file_id_foreign FOREIGN KEY (submission_file_id) REFERENCES submission_files (submission_file_id))
Any suggestion?
