OJS is very slow with a large table of contents (hundreds of items)

[diegoabadan]

We published conference proceedings in OJS. When we updated one of them to OJS 3.2.1-2, access to the table of contents became extremely slow. We note that there is no specific SQL that takes too long, but the number of SQL queries is high.

Nothing appears until all queries are completed, and the sum of them plus PHP processing is a bottleneck.

Highlights:

• Editions with more than 300 published papers take almost 2 minutes to load
• Access is quick if you go directly to the page of each paper or other than the table of contents
• OJS installations with fewer items published per edition on the same server work at the expected speed.

[asmecher]

@diegoabadan, is your database server on the same system as the PHP is executing?

[diegoabadan]

No, the database is on a nearby server that also serves other instances of OJS, connected via a private network.

The persistent = Off option doesn't seem to help.

[asmecher]

@diegoabadan, we've had a report or two that the overhead imposed by running a database that's on a separate server is considerable. It's probably the cumulative effect of network overhead for each of the queries, and the total effect is considerable. Is it an option to try running the database on the same system?

[diegoabadan]

I will do the test and return with the result.

thanks!

[diegoabadan]

Running the database on the same server as the application made the time much shorter. It now typically takes a little over 4s to load the first resource, which can range from 3s to just over 6s.

A typical journal on this same server, using an external database, takes less than 2s.

External test images to help:

Proceedings

1. Test 1:

1. Test 2:

Typical Journal in some server and other DB:

Servers details: 8GB 4 colors SSD (both external database and application server)

[diegoabadan]

For this usage scenario for Proceedings, I was able to get around the problem by enabling the option web_cache = on after https://github.com/pkp/pkp-lib/issues/6512 correction

I suspect that with the growth of rolling pass publications, journals with a more intense publication flow may be affected.

[diegoabadan]

I suspect that with the growth of rolling pass publications, journals with a more intense publication flow may be affected.

Hi! We have an example of a journal with a database on the same server and it takes more than 5s to load the TOC:

http://www.seer.unirio.br/segurofundamental/issue/view/417 http://www.seer.unirio.br/segurofundamental/issue/view/380

It's not a time that makes use unfeasible, but I consider it a negative use experience.

Note: This time is for repeated accesses, where there is already some cache optimization.

OJS 3.2.1-4

[diegoabadan]

We received another complaint of TOC loading slowness: https://portalperiodicos.unoesc.edu.br/siepe/

Database in same server.

As this is an OJS with multiple journals, we cannot enable caching just for this publication.

[asmecher]

@diegoabadan, I think this will be at least partially resolved with https://github.com/pkp/pkp-lib/issues/7304, which should be released in 3.3.0-15. There is a linked commit there if you're able to try applying it to a 3.3.0-x release with your test data to verify.

The upgrade to 3.4.0 (when released) may also surface some underlying issues that could cause extremely low performance; for example, it adds formal foreign key constraints (https://github.com/pkp/pkp-lib/issues/6093) which will break if your database happens to accidentally mix MyISAM and InnoDB storage engines. Running a test upgrade to 3.4.0rc3 might be a good idea.

[diegoabadan]

Hello Alec.

I tested it with the version currently in use and with one generated from Git (future 3.3.0-15) and I didn't notice a relevant difference in times: in both between 8s and 10s to load the current TOC.

I cleared the caches and tested a few times with each version.

I'm still going to experiment with OJS 3.4-rc3 and return here. Thanks for the warning, we always use InnoDB.

[asmecher]

@diegoabadan, can you share a database dump (anonymized as necessary) with me privately?

[diegoabadan]

I'll check how to anonymize and send it to you. Thanks!

[asmecher]

I have a few SQL statements lying around that do at least some of the work:

UPDATE users SET email=CONCAT(username,'@mailinator.com'), password=SHA1(CONCAT(username,username));
UPDATE authors SET email=CONCAT(author_id,'@mailinator.com');
UPDATE plugin_settings SET setting_value='' WHERE setting_name LIKE '%ssword%';
UPDATE plugin_settings SET setting_value='' WHERE setting_name LIKE '%apiKey%';
UPDATE plugin_settings SET setting_value = 0 WHERE setting_name = 'enabled' AND plugin_name = 'acronplugin';

This is just the basics to avoid engaging with APIs or emailing people; you might want to go further.

[diegoabadan]

Dump sent (e-mail).

[diegoabadan]

Related post on the PKP forum: https://forum.pkp.sfu.ca/t/suggestion-to-improve-performance/78230

[diegoabadan]

I tested it on a TOC with around 548 published items. Approximate loading times:

On the current server with OJS 3.3.x: 9s On new server with 4 dedicated CPUs, NVMe disk and OJS 3.3.0-16: 7s Upgrading to OJS 3.4: 5s

The new server is running Ubuntu 22.04 and PHP 8.1.2.