wp_options: autoload — performance and memory use

[pdclark]

Re: https://github.com/ataylorme/WordPress-Hosting-Best-Practices-Documentation/pull/35#discussion_r201106099

"Consider talking about autoload and Memcached" / "can we move this to an issue and get rid of inline TODO comments?"

Discuss wp_options autoload setting. Considerations on performance (query time) and memory use (PHP and object cache memory limits vs. MySQL longtext memory limits)

For example:

• update_option(), autoload defaults to yes|true
• When autoload is yes, wp_load_alloptions() loads these values into memory, either from MySQL or from object cache
• Without object cache, each WordPress page load runs a query of wp_options, loading all autoload options into memory from MySQL.
  • Consider adding an index to the wp_options autoload field, as without an index, query time loads linearly with table size. 10up: wp-options-table Trac: 24044#comment:9
• With object cache (memcached, redis, APCu, etc.), wp_load_alloptions() should persist data across page loads.
  • This speeds access, but Object Cache memory limits must be scaled or taken into consideration. For example, memcached may place a 1MB limit on individual values, but wp_options> value is longtext, which may contain values up to 4GB stackexchange wp-admin/includes/schema.php#L144 MySQL longtext
• Hosts: Consider maximum memory available for all combined options.
  • Given caching situation above, Options Of Unusual Size are not recommended.
  • Total autoload size can be checked with SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_options WHERE autoload='yes'; kinsta.com
• Developers: Consider benefits of having options cached by default (no additional SQL queries), vs. loading values into RAM on all page loads. Is the value likely to be used on every page load? If not — it may be fine to set autoload to false, allowing the value to be cached by MySQL, another cache, etc.
  • Inconsistencies may occur if plugins modify options outside the core Options API.

[getsource]

I like this.