OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



Select Reviewer Performance Issue

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

Select Reviewer Performance Issue

Postby marct » Wed Feb 09, 2011 9:34 am

Greetings,
We are seeing significant performance problems in the ‘Select Reviewers’ function, which is in the getReviewersForArticle() method within classes/submission/sectionEditor/SectionEditorSubmissionDAO.inic.php. We have just under 100 reviewers and when the editors click the link, the app hangs for > 3 minutes while it performs the complex query with numerous joins.

This issue has been mentioned before: viewtopic.php?f=9&t=6750. We are running the latest version 2.3.4, which should have all the patches applied to address this issue. We compared our DB to a clean install DB and we have all the correct table indexes.

Does anyone have any suggestions or hacks to speed up this query?

Thanks
marct
 
Posts: 4
Joined: Tue Feb 08, 2011 11:04 am

Re: Select Reviewer Performance Issue

Postby marct » Fri Feb 25, 2011 6:45 am

To follow up on this: We have tried several hacks to eliminate all of the accessory information brought in by the joins but are not familiar enough with the underlying objects and templates and keep getting errors. Does anyone have examples of optimizing this code or specifically this query?

Are there any extra database indexes we could add? We think we have all of the "out of the box" indexes already.

Thank you.
marct
 
Posts: 4
Joined: Tue Feb 08, 2011 11:04 am

Re: Select Reviewer Performance Issue

Postby asmecher » Thu Mar 03, 2011 7:35 pm

Hi marct,

Are you using review forms? I recently did some optimization work around that -- see http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=6439. Try that and if it doesn't help I can look into it further.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 9099
Joined: Wed Aug 10, 2005 12:56 pm

Re: Select Reviewer Performance Issue

Postby marct » Fri Mar 04, 2011 1:23 pm

Thanks very much for the suggestion - we applied the patch but it did not solve the issue.

As best we can tell, the query that is the issue is on line 780 of lasses/submission/sectionEditor/SectionEditorSubmissionDAO.inc.php within the getReviewersForArticle() method. For some reason this method is taking several minutes to complete its query.
marct
 
Posts: 4
Joined: Tue Feb 08, 2011 11:04 am

Re: Select Reviewer Performance Issue

Postby asmecher » Fri Mar 04, 2011 2:00 pm

Hi marct,

Could you log the query (either by temporarily turning on "debug" in config.inc.php or by using MySQL's slow query log) and try running a DESCRIBE for it through MySQL?

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 9099
Joined: Wed Aug 10, 2005 12:56 pm

Re: Select Reviewer Performance Issue

Postby ramon » Thu Mar 24, 2011 2:45 pm

Hello all,
I'm not sure if this problem is related, so I'll post it here...

A university, running OJS with 19 journals hosted. They have recently upgraded from a pretty old 2.2 to 2.3.4.
We believe the patches were applied after upgrading the database.
They have sent us a dump of their database, which we installed on a fresh 2.3.4 and proceeded as the support from UFG told us, patched after installing.
Some patches did not apply cleanly, such as the "Signoffs fix" patch.
They must have made a few mistakes while upgrading, although they claim there were no database error messages while upgrading.
While they have some style issues, which may be caused by the differences in 2.3.4, they reported that after upgrade a specific journal is crashing the db while attempting to assign reviewers.

This query took over 5min to execute in our test server:
Code: Select all
(mysql): SELECT DISTINCT u.user_id, u.last_name, ar.review_id, AVG(ra.quality) AS average_quality, COUNT(ac.review_id) AS completed, COUNT(ai.review_id) AS incomplete, MAX(ac.date_notified) AS latest, AVG(ac.date_completed-ac.date_notified) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ac ON (ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) LEFT JOIN review_assignments ai ON (ai.reviewer_id = u.user_id AND ai.date_completed IS NULL) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 10216 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) LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = 4096 AND cv.assoc_id = u.user_id AND cv.symbolic = 'interest') LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id) LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id) WHERE u.user_id = r.user_id AND r.journal_id = '12' AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id


Then the following query is loaded, and the only difference is the limit 0,3000 as far as I can tell, and took over 5min as well.
Code: Select all
(mysql): SELECT DISTINCT u.user_id, u.last_name, ar.review_id, AVG(ra.quality) AS average_quality, COUNT(ac.review_id) AS completed, COUNT(ai.review_id) AS incomplete, MAX(ac.date_notified) AS latest, AVG(ac.date_completed-ac.date_notified) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ac ON (ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) LEFT JOIN review_assignments ai ON (ai.reviewer_id = u.user_id AND ai.date_completed IS NULL) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 10216 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) LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = 4096 AND cv.assoc_id = u.user_id AND cv.symbolic = 'interest') LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id) LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id) WHERE u.user_id = r.user_id AND r.journal_id = '12' AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,3000


