Hello everyone,
I have a complicated problem with ojs-2.4.8-5.
When admin tries to select a reviewer for an article (link http://my-site.com/index.php/site/editor/selectReviewer/[article_code]) the system takes about 2 minutes to display the reviewers.
I tried several settings on the tables, but I can’t reduce this response time.
I’ve changed all the tables to innodb, which reduced the response from 5 minutes to 2 minutes.
What can I do to improve this response time?
Regards
Renato L. Sousa
Hi @rensousa,
Can you describe the clauses you identified that are slowing things down?
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @asmecher,
Yes, I have identified the following clauses that consume together approximately 1min 30 sec:
Clause SQL #1
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 = 2507 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
If executed directly in mysql CLI returns:
2455 rows in set (43.01 sec)
Clause SQL #2
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 = 2507 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
mysql CLI:
25 rows in set (43.18 sec)
The link that causes this delay is:
http://[myportal]/index.php/[mysite]/editor/selectReviewer/2507
What can I do to improve this?
Regards,
Renato
Hi @rensousa,
That’s the same query twice, once one with a LIMIT clause and once without. It’s an entire queries, not just a clause or two; I was hoping you might have done some inspection of the pieces of the query to see if you could identify the bottleneck. A heads-up that I probably won’t be able to dedicate a lot of time to it, as we’re focusing our time as much as possible on OJS 3.x – but can you run an EXPLAIN on the query above (without the LIMIT clause)?
Regards,
Alec Smecher
Public Knowledge Project Team
Hi @asmecher,
I had already run the explain command, but I confess that I didn’t quite understand the return.
Can you identify any bottlenecks here?
Should I execute each of the subqueries to identify which one consumes the most time?
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+---------------------------+------+-----------------------------------------------------------+
| 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 | 2455 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 8 | ojs2_rvq.r.user_id | 1 | |
| 1 | PRIMARY | ra | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | ojs2_rvq.r.user_id | 1 | |
| 1 | PRIMARY | ar | ref | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | ojs2_rvq.u.user_id | 1 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | ojs2_rvq.ra.submission_id | 1 | Using index |
| 6 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | ac | ref | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 8 | ojs2_rvq.ac.submission_id | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | ra | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 1 | |
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+---------------------------+------+-----------------------------------------------------------+Hi @asmecher,
I tried to split SQL clauses to know which one consumes most of the time, but the LEFT JOIN directives confuse me to do this analysis. I get error executing only sub queries.
But I analyzed the number of records in each of the tables involved in this query:
Are these values significant for a diagnostics?
How can I get a more accurate diagnosis to know what is going on?
Regards,
Renato
Hi @rensousa,
It looks like this is a re-post of [OJS 2.4.8-2] freezes when selecting Reviewers - #13 by rensousa, correct? Please don’t post the same issue multiple times; it clutters the forum and could cause repeat work. If you think your post has been forgotten, feel free to bump the old thread.
Thanks,
Alec Smecher
Public Knowledge Project Team
I’m sorry for that @asmecher ! I hadn’t really thought about how this could cause problems. 
I will reuse the old thread.
Regards,
Renato