https://redmine.gc.cuny.edu/https://redmine.gc.cuny.edu/favicon.ico2023-01-24T01:03:29ZCUNY Graduate Center - Project Tracking SystemCUNY Academic Commons - Feature #17522: Strategies for reducing database sizehttps://redmine.gc.cuny.edu/issues/17522?journal_id=763972023-01-24T01:03:29ZRaymond Hoh
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-1 priority-4 priority-default" href="/issues/17542">Bug #17542</a>: Ninja Forms plugin can write many "_wp_session" entries into the options table</i> added</li></ul> CUNY Academic Commons - Feature #17522: Strategies for reducing database sizehttps://redmine.gc.cuny.edu/issues/17522?journal_id=763992023-01-24T01:25:58ZRaymond Hoh
<ul></ul><blockquote>
<p>We could develop a tool that analyzes existing sites and identifies these sites, and then work to discover what's causing the disproportionate use.</p>
</blockquote>
<p>This can be done in WP-CLI with:</p>
<pre>
wp db size --tables --human-readable --all-tables --orderby=size --order=desc
</pre>
<p>It does do queries against <a href="https://github.com/wp-cli/db-command/blob/5d5aa01469b49b72b06ae72d374c47924d3ca1c9/src/DB_Command.php#L1003" class="external">information_schema</a>, so this might be an intensive operation (especially the <code>--all-tables</code> clause).</p>
<blockquote>
<p>fixing bugs that might cause inappropriate database use</p>
</blockquote>
<p>I came across one such instance today with the Ninja Forms plugin. See <a class="issue tracker-1 status-1 priority-4 priority-default" title="Bug: Ninja Forms plugin can write many "_wp_session" entries into the options table (New)" href="https://redmine.gc.cuny.edu/issues/17542">#17542</a>. I suspect there are many other plugins doing similar things such as <a href="https://blog.timowens.io/clearing-out-table-rows-across-databases-in-wordpress-multisite/" class="external">Jetpack</a>.</p>
<blockquote>
<p>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.</p>
</blockquote>
<p>We could do a DB query against the <code>wp_blogs</code> DB table. Check the <code>registered</code> and <code>last_updated</code> 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 <code>posts</code> table to verify post count.</p>
<blockquote>
<p>3. Explore a strategy for archiving legacy sites.</p>
</blockquote>
<p>What would the criteria be for a legacy site that would warrant converting over an existing site over to static HTML?</p> CUNY Academic Commons - Feature #17522: Strategies for reducing database sizehttps://redmine.gc.cuny.edu/issues/17522?journal_id=764002023-01-24T01:38:05ZJeremy Felt
<ul></ul><blockquote>
<p>In my experience, it's often the case that a small number of sites are responsible for a disproportionate amount of DB storage space</p>
</blockquote>
<p>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)</p>
<p>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".</p> CUNY Academic Commons - Feature #17522: Strategies for reducing database sizehttps://redmine.gc.cuny.edu/issues/17522?journal_id=786952023-04-14T19:16:56ZBoone Gorgesboone@gorg.es
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Duplicate</i></li></ul><p>I'm going to close this in favor of the more active #17761.</p>