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”?

Thanks! 🙂

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';

the _edit_lock is generated each time you edit a post or page. it consist the timecode and the user. so WordPress is knowing who is currently editing it.

meta_id     post_id     meta_key    meta_value
9           5           _edit_lock  1388386997:1

if you manipulate it WordPress reacts somehow sensitive …I tried to fetch how many seconds somebody worked on a post. All the time it broke my database loading time.

As you said you running this on a large multisite. Don’t know how many users write posts there, but it definitely could break the RAM of the server if to many people edit a post at the same time.

A solution could be:
get rid of _edit_lock

How to disable the “Post Lock/Edit Lock”?

Normally WordPress should have the “_edit_lock” one per Post. Some databases have the problem generating them every time.

Like this guy
http://wordpress.org/support/topic/can-i-remove-_edit_lock-_edit_last-from-wp_postmeta

His solution was to delete them all.
To speed it up you can delete them all every night at 3 o’clock in phpMyAdmin with

DELETE FROM `yourdb`.`wp_postmeta` WHERE `wp_postmeta`.`meta_key` = '_edit_lock'

might be you find a cron job doing exactly that.