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.