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
- 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
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
You can try using staging server with Mysql and report here the different.
The collation that is used by OJS config should the same with the mysql engine used in my.cnf file.
There are some substantial improvements in performance around user management in 3.3.0-9 and onwards; see: https://github.com/pkp/pkp-lib/issues/6991
Public Knowledge Project Team
5-8 minutes for a 4K user database is A LOT.
If is not innodb and collation, I suggest enabling slow_queries in your mysql to discover what query is taking soo long.
Unfortunately, as we have upgrade and hundred one of our client with hundred of journal instance, even in the version of OJS 184.108.40.206 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.
When a page never loads, it’s usually an indication that there’s an error message in the PHP error log. Were you able to check there?
Public Knowledge Project Team
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.
Please Andrew, tell us how it finishes.
This topic was automatically closed after 11 days. New replies are no longer allowed.