[wp-trac] [WordPress Trac] #24044: Add index to wp_options to aid/improve performance

[WordPress Trac]

#24044: Add index to wp_options to aid/improve performance
--------------------------------+------------------------------
 Reporter:  DanBUK              |       Owner:
     Type:  enhancement         |      Status:  reopened
 Priority:  normal              |   Milestone:  Awaiting Review
Component:  Options, Meta APIs  |     Version:
 Severity:  major               |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+------------------------------

Comment (by jkhongusc):

 I am doing some post-op investigation after our outage.  It looks like
 there are multiple triggers to our failure and not due just to the lack of
 the autoload index.  I am trying to figure out two things:
 1) when is this query triggered - select option_name, option_value from
 wp_options where autoload = 'yes';
 2) why is the query being run multiple times? During our outage, there
 were hundreds of the query running.

 I think a high traffic site exacerbates this problem. Info on our install:
 - WP multisite
 - five 8 CPU 16GB RAM WP instances
 - one 8CPU 16GB database MySQL InnoDB
 - 100 WP requests per second during peak traffic.  These are dynamic
 requests, not static/assets
 - Our wp_options row count fluctuates in the 50-100k range, autoload='yes'
 count is in the 100s

 We are planning to add a caching layer, either Redis or memcached, which
 will increase our use of transients.  Will we run into a problem even with
 the autoload index when we have thousands of options autoloaded? I need to
 find an answer to my two questions above.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/24044#comment:62>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform