Why are simple updates to wp_postmeta’s “_edit_lock” so slow?
In our MySQL slow query log, the cumulatively slowest query is a simple update to wp_postmeta. Here’s an example:
UPDATE `wp_postmeta` SET `meta_value` = '1392835505:386' WHERE `post_id` = 94705 AND `meta_key` = '_edit_lock';
Relevant details on our setup:
- MySQL slow query time set to 1s
- wp_postmeta’s storage engine is InnoDB
- Running within a large Multisite installation with tens of thousands of posts on the main WP blog (where these slow queries are occurring)
- High activity in the WP admin area (lots of writers/editors working concurrently, but generally on their own (not others’) content)
- Low activity on the public side of WP (not actually serving up content from the main blog)
- The slow queries seem to all be using the “_edit_lock” key; queries of the same format (that use a key other than “_edit_lock”) do not seem to be slow.
Why is it that this is the slowest query on our system? Does it have something to do with WP’s specific use of “edit locks”?
Update: Output from mysqlsla below:
______________________________________________________________________ 001 ___ Count : 606 (16.83%) Time : 2257.760468 s total, 3.725677 s avg, 1.00512 s to 84.645869 s max (20.60%) 95% of Time : 1355.289277 s total, 2.357025 s avg, 1.00512 s to 12.343604 s max Lock Time (s) : 182.502 ms total, 301 μs avg, 29 μs to 157.542 ms max (0.21%) 95% of Lock : 22.882 ms total, 40 μs avg, 29 μs to 57 μs max Rows sent : 0 avg, 0 to 0 max (0.00%) Rows examined : 1 avg, 1 to 2 max (0.00%) Database : xxx_wp Users : xxx_wp@localhost : 98.84% (599) of query, 51.03% (1837) of all users yyy_wp@localhost : 1.16% (7) of query, 0.94% (34) of all users Query abstract: SET timestamp=N; UPDATE wp_postmeta SET meta_value = 'S' WHERE post_id = N AND meta_key = 'S'; Query sample: SET timestamp=1392835506; UPDATE `wp_postmeta` SET `meta_value` = '1392835505:386' WHERE `post_id` = 94705 AND `meta_key` = '_edit_lock';