OJS 3.3.0.6
Going to “Users & Roles” in the Dashboard, it takes about 40 seconds to load the list of users. Website search seems to be fine otherwise.
Seems similar to the issue described here, but seems it should have been fixed before the time 3.3.0.6 was released. Search is slow and not always working after upgrade to OJS 3.2.1.1 - #16 by asmecher
Thanks!
I also received a similar report in OJS 3.2.1-4. That’s about 16 seconds for nearly 5,700 users.
@guitman444, how many users are there in your OJS?
Edit: In our case, the slowdown in 3.2 is only in searches and not in the initial loading of the user listing.
Not really all that many, 1396.
It’s gotten real slow for us when searching for a specific user - In this case it is taking 2019 seconds - over a half hour(!) (the web request is timed out by the time that finishes)
Here’s the slightly redacted log entry from the MySQL Slow Query Log:
# Time: 210616 23:33:12
# Query_time: 2019.308077 Lock_time: 0.000226 Rows_sent: 0 Rows_examined: 122481553
SELECT DISTINCT u.*,
COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given,
CASE WHEN ugl.setting_value <> '' THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family
FROM users AS u
LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation')
LEFT JOIN user_interests ui ON (u.user_id = ui.user_id)
LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id)
LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id)
LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id)
LEFT JOIN user_settings ugl ON (u.user_id = ugl.user_id AND ugl.setting_name = 'givenName' AND ugl.locale = 'en_US')
LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = 'givenName' AND ugpl.locale = 'en_US')
LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = 'familyName' AND ufl.locale = 'en_US')
LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = 'familyName' AND ufpl.locale = 'en_US')
LEFT JOIN user_settings usgs ON (usgs.user_id = u.user_id AND usgs.setting_name = 'givenName')
LEFT JOIN user_settings usfs ON (usfs.user_id = u.user_id AND usfs.setting_name = 'familyName')
WHERE 1=1 AND ( ( LOWER(CONCAT(COALESCE(usgs.setting_value,''),COALESCE(usfs.setting_value,''),u.email,COALESCE(us.setting_value,''))) LIKE '%usernamehere%' OR LOWER(cves.setting_value) LIKE 'usernamehere%' ) ) ORDER BY user_family, user_given LIMIT 25 OFFSET 0;
I’ve done a cursory check for indexes and they seem to be there as suggested. We have 2530 users. Running 3.3.0.6.
I ran your query, and it took 20 seconds for results. Not 30 minutes like yours, but still seems a bit long for a user query.
In the GUI, I ran a search for the same user and this time it took 68 seconds, so much longer than the sql query in pHpMyAdmin.
Any luck on your end since your last post?
We are having the same problem. but in the DB we have too many spam users from OJS2 version like around ~700k (yes). in the whole site.
There are 53 journals hosted on the site and we getting ~5mins to pre-load the user page or in peak hours it just never ends loading the user page.
Anyone know how to skip the pre-loading just let the journal managers to perform search when they need? sometimes they are justing searching inside a small group of users like section eidtors.
This is the query we stuck at the most:
select u.*, COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given,
CASE WHEN ugl.setting_value <> ‘’ THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family, MAX(ra.date_assigned) as last_assigned,
(SELECT SUM(CASE WHEN ra.date_completed IS NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as incomplete_count,
(SELECT SUM(CASE WHEN ra.date_completed IS NOT NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as complete_count,
(SELECT SUM(CASE WHEN ra.declined = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as declined_count,
(SELECT SUM(CASE WHEN ra.cancelled = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as cancelled_count,
AVG(DATEDIFF(ra.date_completed, ra.date_notified)) as average_time, (SELECT AVG(ra.quality) FROM review_assignments AS ra
WHERE u.user_id = ra.reviewer_id AND ra.quality IS NOT NULL) as reviewer_rating from users as u
left join user_user_groups as uug on uug.user_id = u.user_id
left join user_groups as ug on ug.user_group_id = uug.user_group_id
left join user_settings as ugl on ugl.user_id = u.user_id and ugl.setting_name = ‘givenName’ and ugl.locale = ‘en_US’
left join user_settings as ugpl on ugpl.user_id = u.user_id and ugpl.setting_name = ‘givenName’ and ugpl.locale = ‘en_US’
left join user_settings as ufl on ufl.user_id = u.user_id and ufl.setting_name = ‘familyName’ and ufl.locale = ‘en_US’
left join user_settings as ufpl on ufpl.user_id = u.user_id and ufpl.setting_name = ‘familyName’ and ufpl.locale = ‘en_US’
left join user_group_stage as ugs on uug.user_group_id = ugs.user_group_id left join user_settings as us on u.user_id = us.user_id
left join user_interests as ui on u.user_id = ui.user_id
left join controlled_vocab_entry_settings as cves on ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id
left join review_assignments as ra on u.user_id = ra.reviewer_id where ug.context_id = 2 and ug.role_id in (4096) and u.disabled = 0 and ugs.stage_id = 3
and (lower(u.username) LIKE ‘%or%’ or lower(u.email) LIKE ‘%or%’
or (us.setting_name = ‘givenName’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘familyName’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘affiliation’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘biography’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘orcid’ and lower(us.setting_value) LIKE ‘%or%’)
or lower(cves.setting_value) LIKE ‘%or%’)
group by u.user_id, user_given, user_family order by u.user_id desc LIMIT 30 OFFSET 0
Hi! @abadan @guitman444 @mapress888 @the_reis were any of you able to solve this issue?
Hi @elementslive ,
There were improvements related to issue 6991.
See especially this recent patch.
Any additional comments, I suggest continuing via the issue cited above.
Thanks @adaban I will give it a try!
Chiming in, we are on version 3.3.0-17 and are having the same issue. Maybe we are seeing a different problem from the one solved in issue #6991. We are running a multi journal installation with 2.848 entries in “users” and ~36.000 in “user_settings”. The query executed by clicking on “Users & roles” is the following and takes ~90s to resolve in the backend (even if it is run in the DB directly):
SELECT u.*, (
SELECT us.setting_value
FROM user_settings AS us
WHERE
us.user_id = u.user_id
AND us.setting_name = 'givenName'
AND us.locale IN ('en_US', 'de_DE')
-- First non-null/empty values, then give preference to the current locale
ORDER BY
COALESCE(us.setting_value, '') = '', us.locale <> 'en_US'
LIMIT 1
) AS user_given, (
SELECT us.setting_value
FROM user_settings AS us
WHERE
us.user_id = u.user_id
AND us.setting_name = 'familyName'
AND us.locale IN ('en_US', 'de_DE')
-- First non-null/empty values, then give preference to the current locale
ORDER BY
COALESCE(us.setting_value, '') = '', us.locale <> 'en_US'
LIMIT 1
) AS user_family
FROM users AS u
WHERE 1 = 1 AND EXISTS (
SELECT 0
FROM user_user_groups uug
INNER JOIN user_groups ug
ON ug.user_group_id = uug.user_group_id
WHERE
uug.user_id = u.user_id
AND ug.context_id = 19
) ORDER BY user_family, user_given LIMIT 25 OFFSET 0
Does anyone have any idea what’s causing the slowdown here?
Hi everybody!
I’ve seen this problem on the past year, and there’s an issue to improve it here: Slowness at the "user & roles" page · Issue #8700 · pkp/pkp-lib · GitHub
The pull requests were rejected (due to an aggressive approach in underlying libraries), so I’ll revamp them and come up with something else.
Best,
Jonas Raoni
Hi!
Today I’ve updated the PRs, so I think they will be available into the next release, I’ve spread the improvement to everywhere that had the same problem, so a couple of other places should get a performance bump as well.
** If anyone who’s having the issue is able to apply the PRs and check if it solves the problem, I’d be grateful.**
Best,
Jonas Raoni
I’ve just merged the PRs, so I’ll close this topic, the improvements should be included in the next release.
Best,
Jonas Raoni