13

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';
10
  • How many results do you get for SELECT * FROM wp_postmeta WHERE meta_key='_edit_lock'; ?
    – adrian7
    Feb 22, 2014 at 11:27
  • Thanks for your question, adrian7! There are 33k rows matching your query. I'm not familiar with WP's usage of the '_edit_lock' meta key. Is this abnormal?
    – rinogo
    Feb 24, 2014 at 18:09
  • it's not abnormal, wordpress uses it to alert users when they are trying to edit the same post/page. I suggest you delete all of the _edit_locks from the wp_postmeta, obviously when there's no one editing and check after any performance improvements. (BTW make a backup first).
    – adrian7
    Feb 25, 2014 at 12:13
  • 4
    Does it also take a huge amount of time when you just SELECT this entry? Like SELECT * FROM wp_postmeta` WHERE post_id = 94705 AND meta_key = '_edit_lock';`?
    – fischi
    Mar 5, 2014 at 0:17
  • @fischi: That query seems to take from 45-50ms, at least in the testing I just did a few moments ago. However, it's possible that it would occasionally take a very long time (e.g. up to 84 seconds, as shown in the mysqlsla output included in the question). I'll run a new round of slow query analysis to see if any of my recent changes to our configuration have affected the queries.
    – rinogo
    Mar 13, 2014 at 18:52

3 Answers 3

5

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.

0

try this :)

UPDATE `wp_postmeta` 
    SET `meta_value` = concat(unix_timestamp(),':386')  
    WHERE `post_id`  = 94705 
      AND `meta_key` = '_edit_lock';
0

Ok probably, the easy way is use WP method.

update_post_meta($post_id, '_edit_lock', time().':'.$user_id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.