Performance Benchmarking for WooCommerce HPOS

Hello everyone,

Since last year, we have been working on building and rolling out HPOS (High-Performance Order Storage) as an opt-in feature. This changes the way that we store the order data in the database, from custom post types in wp_post and wp_postmeta tables to custom tables that are dedicated to storing the order data. We have also published the detailed structure for these tables: High-Performance Order Storage: Database Schema

You can read about the motivation for this change in The Plan for the WooCommerce Custom Order Table.

Now, we are very excited to share with you the performance improvements that HPOS will bring, by sharing the results of performance tests that we have been doing. We will compare the performance of posts-based storage with HPOS for common functions and queries such as order creation, filtering, etc.

Setup and Methodology

We are going to run these tests on our test site which has about 400k orders and 30k products already. This site is running on WordPress.com’s business plan, which is billed at $25 monthly (at the time of this writing). This site has memcached caching enabled and installed (part of the host’s standard offering), but no other specific performance plugins are active on the site.

We are going to be running the queries in a WP shell, which means we are not taking the advantage of parallelism that is available from the host. In other words, we will be utilizing only a single worker, which is equivalent of doing everything in a single request, whereas the host actually provides many more workers in parallel, i.e. host can serve many web requests in parallel. This is to say that a site’s performance is much higher overall than our benchmarking will suggest here.

We have also disabled the HPOS <> Post sync, as enabling it will defeat the purpose of benchmarking, especially for order creation flows. You can read more about how sync works in High-Performance Order Storage: Backward Compatibility and Synchronization. We expect that eventually, shops will have the sync disabled permanently on their sites.

Additionally, the WP query cache Improvements to WP_Query performance in 6.1, which was introduced in WP 6.1 is also disabled, so that our queries actually hit the database.

The size of the wp_postmeta table on this site is about 2GB (with 1.4 million rows) and the size of the wp_posts table is about 240MB (600k rows).

Note that the version of WooCommerce used is a development version, built on the cot-main branch. This has some stability fixes that are currently not available in the trunk but should be available pretty soon (those patches are in review cycles).

Tests

We will test with some direct queries as they can trigger the system under testing most directly without noise from other parts of the request.

Order creation

Creating orders was identified as one of the primary goals for the project, by reducing the number of insert queries that we would have to do for creating an order. Here, we see about 5x improvement in order insert performance, primarily because we now need to run fewer insert queries since multiple meta fields are flattened in custom tables.

Benchmarking code

function benchmark_hpos_create_usage() {
        global $wpdb;
        $product_id = 502197;
        $count = 1000;

        $product = wc_get_product( $product_id );
        $label = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? 'HPOS' : 'Posts table';
        $time = microtime( true );
        for ( $i = 0; $i < $count; $i++ ) {
                $order = new WC_Order();
                $order->set_billing_first_name( 'John' );
                $order->set_billing_address_2( '123' );
                $order->add_product( $product );
                $order->save();
                assert( $order->get_id() > 0 );
        }
        $time_taken = microtime( true ) - $time;
        echo "[$label] " . 'Time to create ' . $count . ' orders: ' . $time_taken . 's';
}

Results

[HPOS] Time to create 1000 orders: 15.181570053101s

[Posts table] Time to create 1000 orders: 78.124469995499s

Order checkout

Similar to the above, we also expect an increase in checkout performance. Note that insert performance does not directly translate to the checkout performance, since the latter involves various data verification and triggers (such as inventory management, various do_action and apply_filter hooks, user verification, etc).

Even so, we see an improvement of about 1.5x in the checkout (for simple checkouts with one product) when using HPOS:

Benchmarking code

function hpos_benchmark_process_checkout() {
    $label = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? 'HPOS' : 'Posts table';
    wc_load_cart();
    $product = wc_get_product( 502197 );
 
    add_filter( 'woocommerce_payment_successful_result', function ( $result, $order_id ) {
        assert( $order_id > 0 );
        throw new Exception( 'Order created: ' . $order_id );
    }, 10, 3 );
 
    $_POST['terms'] = 1;
    $_POST['terms-field'] = 1;
    $_POST['createaccount'] = 1;
    $_POST['payment_method'] = 'cod';
    $_POST['billing_first_name'] = 'John';
    $_POST['billing_last_name'] = 'Doe';
    $_POST['billing_company'] = 'Company';
    $_POST['billing_address_1'] = '123 Main St';
    $_POST['billing_address_2'] = '';
    $_POST['billing_city'] = 'San Francisco';
    $_POST['billing_state'] = 'CA';
    $_POST['billing_postcode'] = '94107';
    $_POST['billing_country'] = 'US';
    $_POST['billing_email'] = 'test_' . time() . '@example.com';
    $_POST['billing_phone'] = '555-555-5555';
    $_POST['ship_to_different_address'] = 0;
 
    add_action( 'woocommerce_after_checkout_validation', function ( $data, $errors ) {
        if ( $errors->get_error_messages() ) {
            print_r( implode( $errors->get_error_messages() ) );
            throw new Exception( 'Validation failed: ' . implode( ', ', $errors->get_error_messages() ) );
        }
    }, 10, 2 );
 
    $time_taken = 0;
    for ( $i = 0; $i < 10; $i++ ) {
        wc_empty_cart( true );
        $checkout_nonce = wp_create_nonce( 'woocommerce-process_checkout' );
        $_REQUEST['woocommerce-process-checkout-nonce'] = $checkout_nonce;
        wc_clear_notices();
        WC()->cart->add_to_cart( $product->get_id(), 1 );
        benchmark_checkout( $time_taken );
    }
    wc_maybe_define_constant( 'DOING_AJAX', false );
    echo "[$label] " . 'Time to process 10 checkouts: ' . $time_taken . 's';
 
}
 
