WooCommerce and Coupon _used_by Logging

So in WooCommerce (as of 2.3.x) every use of a coupon means another post meta is added to that coupon with the key _used_by. For sites that offer a single coupon for all of the general public to use, this can cause some issues in extreme edge cases. In our specific case we ran into an issue where the meta of a single coupon totaled 78.5k rows (at the time the post meta table included over 15,000,000 rows). At random without any changes to the site code or server configurations the site immediately started having degraded performance.

After a large effort on our end and the L3 support at WPEngine, they were able to trace back the performance to a looping of queries to the wp_postmeta table but were not sure why. It was tracked back to this line of code within WooCommerce.

https://github.com/woothemes/woocommerce/blob/master/includes/class-wc-coupon.php#L178

get_post_meta ultimately down the line trigger update_meta_cache to be called. The SQL query to pull post meta does not include the meta_key, because WordPress is built in a way that it will attempt to instead just make a single SQL query and cache the entire result. So when you call get_post_meta multiple times in a row for a single post, only one SQL query is actually triggered. At WebDevStudios, Brian Richards and I actually used this to our advantage after some discussion on the best way to store data on a project.

In almost any other use case the use of a single SQL query to pull all post meta and cache it works perfectly fine.

However, if you are using Memcache as an object cache you should know that any single piece of data stored by default has a maximum of 1MB in size. Our post meta for this single coupon just hit that 1MB limit and was not cacheable by Memcache anymore.

So, when WC_Coupon->populate() loops through all the meta keys of the coupon calling get_post_meta on each to populate the object, instead of one SQL query for everything it hits the database for every single call. This caused quite a bit more stress on the database causing response times to go from 10–25ms to nearly 200ms, per query, with nearly 15 queries for this specific coupon as well.

Update: There is now a GitHub issue (#8482) opened to look into this.