search

wp-cli / ideas

💡 Ideas and feature requests are collected here
40 stars 1 forks source link

Clean up unused options in the database #2

Open danielbachhuber opened 7 years ago

danielbachhuber commented 7 years ago

To help website managers reduce the size of options automatically loaded on every request, it would be helpful to have a command that could show you which options can be safely deleted.

It would determine which options can be safely deleted based on a registry of plugins and their known options. If an option is present in the database and correlates with a plugin in the registry, but the plugin isn't installed, then we can consider it safe to delete.

We can build the registry of plugins and their options with WP-CLI's test harness. The test harness would install and activate the plugin, and then determine which options the plugin had added.

TheLastCicada commented 7 years ago

The registry of plugins and their options seems like a lot of work and a lot of maintenance, but it would be cool if it worked. For me, a place to start that would be nice is having a diagnostic command that simply reported on the number of rows in the options table and provided guidance on if this is dangerously high or not. I could even see this being a warning that is printed as output on every command that you run if it is over a certain threshold, like 10,000 rows or something, giving site owners an alert that something is wrong. I find it is common that the options table can totally out of control by the time performance gets bad enough for me to check the size.

danielbachhuber commented 7 years ago

The registry of plugins and their options seems like a lot of work and a lot of maintenance, but it would be cool if it worked.

Yeah, would need a lot of automation involved.

For me, a place to start that would be nice is having a diagnostic command that simply reported on the number of rows in the options table and provided guidance on if this is dangerously high or not. I could even see this being a warning that is printed as output on every command that you run if it is over a certain threshold, like 10,000 rows or something, giving site owners an alert that something is wrong.

See https://github.com/wp-cli/doctor-command/blob/master/doctor.yml#L2-L3

In addition to total size, I suppose we could include a count check as well.

TheLastCicada commented 7 years ago

In addition to autoload options being checked, I suggest we also check total number of options. While autoload options is likely the most common way for this to go wrong, I've seen big sites slow to a crawl when the total number of options reaches an insane amount (close to the millions usually). Since there's no index on the autoload field, just doing the query to return all autoload options takes forever since it does a full table scan. Once you get enough records in the options table, the query to get autoload options will be slow enough to be a performance drag.

joyously commented 7 years ago

I've used a plugin called Cleanup Options that does this. If you look in its readme file, you'll see how:

= Orphaned Options List = Listed Options are those that are found in the wp_options table but are not referenced by "get_option" or "get_settings" by any of the PHP files located within your blog directory. If you have deactivated plugins and/or non-used themes in your directory, the associated options will not be considered orphaned until the files are uninstalled.