[OJS 2.4.8-2] freezes when selecting Reviewers

Hey @rensousa

How were the spam users removed? It may be possible that there are some broken references to users now and that’s causing a problem.

To see if it’s a real issue without looking in the logs, try looking in your browser console and for HTTP requests that result in a 500 status code. From there you can dig further.

Cheers,
Jason

Hello @jnugent,

I also suspected broken references, but users are removed using the mergeUsers.php via CLI.
I searched the apache logs but did not find HTTP requests returning code 500. All requests return 200.
Is it possible to investigate directly in the DB?

Regards,

Renato

Hey @jnugent

Analyzing the URL of the reviewer’s selection, I realized that there is a code and I executed a select in the DB to verify if there is any user with this id and so far all the codes are equivalent to valid users.

I already tried to clear data cache, template cache and schedule tasks but it did not work.

Cheers,

Renato

Hello everyone,

Today I noticed that the reviewers selection page is loaded, but it takes too long. It takes about 10 minutes to display, which harms the work a lot.

I checked the apache error log and the mysql error log but I did not find anything relevant.

Somehow cleaning up spam users (there were 90k and now it’s 4k) might have caused this problem?

How can I solve this?

Regards,

Renato

Hey Renato

With so many records removed, it might be worth running a few analyze table commands on the database, within MySQL. There may be some indexes that need repairing, and I could easily see a broken index making some of those queries slow.
Cheers,
Jason

Hey James,

I checked the mysql error logs as well as the table status, but I did not notice any problems.
I checked a mysql dump I had and I did not find any CREATE INDEX statements, which indicates that OJS 2.4 tables do not use indexes.
The server is ok for memory consumption and CPU load.

What could be causing this extreme slowness? Why does this happen only in the selection of reviewers?

I have no ideas for diagnostics :frowning:

Regards,

Renato

Hi Rensousa,

Whoa, yes there are indexes on OJS 2.4 tables, i.e.:

mysql> mysql> show index from review_assignments;
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name                         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| review_assignments |          0 | PRIMARY                          |            1 | review_id      | A         |        4519 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_submission_id |            1 | submission_id  | A         |         753 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_reviewer_id   |            1 | reviewer_id    | A         |        2260 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_form_id       |            1 | review_form_id | A         |          31 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

and:

mysql> show index from roles;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| roles |          0 | roles_pkey       |            1 | journal_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| roles |          0 | roles_pkey       |            2 | user_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| roles |          0 | roles_pkey       |            3 | role_id     | A         |       19194 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_journal_id |            1 | journal_id  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_user_id    |            1 | user_id     | A         |        9597 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_role_id    |            1 | role_id     | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

So, if you’re not seeing indexes it might be worth creating an empty OJS database with just the structure and no content, and then doing a data only import from your main database to see if you can build the indexes again.

Cheers

Hello Jason,
Sorry for the wrong info that the tables had no indexes. I searched the CREATE INDEX statement in the dump file and as I did not find it I thought there were no indices.
See the return of the commands below:

mysql> show index from review_assignments;
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name                         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| review_assignments |          0 | PRIMARY                          |            1 | review_id      | A         |        6453 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_submission_id |            1 | submission_id  | A         |        1613 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_reviewer_id   |            1 | reviewer_id    | A         |        3226 |     NULL | NULL   |      | BTREE      |         |               |
| review_assignments |          1 | review_assignments_form_id       |            1 | review_form_id | A         |        6453 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> show index from roles;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| roles |          0 | roles_pkey       |            1 | journal_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| roles |          0 | roles_pkey       |            2 | user_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| roles |          0 | roles_pkey       |            3 | role_id     | A         |        6646 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_journal_id |            1 | journal_id  | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_user_id    |            1 | user_id     | A         |        3323 |     NULL | NULL   |      | BTREE      |         |               |
| roles |          1 | roles_role_id    |            1 | role_id     | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

I think then the delay is not some problem with the indexes, right?

Any other ideas? I’ve already exhausted my attempts to find the problem :sob::sob::sob:

Thank you very much for your help!

Regards,

Renato

hey Renato,

