Performance improvements in 3.6

The upcoming release of 3.6 contains a plethora of performance enhancements that improve both admin and frontend page load times. This post summarises some of the smaller changes, and explains the reasoning behind some of the larger changes and how they work.

If you’re keen on testing any of this before 3.6 drops, you can test out the release candidate here.

Product Data Lookup Tables

Post meta is super flexible, but not that efficient when we need to sort or filter by many meta values at once. This is mostly down to the need to do multiple joins between the wp_postmeta and wp_posts tables

Something our team has been looking at is moving data from post meta to custom tables to mitigate this. We actually have a feature plugin in progress to do just that, but it does introduce challenges such as backwards compatibility so it’s a long term project. To ship some improvements short term we’ve looked at an alternative approach…lookup tables.

Lookup tables can be tailored to the needs of the application, making data available in a structured, efficient format, with correct indexes, without needing to move data out of wp_postmeta. Data is formatted and synced to the lookup table when a product is updated.

The new product lookup table contains data we use for searching, sorting, and filtering, and the schema is as follows:

  `product_id` bigint(20) NOT NULL,
  `sku` varchar(100) NULL default '',
  `virtual` tinyint(1) NULL default 0,
  `downloadable` tinyint(1) NULL default 0,
  `min_price` decimal(10,2) NULL default NULL,
  `max_price` decimal(10,2) NULL default NULL,
  `onsale` tinyint(1) NULL default 0,
  `stock_quantity` double NULL default NULL,
  `stock_status` varchar(100) NULL default 'instock',
  `rating_count` bigint(20) NULL default 0,
  `average_rating` decimal(3,2) NULL default 0.00,
  `total_sales` bigint(20) NULL default 0,

The new lookup table is used by joining it to the main posts table (product_id maps to post id) when running queries.

As an example, frontend sorting and filtering prior to 3.6 the queries was done by joining the wp_postmeta table to the wp_posts table and using ORDER BY and WHERE clauses on that data. Now we simply join to the new lookup table instead which should be more efficient.

This can be useful during searches or when sorting by meta. Examples where this can be useful include:

  • Stock reports
  • Dashboard reports
  • Sorting by X on the frontend
  • Sorting by X in admin
  • Searching by SKU in the backend
  • Filtering by price

ref: https://github.com/woocommerce/woocommerce/pull/22718

Updating the Lookup Table

The data stores handle updating of lookup tables when data changes. WC_Data_Store_WP has 4 new methods which are extended by other data stores:

  • get_data_for_lookup_table – Returns an array of data to store in a lookup table, by name
  • get_primary_key_for_lookup_table – Returns the primary key of the lookup table, by name
  • update_lookup_table – Updates a row, by ID, for a named table. It uses get_data_for_lookup_table and get_primary_key_for_lookup_table to gather updated data.
  • delete_from_lookup_table – Removes a row from a lookup table.

Updates are done only when data is updated, for example, the product data store monitors all changes to products:

if ( array_intersect( $this->updated_props, array( 'sku', 'regular_price', 'sale_price', 'date_on_sale_from', 'date_on_sale_to', 'total_sales', 'average_rating', 'stock_quantity', 'stock_status', 'manage_stock', 'downloadable', 'virtual' ) ) ) {
    $this->update_lookup_table( $product->get_id(), 'wc_product_meta_lookup' );
}

As long as plugins/extensions use the CRUD system, the data stores will keep things in sync.

Initial population of the data store is done via an update routine which calls a new function named wc_update_product_lookup_tables(). This queues a bunch of updates using Action Scheduler to populate the tables in stages.

The product table can also be forced to update via a tool in system status, WooCommerce > Status > Tools > Product lookup tables, or using a CLI command which does the updates all at once:

wp wc tool run regenerate_product_lookup_tables --user=1

This process can take a while depending on the number of products, but once updated the sync only does updates/deletes.

Transient Invalidation

WooCommerce 3.5 and below had a transient versioning system which would invalidate data and clean up expired transients. In this system, the transients names were given a ‘version’ suffix to allow them to be invalidated all at once. Cron jobs cleaned up expired transients after invalidation.

The drawbacks of this included:

  • Hundreds of cron jobs being created when updating many products at once, for example, when generating or importing products
  • The cronjobs, when ran, would slow down other pages due to load
  • Large number of cleanups were needed after any form of cache invalidation
  • Cleanups were difficult because the transient names are unknown (they were appended with a random suffix) so this had to be done with a custom SQL query.

To fix this, we’ve introduced a new method of handling versioned transients. Instead of changing transient names and invalidating transient data by name, we store transients as an array with this structure:

array(
    'version' => 12345,
    'data'    => ....
)

When the transient is read, if the version is outdated, the transient is regenerated. This allows us to invalidate cache without expensive cleanup operations.

ref: https://github.com/woocommerce/woocommerce/pull/22511

REST API Initialisation

In 3.5 and earlier, the REST API was loaded on each page. To improve performance we’re now only including and initialising the REST API during the rest_api_init hook.