Then, OJS stopped responding at this query:
Code: Select all
(mysql): UPDATE sessions SET user_id = 21157, ip_address = '192.168.0.254', user_agent = 'Mozilla/5.0 (Windows NT 5.1; rv:2.0) Gecko/20100101 Firefox/4.0', created = 1300999069, last_used = 1301002135, remember = 0, data = 'username|s:12:\"carlacercomp\";userId|i:21157;enrolmentReferrer|a:1:{i:0;s:7:\"editors\";}signedInAs|s:1:\"1\";', acting_as = 0 WHERE session_id = '3bseban0891q86j66att08vc07'


Any ideas? :)
PS: Thank you for all past and future support provided!!
ramon
 
Posts: 940
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil

Re: Select Reviewer Performance Issue

Postby ramon » Tue Mar 29, 2011 5:17 am

Hello all,

Anyone with ideas or has experienced such a thing?
If PKP members are willing, I can PM the DB dump.
ramon
 
Posts: 940
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil

Re: Select Reviewer Performance Issue

Postby asmecher » Wed Mar 30, 2011 11:03 am

Hi Ramón,

I've passed this along to Matt and he'll follow up soon. The problem is with the Reviewing Interests join.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 9099
Joined: Wed Aug 10, 2005 12:56 pm

Re: Select Reviewer Performance Issue

Postby mcrider » Wed Mar 30, 2011 3:52 pm

Hi Ramon,

Can you try applying this patch and letting us know if it speeds up the query for you?

Cheers,
Matt
mcrider
 
Posts: 952
Joined: Mon May 05, 2008 10:29 am
Location: Vancouver, BC

Re: Select Reviewer Performance Issue

Postby ramon » Thu Mar 31, 2011 5:43 am

Hello all,

The patch did work, but it brought all 123 reviewers available, although in config.inc.php items_per_page = 25.
Is this normal?

When disabling debug and stacktrace the query runs very fast.

I'll forward the patch to the Journal so they can test and see if it works on their end.
I'll keep you posted.
ramon
 
Posts: 940
Joined: Wed Oct 15, 2003 6:15 am
Location: Brasí­lia/DF - Brasil

Re: Select Reviewer Performance Issue

Postby jamilatta » Mon Apr 04, 2011 4:23 am

Ramon,

try to use this code, you can improve the code:

function &getReviewersForArticle($journalId, $articleId, $round, $searchType = null, $search = null, $searchMatch = null, $rangeInfo = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
$paramArray = array('interests', $articleId, $round, $journalId, RoleDAO::getRoleIdFromPath('reviewer'));
$searchSql = '';

if (isset($search)) switch ($searchType) {
case USER_FIELD_USERID:
$searchSql = 'AND user_id=?';
$paramArray[] = $search;
break;
case USER_FIELD_FIRSTNAME:
$searchSql = 'AND LOWER(first_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_LASTNAME:
$searchSql = 'AND LOWER(last_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_USERNAME:
$searchSql = 'AND LOWER(username) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_EMAIL:
$searchSql = 'AND LOWER(email) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
break;
case USER_FIELD_INTERESTS:
$paramArray = array('interests', $articleId, $round, ASSOC_TYPE_USER, 'interest', $journalId, RoleDAO::getRoleIdFromPath('reviewer') );

$searchSql = 'AND LOWER(cves.setting_value) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
$paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');

$result = &$this->retrieveRange(
'SELECT DISTINCT
u.*,
a.reviewer_id
FROM users u
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?)
LEFT JOIN roles r ON (r.user_id = u.user_id)
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id AND a.cancelled = 0 AND a.submission_id = ? AND a.round = ?)

LEFT JOIN controlled_vocabs cv ON (cv.assoc_type = ? AND cv.assoc_id = u.user_id AND cv.symbolic = ?)
LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id)
LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)

WHERE u.user_id = r.user_id AND
r.journal_id = ? AND
r.role_id = ? ' . $searchSql . '
ORDER BY last_name, first_name',
$paramArray, $rangeInfo
);

break;
case USER_FIELD_INITIAL:
$searchSql = 'AND (LOWER(last_name) LIKE LOWER(?) OR LOWER(username) LIKE LOWER(?))';
$paramArray[] = $search . '%';
$paramArray[] = $search . '%';
break;
}
if($searchType != USER_FIELD_INTERESTS){
$result = &$this->retrieveRange(
'SELECT DISTINCT
u.*,
a.reviewer_id
FROM users u
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?)
LEFT JOIN roles r ON (r.user_id = u.user_id)
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id AND a.cancelled = 0 AND a.submission_id = ? AND a.round = ?)
WHERE u.user_id = r.user_id AND
r.journal_id = ? AND
r.role_id = ? ' . $searchSql . '
ORDER BY last_name, first_name',
$paramArray, $rangeInfo
);
}

$returner = &new DAOResultFactory($result, $this, '_returnReviewerUserFromRow');
return $returner;
}
jamilatta
 
Posts: 2
Joined: Mon Feb 21, 2011 5:31 am

Re: Select Reviewer Performance Issue

Postby marct » Mon Apr 25, 2011 6:36 pm

This code snippet resolved the issue for us. Performance is now fixed. Thanks very much!
marct
 
Posts: 4
Joined: Tue Feb 08, 2011 11:04 am


Return to OJS Technical Support

Who is online

Users browsing this forum: No registered users and 4 guests