We currently have an instance of Collective Access backend ("Providence") installed on premises. About 4650 objects were imported into the system. Many of these objects are scanned PDFs with OCR'ed full text and many of these PDFs were hundreds of megabytes in size, namely books. After everything was imported, the system was responsive. Nevertheless, after a few days of no activity, the system would no longer respond within the 300 seconds of PHP's max_execution_time configuration property. Max execution time was increased to 1000 seconds, and at the time, Providence was able to respond within that time frame. Subsequent requests were normal, we were able to navigate the interface without issue. Later, after about a month without activity, the system fails to respond. I attempted to increase both PHP's max execution time and Apache's Timeout property to 60000 seconds (16.6 hours). Unfortunately, the system is returning an empty response after 11 hours. While the system hangs, each request consumes 100% of one CPU core in the system, and continues to consume 100% of a CPU core until failure 11 hours later.

I also ran caUtils rebuild-search-index on the production Providence instance, which took 2h 45 minutes to complete. The ca_sql_search_word_index table is by far the largest database table, with 33 million records.

We experienced similar behavior in our test instance with only a handful of large objects. The longer it is left idle, the longer the system hangs on that first request. Thankfully, on our test instance, the scale of the problem is much smaller and the system is able to complete that first request within a few minutes and after that everything works.

Perhaps it's worth mentioning that throughout all this time, we had scheduled (cron) jobs running "caUtils process-task-queue" and "caUtils process-indexing-queue" every 10 minutes, respectively.

Production environment:

Intel(R) Xeon(R) Bronze 3206R CPU @ 1.90GHz (8 Cores)
64GB RAM
Ubuntu 20.04 LTS Docker image
    Apache 2.4.41
    PHP 7.4 without FPM
    Providence 1.7.17
MySQL 8.4 Docker image
NFS mounted media directory volume

Test environment:

Intel(R) Xeon(R) CPU E5-2430 0 @ 2.20GHz (6 Cores w/SMT)
16 GB RAM
Ubuntu 20.04 LTS Host OS
    Apache 2.4.41
    PHP 7.4 without FPM
    Providence 1.7.13
Local media directory