ref: https://github.com/woocommerce/woocommerce/pull/22615

Similarly, to speed up the API request itself, frontend/theme code is no longer loaded during REST requests.

ref: https://github.com/woocommerce/woocommerce/pull/21090

To ensure this was not a breaking change, specifically for plugins extending the WooCommerce REST API or using it’s classes directly, the WC autoloader will include all REST API classes if used. If this occurs a warning will be logged or displayed if WP_DEBUG_DISPLAY is enabled.

We recommend other plugins switch to using the rest_api_init hook to prevent performance issues.

Loading Active Webhooks

WooCommerce creates instances of the WC_Webhook class to listen out for events and send requests out when specific events are triggered. Each webhook has its own instance.

Because there is no limit on the number of webhooks a user can add this can create bottlenecks if many webhooks exist, even if the webhooks are not in use. Webhooks are loaded regardless of status in 3.5.x.

WooCommerce 3.6 will only load active webhooks. Other webhooks (i.e. those that are disabled or paused) will no longer be loaded. This reduces the load on stores with inactive webhooks.

ref: https://github.com/woocommerce/woocommerce/pull/22760

Caching Improvements

These improvements introduce new caches, and improve existing caches, to speed up database read times if the same query is run multiple times on the same page, or on future page loads if using dedicated object caching.

  • Prime caches when reading variations (#22587) – Priming caches reduces the number of queries by bulk-selecting all data that will be needed later on.
  • Use day, not time, in status widget for caching (#22950) – This increases the likelihood of a cache being available by caching data for longer.
  • Added caching to System Status Report (#22887) – Adds transient caches for expensive remote posts on the status report so it doesn’t retest as often.
  • Add caching to attribute functions (#22938) – Caches global attribute data to prevent multiple DB reads or looping over arrays of attributes.
  • Improved caching of template loader, product types, and layered navigation widget (#22612)

Query Improvements

These improvements increase the speed of queries, or reduce the number of queries per page load.

  • Delete empty meta on update in data-stores (#22773) – This change will remove ’empty’ meta values, rather than keep them in the database. Thus reducing the amount of meta stored in total.
  • Don’t query for an order count unnecessarily (#22984) – Prevents a query if the order count is not needed for the main menu in admin, either because of permissions, or if removed intentionally.
  • Index on the downloadable_product_permissions table to improve speed when fetching downloads for customers (#22906) – A new index on this table speeds up lookups of download permissions on stores with many downloads/customers.
  • Improve slow term ordering by using queries instead of PHP (#22570, #22570) – The previous queries grabbed all terms then sorted them with PHP code/loops which used memory and was slower than pure SQL queries that are now in 3.6.
  • Improved speed of the find_matching_product_variation variation lookup function (#22423) – This is used when a product has a large number of variations to find matches based on selections.

Miscellaneous Improvements

  • Remove external lookup of IP addresses on localhost (#22608) – A slow lookup was performed to get the IP address of the user when on localhost. Since this only affects those testing locally, this bottleneck has been removed.
  • Optimized product CSS class functions to avoid multiple product reads and duplication (#22610) – The CSS class name generation for products was terribly slow and inefficient and did lots of logic twice. This is now improved.
  • Image regeneration edge cases (#22818) – Some sizes of images, in combination with image size settings, lead to images being regenerated on every page load. This is now resolved.
  • Remove unused legacy ‘woocommerce_lock_down_admin’ option and use filter only instead (#17796) – This option has not existed for some time so removing it removes a query per page load.
  • Removes legacy woocomerce_termmeta table and swap out all calls to legacy term meta functions to the new WP ones (#22570) – This table has not been needed since WP 4.4.0. Removing the checks for it prevents unnecessary conditionals.

Benchmarking the Improvements

To measure the above improvements we wrote a script which would hit various frontend/admin endpoints and time them, making sure caches were cleared between each run. These were then loaded into a spreadsheet to calculate averages and generate some charts.

The tests were ran on WooCommerce 3.6 RC1, 3.5.7, and 3.4.7 to see the change over time, and we tested with 3 data sets:

  • small (20 products from sample data)
  • medium (1000 products generated with wc-smooth-generator)
  • large (25k products generated again with wc-smooth-generator).

This was to ensure changes such as the new lookup table improved the performance of large stores without negatively impacting smaller stores which may not be facing problems.

We tested mostly product-related endpoints since the majority of performance improvements in 3.6 targeted them. These included:

  • Shop page
    • Main shop page
    • sorting by price/reviews/sales
    • filtering by price
  • Stock reports (low, out of stock, most stocked)
  • Admin dashboard (which shows stock reports)
  • Product list in admin

Each test was ran 10 times and used to calculate an average.

Results

On the small store, the results were almost identical between each version of WooCommerce. With such as small data set, this was expected. Postmeta copes fine with small amounts of data.

The medium store also had similar results, however, the SKU searching and price filtering did show some positive improvement due to the lookup table:

The large store, which had 25k products and 50k variations, showed the benefits of the lookup table more clearly, showing improvements of up to 62% between 3.5 and 3.6. This chart shows the frontend requests we measured (results in seconds):

On the admin side, the performance improvements were more substantial, particularly when reporting or dealing with SKUs:

For example, the SKU search above is a fulltext search which on the large data set took ~40 seconds. With the lookup table, results were matched in under 1 second.

What’s Next?

The above changes are shipping in April 2019 inside WC 3.6 which is now in release candidate stage. We hope the performance improvements help you and your clients. This is an ongoing process and we’re continually looking at ways to improve performance further, so stay tuned to the dev blog to find out these changes are coming.

If you have suggestions or feedback about the work that has been carried out so far, please let us know in the comments or on GitHub.


17 responses to “Performance improvements in 3.6”

  1. Hi, this is realy interesting and necessary. In fact, i use WordPress and WooCommerce since 2 years, and I saw the performance deteriorate as I added new products.I beleive in this project and your team to make WooCommerce better than ever. And I think the performances are not to be taken lightly, so continue like this, it’s good work.

  2. simbahosting Avatar
    simbahosting

    Sounds good!

    > “On the small store, the results were almost identical between each version of WooCommerce. With such as small data set, this was expected. Postmeta copes fine with small amounts of data.”

    How much non-product stuff was in the postmeta table? Could there still be a performance boost for stores with small numbers of *products*, but large numbers of other things (e.g. orders) ?

    1. That is definitely a possibility but not something we benchmarked for this post. The small install was sample product data only on a clean install.

      We could have theoretically created a tonne of bogus postmeta to test that out. I’m pretty sure that yes, the lookup table would perform better 🙂

  3. Flash question: how does one use the new lookup table, when a product has more prices than the “standard” ones? For example, if a product has a custom price added to it, or if it has to be filtered by “discounted price” (which has to be calculated on the fly), how does one do that?

    1. Hi Diego

      The lookup table is not used to store truthful prices for use on product pages for example, so any price logic is going to continue to work as-is.

      What the lookup table does is take _price and store the min _price and max _price to the lookup table for the purposes of sorting and filtering. So if your custom code works today, it will work with 3.6. Likewise, if your custom code does not take care of sorting and filtering today, nothing changes for you.

      If your plugin implements custom sorting and filtering to support these prices, that code will need to be left in place and will not take advantage of the lookup table.

      Hope that makes sense.

  4. Great! Keep up the good work.
    WooCommerce <3

    Note: generating graphs with descending versions maybe can be a little unnatural. On first look the feeling is the performance is decreasing.

    1. Thanks!

      I already changed the charts after someone complained in one of the groups 😀

      Is it just me, but shouldn’t these versions be going from left to right and increasing in #, not right to left? SMH

      Nevermind <3

  5. Wonderm00n Avatar
    Wonderm00n

    WPML / WooCommerce Multilingual does not play well with this :-/ the shop page and the products page on wp-admin times out.

    1. Probably they need to do some update in order to work with this new table.

      1. Wonderm00n Avatar
        Wonderm00n

        They did already 🙂

  6. Hi,

    I am experiencing an error after updating to WC 3.6.1 – Can anyone assist on how to resolve this error please?

    Below error message:

    ‘Classes that extend the WooCommerce/WordPress REST API should only be loaded during the rest_api_init action, or should call WC()->api->rest_api_includes() manually.’

    1. Some of your plugins or even your theme is registering REST API endpoints before rest_api_init, and this need to be updated in the plugin’s or theme’s code. Please contact the plugin’s/theme’s author.

  7. I hope the custom table for the products becomes part of the core as soon as possible 🙂

  8. You choose not to show my comment from 1-2 weeks ago? 🙁 Why? It is important.

    1. I can find any other comment, we took a few time focusing on the fixes. Maybe got sent to Spam, this happens sometimes, but I can’t find, if you like to ask again I’ll be happy to reply.

  9. scmsteve Avatar
    scmsteve

    I know this is a little late, but since we updated to 3.6 (we went from 3.5.8 to 3.6.2), webhooks using WP REST API Integration V2 are failing. A few that are still using Legacy API v3 are not failing.

    These are triggered from subscription creation/update events, and scheduled via Action Scheduler. They fail with this:

    unexpected shutdown: PHP Fatal error Class ‘SkyVergeWooCommerceMembershipsAPIControllerMembership_Plans’ not found in /home/simplych/public_html/wp-content/plugins/woocommerce-memberships/includes/api/v2/class-wc-memberships-rest-api-v2-membership-plans.php on line 36

    I can open a WP CLI shell and call the callback function and it completes successfully, but they are always failing with the above when scheduled.

    Is this possibly related to the WC 3.6.x changes? I have tried to eliminate all other things that may have changed yet the problem remains.

    1. This error is not from WooCommerce, it’s coming from the Memberships plugin, so please report it in the appropriate support channel: https://woo.com/my-account/create-a-ticket/

Leave a Reply

Your email address will not be published. Required fields are marked *