CUNY Academic Commons

Here's a breakdown of the performance issue introduced by the 2.7.14 release, at least so far as I understand it.

First, there was a code-level change introduced by 2.7.14 that triggered a huge amount of database traffic, which resulted in the site crawling to a stop. The table-of-contents-plus plugin appears to have been taken over by All-In-One SEO, and the plugin was rewritten to use the AIOSEO internal libraries. One of these libraries uses a database-upgrade routine that involves a check that looks like this: SELECT .... FROM INFORMATION_SCHEMA.COLUMNS.... On our large database, this sort of query requires an enormous amount of overhead, as all columns across all tables are loaded into memory.

The plugin only in fact needs a very limited amount of info from this query, specifically the columns belonging to tables with the 'aioseo' or 'actionscheduler' tables belonging to the current site. I've taken two steps here:

1. I reported the issue to the plugin authors, along with a suggested fix. See https://wordpress.org/support/topic/information_schema-columns-scan-causes-extreme-load-on-large-databases/#new-topic-0
2. I introduced a 'query' filter that catches these problematic queries, and then uses more targeted queries to collect and return the limited information the plugin needs. I put this in place as a hotfix yesterday while we were in the middle of our incidents, and today I migrated it over to a targeted plugin-fix file that is tracked under version control: https://github.com/cuny-academic-commons/cac/commit/87316f407319823a5ca67e1821d566c8ddbd5986

I've made a note to check with the plugin support forums (and the plugin code itself) before my next scheduled maintenance release on Nov 10 to see whether the AIOSEO team has implemented my fix or something like it. Once there's an upstream fix, I can remove the 'query' filter I've got in place.

This kind of issue has come up in the past with plugin updates, and we've discussed PHPCS sniffs and other static-analysis tools that might help us head them off. See #22630, #20828, #21509. Jeremy, I wonder if we're at a point with our AI-agent tools where we could write such a tool, or maybe even begin to talk about introducing AI-based scans for targeted items like this into our CI workflow.

So that's the part of the issue related to our codebase. The next part of the incident was that, when our database crashed, it somehow caused our Redis object cache to go away on the production site. It could be because a MySQL service restart was triggered or something; Reclaim reports that the vendor that they use for webserver containers like ours recently changed their default config to use Memcached instead of Redis, and it could be that this new configuration took hold after a daemon was reloaded. I don't know that it's worth doing a ton of research to diagnose the specific order of events that led to the object cache backend being swapped in this way, since presumably it's a one-off. I worked with Reclaim on the fly to swap out our Redis object cache drop-in with one that supports Memcached. I noticed during that, during some of Reclaim's reprovisioning, our custom object-cache.php drop-in - which acts like a loader that selects a cache engine based on the CAC_CACHE_ENGINE constant - was wiped out in production by the object-cache drop-in that's written by the Litespeed plugin. I find this annoying, as I prefer to keep our code-level changes under version control, and I've asked the Reclaim team on multiple occasions if we could lock down file permissions to avoid this type of situation where a plugin writes application files. But given the other items that we've had to deal with this week, coupled with the fact that the object cache setup does in fact seem to be working at the moment, I don't have the bandwidth to address the issue at this time. I may circle around to it in a few weeks.

I have to say that performance on the site is spotty as I write this. So, while I think that the plugin issue described above is the proximate cause of yesterday's crash, and I think that I've solved that proximate problem, it may be that other config-level changes are necessary to get us back to normal. I'll leave this ticket open while I continue investigation over the upcoming days.

Thanks for the ping, Colin. I took some more action after my last update, and site performance is now good, but I forgot to record it here. On Wed Oct 28 I continued our exchange with Reclaim about our pending issues. They believed there might be a problem with database overhead, explaining the ongoing slowness on the site. This didn't seem right to me based on what I was seeing, so I sent back a message with a couple other theories. One of them was that the default Memcached configuration on their vendor's new setup, which had the -vv flag indicating verbose messaging sent to the logs, was causing latency due to frequent use of the systemd-journal and disk I/O. Reclaim restarted the Memcached daemon without that flag, and the performance problems seemed to go away.

My wordpress.org support post regarding the issue in table-of-contents-plus got a nice response from a member of the plugin team. See https://wordpress.org/support/topic/information_schema-columns-scan-causes-extreme-load-on-large-databases/#post-19059153 So it sounds like an upstream fix is likely in the works. When that fix arrives, my 'query' filter will degrade gracefully - it will no longer match any active queries. When I notice that this has happened, I'll remove my query filter to clean up the database and avoid unnecessary filter overhead. Until then, the workaround I have in place should continue to mitigate the performance issues.

Given the status of things, I'm going to mark this ticket Resolved. I'll leave a note here once the upstream changes have been implemented and I can remove my hotfix.