Dear community and PKP,
We’ve run into the same problem IN OUR OJS2 INSTALLATION (my OJS3 test installation shows a similar behavior, I guess it is the very same problem) .
The metrics piled up for our journal (www.jstatsoft.org). Number of rows ca. 3 million, but given the high number of user visits the server started struggling (memory and CPU load). Generation of one page ~6 seconds today.
The reason seems to be this query:
SELECT SUM(metric) AS metric FROM metrics WHERE assoc_id = '1983' AND assoc_type = 257 AND context_id = '1' AND metric_type = 'ojs::counter' LIMIT 5000;
The Problem
No explicit key for the combination of these four variables.
The Solution
My fix was to add a fourth key to the table for this specific query (see above).
ALTER TABLE metrics ADD KEY metrics_assoc_id_assoc_type_context_id_metric_type (assoc_id , assoc_type , context_id , metric_type);
The Result
Helped to cut down the query time from ~6 seconds to about 0.05 seconds in my test-set and gives our system room to breath :). As mentioned I encountered a very similar behaviour on our OJS3 test-installation, need to have a look whether it is the very same problem. If so, I would consider to include this additional index/key in default OJS3 installations?
Tags: @bozana, @asmecher and @jmacgreg
All best,
Reto