Describe the issue or problem
When using the Users & Roles page (Settings > Users & Roles > Users > Search) The search is exceedingly slow with tested average wait times of 5-8 minutes.
Steps I took leading up to the issue
For example:
Go to Users & Roles page (Settings > Users & Roles > Users > Search)
Click on ‘Search’
Enter the Given Name of a known user
Check the box for “Include users with no roles in this journal”
Click “Search”
Wait for 5-8 minutes for results to be displayed.
What application are you using?
For example, OJS 3.3.0-6
Additional information
Happy to obtain, but don’t know where exactly to go from here.
What the database app that you are using for the OJS ?
If you are using MySql have you change database engine to Innodb ?
Have you check the collation handshake between the OJS and the database collation ?
FYI, we have same experience with our client with the same case when using MariaDB for OJS after changing to MySQL it run well.
Hi @navotera ! The users table has 4,830 rows. It is using mysql (mariadb currently) and the database engine is innodb. Collation handshake… I’m guessing that you mean the settings on the connection in config.inc.php:
; Database collation
collation = utf8mb4_bin
; Client output/input character set
client_charset = utf-8
; Database connection character set
connection_charset = utf8mb4
Unfortunately, as we have upgrade and hundred one of our client with hundred of journal instance, even in the version of OJS 3.3.0.11 when using MariaDB the user list even does not load and just show infinite loading. When we change to MySql it load flawlessly without any additional config.
For the collation make sure in the OJS config, it should be the same handshake with charset configured in the database engine configuration, so there won’t need any intermediate character translation between the app and the database server. This will optimize the query of database app.
Perhaps this knowledge can help other user who have same issue.
After reading the multiple threads, I ran the sql query here and saw a reduction in time from 5-8minutes down to 17-30 seconds. Obviously, that’s still kinda slow, but we’re on the old version. We’ll be updating to 3.3.0-9+ soon.
In regards to the collation handshake… these are compatible from what I understand. However, we may be running into slowness that is actually caused by using utf8-mb4 … an untested, but plausible, theory. Unfortunately, there isn’t an exact match available for utf8-mb4 for all portions of encoding handling (client, connection, database, table, field) as some of them simply say utf8, but mean mb4. This combination, however, has kept our setup from having mojibake.
I think we’re on the right tack.
Thank you, everyone, for your attention on this one.