CUNY Academic Commons

We could develop a tool that analyzes existing sites and identifies these sites, and then work to discover what's causing the disproportionate use.

This can be done in WP-CLI with:

wp db size --tables --human-readable --all-tables --orderby=size --order=desc

It does do queries against information_schema, so this might be an intensive operation (especially the --all-tables clause).

fixing bugs that might cause inappropriate database use

I came across one such instance today with the Ninja Forms plugin. See #17542. I suspect there are many other plugins doing similar things such as Jetpack.

2. Explore a strategy for deleting/decommissioning/archiving empty sites. It's likely that a large number of sites have been created but never used, and can be safely removed.

We could do a DB query against the wp_blogs DB table. Check the registered and last_updated columns to see if they are the same (or very close to the same) to determine empty sites. Probably add a 5-year threshold to be safe and check the corresponding posts table to verify post count.

3. Explore a strategy for archiving legacy sites.

What would the criteria be for a legacy site that would warrant converting over an existing site over to static HTML?

In my experience, it's often the case that a small number of sites are responsible for a disproportionate amount of DB storage space

This seems the most impactful near-term. If we can pull a list of tables by size, I would expect to see a small handful of sites with a runaway options table or some other sort of generated content that we can find a root cause for. (Like Ray just mentioned w/ Jetpack & Ninja Forms)

I think options 2 and 3 may not save a bunch immediately, but could account for more predictable data usage over time. If we know what those sites look like (e.g. how often a site is started and immediately abandoned), we can plan ahead and say "over the next N months, this much more storage is likely going to be used and this is how we maintain it".