function benchmark_checkout( &$time_taken ) {
    $time = microtime( true );
    try {
        WC()->checkout()->process_checkout();
    } catch ( Exception $e ) {
        echo $e->getMessage();
    }
    $time_taken = $time_taken + ( microtime( true ) - $time );
}

Results

[HPOS] Time to process 10 checkouts: 0.99165391921997s

[Posts table] Time to process 10 checkouts: 1.5086543560028s

Search orders using metadata

In the HPOS project, we also have the freedom to implement indexes on the order_meta table, which we didn’t have for the post table. As you would expect, adding indexes speeds up the meta query lookup, about 10x in the below benchmarking code. On the other hand, additional indexes decrease the insert speed, but that’s should be offset by the insert performance increase from flattening, as noted in the previous tests.

Benchmarking code

function benchmark_hpos_metadata() {
    $label = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? 'HPOS' : 'Posts table';
 
    $time = microtime( true );
 
    $query = new WC_Order_Query(
        array(
            'limit'      => 1000,
            'return'     => 'ids',
            'orderby'    => 'id',
            'order'      => 'DESC',
            'billing_address_index' => 'John    123      ',
            'meta_query' => array(
                array(
                    'key'     => '_billing_address_index',
                    'value'   => 'John    123      ',
                    'compare' => '=',
                ),
            ),
        )
    );
 
    $orders     = $query->get_orders();
    $time_taken = microtime( true ) - $time;
    assert( count( $orders ) === 1000 );
    echo "[$label] " . 'Time to search ' . count( $orders ) . ' orders by metadata: ' . $time_taken . 's';
}

Result

[HPOS] Time to search 1000 orders by metadata: 0.052868127822876s

[Posts table] Time to search 1000 orders by metadata: 0.63891506195068s

Filtering by an indexed column

Now that we have flattened several post_meta keys into their own columns, we are also taking advantage of indexes on individual meta_keys where relevant. One of these fields customer_id has its own indexed columns, whereas, in the post table, this was stored in the _customer_user meta key. In HPOS, this query to get all orders for a customer is now 40x faster since it can use the index for the customer_id column.

Benchmarking code

function benchmark_hpos_filter_customer() {
    $label = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? 'HPOS' : 'Posts table';
    $time = microtime( true );
    $query = new WC_Order_Query(
        array(
            'limit' => 1000,
            'return' => 'ids',
            'customer_id' => 56902779,
        )
    );
    $orders = $query->get_orders();
 
    $time_taken = microtime( true ) - $time;
    echo "[$label] " . 'Time to filter ' . count( $orders ) . ' orders for customer: ' . $time_taken . 's' . PHP_EOL;
}

Result

[HPOS] Time to filter 1000 orders for customer: 0.015961170196533s

[Posts table] Time to filter 1000 orders for customer: 0.59882402420044s

Search orders using non-indexed column

Similar to the above, some of the flattened columns are not indexed. However, we still expect some performance improvement since the search area is still reduced from the entire wp_postmeta table earlier, to only a single entry per order. In the below query, this brings us improvements of up to 3x for our database.

Benchmarking code

function benchmark_hpos_search_non_index_usage() {
    $label = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? 'HPOS' : 'Posts table';
 
    $time = microtime( true );
    if ( 'HPOS' === $label ) {
        $query = new WC_Order_Query(
            array(
                'limit'       => 1000,
                'return'      => 'ids',
                'orderby'     => 'id',
                'order'       => 'DESC',
                'field_query' => array(
                    array(
                        'field' => 'created_via',
                        'value' => 'checkout',
                    )
                )
            )
        );
    } else {
        $query = new WC_Order_Query(
            array(
                'limit'       => 1000,
                'return'      => 'ids',
                'orderby'     => 'id',
                'order'       => 'DESC',
                'created_via' => 'checkout'
 
            )
        );
    }
    $orders = $query->get_orders();
    assert( count( $orders ) === 1000 );
    $time_taken = microtime( true ) - $time;
    echo "[$label] " . 'Time to search orders: ' . $time_taken . 's' . PHP_EOL;
}

Result

[HPOS] Time to search orders: 0.29565596580505s

[Posts table] Time to search orders: 1.0048348903656s

Database size

With HPOS, we are technically sharding the post-meta table by moving out all the entries related to orders. We expect that, eventually, site administrators will be able to remove existing post meta rows belonging to orders, thereby increasing site performance outside of orders workflow (since less table size implies fewer rows for MySQL to scan during queries).

For instance, in woocommerce.com, which is a large deployment, orders are about 81% of all post records. In the wp_postmeta table this skew is even more, meta records belonging to orders are about 97% of all rows. If we were to remove post meta records belonging to orders (which we plan to do after HPOS has been running for a while), woocommerce.com would see the post meta table data reduction of 97%. We expect this size reduction to improve overall site performance and not be limited to orders.

(Note: the work to run woocommerce.com with HPOS is in progress).

Conclusion

As you can see, HPOS improves the performance of various order-related flows, with the potential to increase general site performance by a lot. We encourage all plugin developers to upgrade their plugins to support HPOS so that merchants in the ecosystem can take advantage of this project. You can use the upgrade guide to support HPOS or feel free to pop by in the #upgrade-party channel in WooCommerce slack (Join WooCommerce on Slack) and we would be happy to assist you as you prepare your plugins for what will soon (August 2023) be the default experience.

Next steps

Similar to query benchmarks, we are also running tests for web request performance and plan to publish the results soon.

Please don’t hesitate to post in the comments if you have further questions about these benchmarks, or would like us to benchmark other aspects of the project that we have missed.