Submissions (again) loading slowly

Hi,

submissions (especially All Active and Archives tab) are loading sluggishly (>10 sec per Tab).
All patches as discussed in Quite slow loading after upgrade 3.1.2-4 to 3.2.0-1 are applied, tables are all InnoDB.
No errors so far in the error log when the submission page is loaded.

This is a multijournal (11 active journals) installation using:
OJS 3.3.0-10
PHP 7.3.29
Database Server Version 5.5.5-10.6.3-MariaDB-log

These journals have usually about 200 archived articles and 20-50 active submissions.

Submission tabs of another journal on a single-journal installation with around 5700 archived articles and 30-50 active submissions load within 2 seconds (there, PHP 7.3.33 is installed).

Scratching my egghead.

Is MariaDB on the same server as OJS (eg localhost)?

Something that can negatively affect performance is the number of users. Would they be in the hundreds or thousands in this OJS?

No, it isn’t. But this is a very potent MariaDB server (primary/replica) with which we did not have problems. Yes, total users may be over thousand.

@abadan - we are talking submissions here, not users. @asmecher - is the fast user search patch mentioned in [OJS 3.3.0.3] Slow queries on the user management page · Issue #6991 · pkp/pkp-lib · GitHub already in OJS 3.3.0-10 ?

We detected slowness in OJS in other areas, in very specific scenarios, where running OJS and the database on the same server brought significant improvements. However, I have doubts if it would apply in this case of the submissions listing.

As for patches to improve performance dealing with users, it is present as of OJS 3.3.0-9. I understand that they are different cases, but I remember side effects here. Trying to remember the scenario where I saw this occur and I suspect it was only when doing a search in the submissions in one of these tabs.

Unfortunately, we are not allowed to have the DB on the same server.

We did a few tests with our test installation and test journal (310 submissions) and found the following:

  • total time until all tabs were loaded (i.e. show the counts) is 13-20 seconds.
  • we enabled database debug in config.inc.php . About 11’000 SQL queries were sent to the DB. I assume this is because Laravel splits the queries into single SELECT statements, doesn’t it? @asmecher
  • We did an audit log DB-server side. Results:
    None of the query was a slow query. Good so.
    Just one connection is opened and closed per Submissions Tab loading, good so.
    The 3000 queries per connection were handled within 2 secs. Good so.
  • We checked response times for the page components using the network time utility in the Safari developer tools. The main page needs 5-6 seconds, the XHR (json responses) each 6-9 seconds.XHR is about 500 KB of data each.
  • We checked then the API call for the XHR, e.g. something like api/v1/submissions?status=1&assignedTo=-1&searchPhrase=&count=30&offset=0&=1651150524558
    The interesting thing is the count parameter (which is the number of items returned per page). This parameter value is hard-coded in lib/pkp/classes/components/listPanels/PKPSubmissionsListPanel.inc.php - Bad so. In my opinion, it should be configurable as is the number of list items in the Website settings of a journal. Reducing this parameter in the code immediately reduces load time as we tested.

Questions:

  • Can queries be optimised further? I think 3000 per connection to get back 30 submissions and a total submission count is pretty hefty. Also, to construct the JSON response from the individual results.
  • Are there any recommended caching components that could be installed to improve performance (e.g. memcache)?

Hi @mpbraendle,

For the moment, I’d strongly recommend finding a way to host your PHP and database on the same server. I recognize that the number of connections required for a page load is extremely high, but it has not been a performance bottleneck for users in 99% of hosting scenarios and so it hasn’t been a priority to optimize it.

With regards to caching we are in the middle of a fairly major rewrite in 3.3 and 3.4 of our storage back-end, to move it from MySQLi under ADODB to PDO under Laravel’s query builder. We’re in the process of removing dependencies on our long-standing homemade caching toolset, in order to replace it with Laravel caches or Stash (see Replace homebrew caching library with 3rd-party caching library · Issue #3304 · pkp/pkp-lib · GitHub). Once this is done we’ll have a lot of additional options, but these are large code migrations and will take a few releases to complete.

In other words, we are moving towards what you propose, but it’ll take longer than your current time horizon if you’re currently suffering performance issues. You’ll need to host PHP and MySQL on the same box for now.

Further investigation and proposals are of course welcome!

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks Alec - as said above, the policy of our university is that databases are hosted on a central database server (although potent and on a fast network) in order to provide 7/24 service including support/backups etc. So we have to wait for and are looking forward to the next releases.