[wp-trac] [WordPress Trac] #34848: Add support for updating post meta in bulk

[WordPress Trac]

#34848: Add support for updating post meta in bulk
------------------------------------------+-----------------------------
 Reporter:  patrickgarman                 |       Owner:  chriscct7
     Type:  enhancement                   |      Status:  assigned
 Priority:  normal                        |   Milestone:  Future Release
Component:  Options, Meta APIs            |     Version:
 Severity:  normal                        |  Resolution:
 Keywords:  needs-patch needs-unit-tests  |     Focuses:  performance
------------------------------------------+-----------------------------

Comment (by patrickgarman):

 I think I've mentioned before, and I'll preface again with "I'm not a DBA,
 this isn't my expertise, but based on the work I've been doing this is the
 experience I've had" line like usual.

 I've included some basic info around the experience I've had with reducing
 raw volume of inserts by combining them in point 3 on my comment here:
 https://github.com/woothemes/woocommerce/issues/9735#issuecomment-168032658

 tl;dr - went from 600 inserts per minute down to 200 inserts per minute
 (same volume of data, just combining inserts) and the query time itself
 went up on average of ~.1ms

 This data is from a high volume WooCommerce site, and New Relic is the
 tool used to pull this specific data. I'm assuming the tool here is
 accurate, I've not found or heard anything to believe it isn't accurate.
 To further expand on this... The reason I started at that time focusing on
 reducing the query volume is the volume of orders being inserted into the
 database (this was cyber monday) was causing the postmeta table to lock up
 and would bring the site to a halt. Database server loads were over 120
 (32 cpu, 120GB ram). Since then and after I implemented these changes (and
 nothing else specifically related to reducing postmeta related things)
 we've experienced even higher loads where we no longer were bottlenecked
 by the postmeta table in this way.

 I'll add here about transactions (again not being an expert here either)
 that WooCommerce uses transactions already. When an order is being created
 (see links below), this has caused issues at times under load. When the
 database server locks up like it has on occasion due to volume what I'm
 finding is the postmeta of an order gets inserted yet the record in the
 posts table will not exist. Now, in theory a postmeta will NEVER get
 inserted if the post itself doesn't get inserted, there isn't a post_id to
 be had in this case. Yet something is happening in that transaction to not
 insert the posts record yet does complete the postmeta records.

 https://github.com/woothemes/woocommerce/blob/master/includes/class-wc-
 checkout.php#L181
 https://github.com/woothemes/woocommerce/blob/master/includes/wc-core-
 functions.php#L1097

 I look forward to hearing more about the stats and maybe more about what
 someone who knows what they doing here thinks of the data, experiences,
 and issues I've run into.

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