OCS Freezing while selecting a user

[BVBAccelerate]

Hello,

I have an installation of OCS running that freezes whenever someone tries to select a reviewer for an abstract. I've pinpointed the problem, and it's a slow query to get the list of users to select the abstract. The users table has 1176 rows, the user_settings table has 819, the roles table has 741 and the review_assignments table 3126. I'm assuming that this query takes so incredible long because of that review_assignments table and how many times that table is called in the query (copied below).

I'm looking for some way to fix this problem since they have completed their abstract submissions and at this point are ready to start reviewing. I've thought of a few things:

I am wondering if in the upgrade from 2.3.3.1 to 2.3.4 has any improvement to this query that may allow it to execute.

My only other guess as to what to do would be for them to go in and start deleting reviewers from paper assignments. I'm not even sure if that would be possible, however.

I'm worried that I will have to go into the database and start deleting these assignments that way. I'd like to avoid that considering I don't want to mess up any relational data.

Does anyone have any insight as to whether or not I am correct in diagnosing the problem? Also, any input on my suggestions above or additional suggestions would be very helpful.

Thanks in advance!

Code: Select all

# Time: 111117 13:09:21
# User@Host: iassid_conf[iassid_conf] @ localhost []
# Query_time: 4499  Lock_time: 300  Rows_sent: 25  Rows_examined: 395310566
use iassid_conference;
SELECT DISTINCT
            u.user_id,
            u.last_name,
            ar.review_id,
            AVG(a.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 a ON (a.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.paper_id = 505 AND ar.stage = '1')
            LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests')
            LEFT JOIN roles r ON (r.user_id = u.user_id)
         WHERE   u.user_id = r.user_id AND
            r.sched_conf_id = '8' AND
            r.role_id = 256 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,25;


[BVBAccelerate]

I'm also noticing that even as I cancel the review requests it is not deleting records from the database. Is there any way to delete a review request? If not it seems like the system would eventually overload itself after many conferences and papers.

If there is no way to delete them, are there any relational tables in the system that might be negatively affected if I just deleted all of the review requests that had the 'cancelled' field set to one?

I really need to figure out how to reduce this soon.

[balkan]

Dear Sirs,

I have the same problem, at the beginning, when we started to Select the Reviewers from the list of reviewers it was OK, by the time it is more and more slow to edit the list of Reviewers for Assign (there were 150 person in this list and I have unenrolled lot of them but it was not good ideas because it is slower and slower every time).
Now we can not work, we are blocked and in the middle of the preparation of the conference. The authors should be informed about acceptance of their paper until 30 of November, but we can Assign the reviewers and shared (distributed) the abstracts for review.

Please if somebody can help me in this matter.

Olivija

[balkan]

In the last period, after long time waiting to display the list of reviewers to make selection (I wait more than 3 minutes) I have this page:

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
Apache Server at ocs.balwois.com Port 80


I do not know what to do ?????

Please if somebody has an idea ?????

Olivija

[balkan]

I forget to mention that I am using also OCS 2.3.3.1.

Olivija

[ramon]

Hello Olivija,

I would highly recommend upgrading to the latest version.
Also, check your server logs to see what query is hanging your database, or if it's something else entirely (memory leaks, low memory, etc).

I remember having a similar issue, but it was OJS related, so the fix posted may not be fully compliant.

[BVBAccelerate]

Well, just in case anyone was wondering I was able to solve the problem but I had to delete the cancelled review requests right from the database itself. I'm not sure if it's broken and important relations anywhere in the database, but I haven't heard of any problems in the past few days. It might be a hack of a solution, but I'd still like to hear from the developers about the possibility of the upgrade solving the problem with the query and any other insights.

[jmacgreg]

Hi BVBAccelerate,

I don't believe this has been fixed in our latest stable release (ie. OCS 2.3.4), but it may be fixed in our development branch. Any chance you could try applying the following patch: https://github.com/pkp/ocs/commit/b64df ... d0250.diff. If it doesn't apply cleanly, and/or if you don't see any improvements, please let us know.

Cheers,
James

[balkan]

I have 1181 records in the table review_assignments, in fact there are around 730 abstract and for each abstract there are one, two or three reviewers. I do not know if this table is limited and can not accept more assignments ???

If I delete the records, I will delete the reviewers which are already assigned ????? And the abstract will continue without reviewers.

But in my case, I have the problem in the moment when the Director Topic click to SELECT REVIEWERS and in that moment all OCS System is blocked now, it takes more than 6-7 minutes to edit the list of reviewers, Once the list is edited the director Topic can do assignment, but the problem is to edit this list. At the beginning it was working well, an every new Selection of the Reviewers, it was longer and longer .... end very rapidly it became insupportable, now the OCS system blocked completely.

The rest of the system is working correctly (except some bugs, that I haven't correct it yet.

I am afraid to make upgrade to 2.3.4, I am afraid of new problems and we are in the middle of the organization.

Definitely is not question of godaddy, I spoke with them on the phone and everything is ok from their site, also as I told you at the beginning it was working well.

I have feeling that the problem is in the query to search for the reviewers or some information are accumulating somewhere that slow down the process.

I really do not know what to do, and I am very disappointed specially that I pushed the Organizing Committee to use his system. Now everybody is waiting solution from me

Thanks a lot,

Olivia

[BVBAccelerate]

Hey Olivija,

I'm almost positive that the problem here is the number of reviewers and records in that table. I deleted many of our records and now we are working at a much better pace. There technically isn't a limit but the query that is running in the system to grab that list is just inefficient. You might have a problem that will be unavoidable without upgrading if you are ultimately going to have around 2000 records in that table. I am not even sure if the upgrade solves the problem so it might not be worth it. I'm going to do as James suggested and apply that patch after talking to the people running the conference to see if they're okay with upgrading now.

If you have the ability to reboot your server or kill queries that are running on your database that's what I kept doing as a temporary fix to the problem.

One thing that doesn't help is the fact that once you cancel reviewers it doesn't delete the record from the table, it just flags that record as cancelled. I went through and deleted every review request that was cancelled by running a query on the database. I'd suggest doing that first to try to improve the load times.

Ultimately, it is the query and the number of records that is the problem. Without am improved query or some other fix we're just going to need to keep the number of records in this table very low. This will ultimately be a problem for us as well considering we have about 800 abstracts in the system and this is only the first conference! After a few more it might become unusable again.

[balkan]

We solved the problem (in our case). The problem was in the SQL query from the script TrackDirectorSubmissionDAO.inc.php in the getReviewersForPaper function.

we tried it directly in phpMyadmin and it took 4 minutes time execution and more than 4 000 000 have been retrieved before GROUP clause!

SELECT DISTINCT
u.user_id,
u.last_name,
ar.review_id,
AVG(a.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 a ON (a.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.paper_id = 84 AND ar.stage = 2)
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests')
LEFT JOIN roles r ON (r.user_id = u.user_id)
WHERE
u.user_id = r.user_id AND
r.sched_conf_id = 1 AND
r.role_id = 256
GROUP BY u.user_id, u.last_name, ar.review_id
ORDER BY u.last_name ASC LIMIT 0,30;

we change the order of LEFT JOIN clauses and it works better :

SELECT DISTINCT
u.user_id,
u.last_name,
ar.review_id,
AVG(a.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 ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.paper_id = 84 AND ar.stage = 2)
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 user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests')
LEFT JOIN roles r ON (r.user_id = u.user_id)
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id)
WHERE
u.user_id = r.user_id AND
r.sched_conf_id = 1 AND
r.role_id = 256
GROUP BY u.user_id, u.last_name, ar.review_id
ORDER BY u.last_name ASC LIMIT 0,30;

and we finally by pass the bug :

SELECT DISTINCT
u.user_id,
u.last_name,
ar.review_id,
0 AS average_quality,
0 AS completed,
0 AS incomplete,
0 AS latest,
0 AS average
FROM users u
LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.paper_id = 84 AND ar.stage = 2)
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests')
LEFT JOIN roles r ON (r.user_id = u.user_id)
WHERE
u.user_id = r.user_id AND
r.sched_conf_id = 1 AND
r.role_id = 256
GROUP BY u.user_id, u.last_name, ar.review_id
ORDER BY u.last_name ASC LIMIT 0,30;

[balkan]

Thanks a lot for everybody, you helped us a lot. Now we can continue our work.
I expect that the developers will make a new patch soon for repairing this problem correctly.

Olivija

[mcrider]

Hi folks,

I've tried to get some diagnostics for this page on the largest conference dataset I could get my hands on, but the page (select reviewer) loads fast. Can anyone send me a database dump of their OCS installation so I can confirm balkan's changes produce the right effect?

Cheers,
Matt

[balkan]

Hi Matt,

Can I have your email address to send you our asked files.

Olivija

[mcrider]

Hi balkan,

Its mattcrider AT gmail dot com. But if its a large file, you may want to put it on dropbox or some other web-based hosting environment.

Thanks,
Matt