You might want to turn “debug = On” in your configuration file, temporarily, or on a test install, in order to see the database queries being run. Then you can copy those queries and run a mysql explain on them. So, if the query is: “select * from users” you can run “explain select * from users” And MySQL will tell you how it is performing the query, what indexes it is using, the cardinality, etc. That might give you something to go on. Maybe there’s a large join that is causing problems.

Cheers,
Jason

Hello @jnugent

Thanks for your tip. Through the “debug = ON” option for database queries I discovered who is to blame for this delay in loading the page.
There are 2 SQL clauses that together consume the page response time.

SQL 1
mysql> SELECT DISTINCT u.user_id, u.last_name, ar.review_id, (SELECT AVG(ra.quality) FROM review_assignments ra WHERE ra.reviewer_id = u.user_id) AS average_quality, (SELECT COUNT(ac.review_id) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS completed, (SELECT COUNT(ac.review_id) FROM review_assignments ac, articles a WHERE ac.reviewer_id = u.user_id AND ac.submission_id = a.article_id AND ac.date_notified IS NOT NULL AND ac.date_completed IS NULL AND ac.cancelled = 0 AND ac.declined = 0 AND a.status <> 1) AS incomplete, (SELECT MAX(ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) AS latest, (SELECT AVG(ac.date_completed-ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 2483 AND ar.round = 1) LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) WHERE u.user_id = r.user_id AND r.journal_id = 2 AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ;

SQL 2
mysql> SELECT DISTINCT u.user_id, u.last_name, ar.review_id, (SELECT AVG(ra.quality) FROM review_assignments ra WHERE ra.reviewer_id = u.user_id) AS average_quality, (SELECT COUNT(ac.review_id) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS completed, (SELECT COUNT(ac.review_id) FROM review_assignments ac, articles a WHERE ac.reviewer_id = u.user_id AND ac.submission_id = a.article_id AND ac.date_notified IS NOT NULL AND ac.date_completed IS NULL AND ac.cancelled = 0 AND ac.declined = 0 AND a.status <> 1) AS incomplete, (SELECT MAX(ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) AS latest, (SELECT AVG(ac.date_completed-ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 2483 AND ar.round = 1) LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) WHERE u.user_id = r.user_id AND r.journal_id = 2 AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,25;

Running the SQL together with the explain command returns the same information for the two clauses because they are the same, differing only in the ORDER BY statement:

+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys                                                   | key                            | key_len | ref                     | rows | Extra                                        |
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | r     | ref    | roles_pkey,roles_journal_id,roles_user_id,roles_role_id         | roles_role_id                  | 8       | const                   | 3394 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | u     | eq_ref | PRIMARY                                                         | PRIMARY                        | 8       | RVQOJS.r.user_id        |    1 |                                              |
|  1 | PRIMARY            | ra    | ref    | review_assignments_reviewer_id                                  | review_assignments_reviewer_id | 8       | RVQOJS.r.user_id        |    2 |                                              |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY                                                         | PRIMARY                        | 8       | RVQOJS.ra.submission_id |    1 | Using index                                  |
|  1 | PRIMARY            | ar    | ref    | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8       | RVQOJS.u.user_id        |    2 |                                              |
|  6 | DEPENDENT SUBQUERY | ac    | ref    | review_assignments_reviewer_id                                  | review_assignments_reviewer_id | 8       | func                    |    2 | Using where                                  |
|  5 | DEPENDENT SUBQUERY | ac    | ref    | review_assignments_reviewer_id                                  | review_assignments_reviewer_id | 8       | func                    |    2 | Using where                                  |
|  4 | DEPENDENT SUBQUERY | ac    | ref    | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8       | func                    |    2 | Using where                                  |
|  4 | DEPENDENT SUBQUERY | a     | eq_ref | PRIMARY                                                         | PRIMARY                        | 8       | RVQOJS.ac.submission_id |    1 | Using where                                  |
|  3 | DEPENDENT SUBQUERY | ac    | ref    | review_assignments_reviewer_id                                  | review_assignments_reviewer_id | 8       | func                    |    2 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | ra    | ref    | review_assignments_reviewer_id                                  | review_assignments_reviewer_id | 8       | func                    |    2 |                                              |
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+

Why do these clauses take so long to respond?
Can you see any problems?
How can I solve this? :thinking::thinking::thinking:

Regards,

Renato

Hi @rensousa

Apologies for my late reply - I was away for a few days. To be honest, I am not entirely sure why those queries are slow for you, because the keys look good.

How many users and reviewers are still enrolled in your journal? Does that 3394 count for rows in the roles table seem reasonable?

Cheers,
Jason

Hi Jason,

No problems. Your answers have helped me a lot in finding a solution to the problem.

I suspect there are some problems with the tables as they have been mismanaged for a long time, with no security or control concerns to prevent spammer accounts.

I noticed that all OJS tables (in my database are 170 :thinking::thinking::thinking:) have storage engine like MyISAM instead of InnoDB.

In the tests I did, changing the engine decreased response time from 2 min 32 sec to 34 sec !!!

See the number of table records involved in these SQL statements:

  • review_assignments: 6469
  • users: 5569
  • roles: 7502
  • articles: 2879

I’m sure there are a lot of unnecessary records, but removing them without affecting real records is a huge challenge.

Regards,

Renato L. Sousa

Hello everyone

Today the system is in version 2.4.8-5. I just measured the time of this operation (select reviewers in the article) and the delay is 3min 52sec.

Does your system take all this?
What can I do to reduce this response time?

Regards,

Renato

Hi @rensousa

Is this still a database that is MyISAM tables, instead of Innodb?

Cheers,
Jason

Hi @jnugent,

No, all tables currently use the InnoDB engine, which has reduced the time considerably.
However, there is still a high response time that hinders the use of users.

Regards,

Renato

Hi Renousa,

Okay, originally in a few posts above you said:

But now you said:

So, did the time for these queries jump back up to nearly four minutes, just because of the upgrade to 2.4.8-5? Or is it still about 30 seconds?

30 seconds for a 2.4.8 install with a lot of reviewers might just be the way it is, especially since the query basically runs twice. One of the ways we’ve sped up database queries with the PKP Hosting we offer is to use SSD drives instead of older hard drives. That helps a lot of things (including the time it takes to run large upgrades).

Cheers,
Jason

Hi @jnugent,

Sorry for the confusion, I actually expressed myself wrong.
When said:

… decreased the response time from 2 min 32 s to 34 s !!!

I referred to the execution time of a complex SQL statement and not the system response.
Prior to the mysql engine change, the system response time exceeded 7 minutes. :scream:

At this time the most time consuming SQL statement executes at 43 sec and the total OJS response time is 1 min 40 sec.

I believe there is a lot of unnecessary information in the tables created by spammers because the system has been without SPAM control for a long time.

How can I check db for mismatched information?
Could it be this inconsistency that causes this slowness?

Regards,

Renato

Hi @rensousa

If you think you have lots of possible spam registrations, maybe look for users who have created no reviews (i.e. they are reviewers but have no review assignments) or have not logged into the system since their account was created (date_registered and date_last_login are the same). If you build a list of user ids you can remove those in batches with a command line script and just merge those users into an admin account.

Cheers,
Jason

Great idea @jnugent,

I just need to commit the SQL statements to not select records incorrectly:

  • Reviewers without review assignment:
    select username, first_name, last_name, email from users where user_id not in (select reviewer_id from review_assignments);

  • Users who have not logged in since the account was created:
    select username, first_name, last_name, email, date_registered from users where date_registered = date_last_login;

Are the SQL statements correct, @asmecher ?
Particularly I have doubts about the first sentence. Does the reviewer_id field contain the userid (user_id)?

Regards,

Renato

Hi Renato,

That first query isn’t quite right. It will return users without review assignments, yes, but you’d also want to make sure that those users were actually reviewers, which means a join across the roles table. The query might look something like this:

SELECT DISTINCT users.user_id FROM users, `roles`, review_assignments where users.user_id = roles.user_id AND role_id = 4096 and users.user_id NOT IN (SELECT distinct reviewer_id from review_assignments)

I did some quick sanity tests on a local 2.4.8 install I have here and the user ids I get back are reviewers with no assignments. ~1200 reviewers out of ~5000 in this installation.

Cheers,
Jason