Skip to:
Content

bbPress.org

Opened 13 years ago

Closed 11 years ago

Last modified 10 years ago

#995 closed defect (bug) (wontfix)

performance design flaw in bb_update_meta

Reported by: _ck_ Owned by:
Milestone: 1.1 Priority: normal
Severity: normal Version:
Component: Back-end Keywords:
Cc:

Description

There is an inherited, legacy performance issue in bb_update_meta that causes excessive queries when meta is updated in the db.

There is a failed logic problem that does a SELECT query for the row about to be updated to determine UPDATE/INSERT instead of using ON DUPLICATE KEY, with a key in the table on object_id + meta_key.

Originally I thought maybe meta didn't have a key like that because it was designed on purpose to tolerate multiple meta_keys with the same name and the same object_id. But this makes no sense since bb_update_meta will force all the same object_id + meta_keys to have the same data. So it's pointless, you can't have different data.

The problem with this is, it allows corruption of the meta by accidental creation of multiple rows with the same object_id + meta_key.

BUT the data MUST ALWAYS be the same in such duplicate rows, because update meta does the SELECT query and will then before an UPDATE, with LIMIT to affect all the rows.

The table will then just keep the extra duplicate rows, and the SELECT in the update meta happens over, and over and over again.

This design flaw in update meta causes the queries to double for every plugin that uses it and wastes the mysql cache.

I'd like to see this addressed even in 0.9 as a patch, with a routine to first check the meta tables for duplicates, remove the duplicates, then add the table key index, then replace the bb_update_meta routine to use INSERT ON DUPLICATE KEY instead of SELECT/INSERT/UPDATE.

Change History (9)

#1 @_ck_
13 years ago

Oh and to make matters worse, updating meta (at least topic meta) causes not only the above two queries to execute, but in addition it causes the topic and then the topic meta to re-load, for reasons I cannot fathom.

So a single topic_updatemeta causes no less than FOUR queries.

  1. SELECT (to find any existing meta rows to either INSERT or UPDATE)
  2. INSERT (or UPDATE, the meta)
  3. SELECT (re-load the topic)
  4. SELECT (re-load the topic meta)

That's insane.
There should be a table key, an INSERT ON DUPLICATE KEY and the redundant topic re-load should be avoided. I believe it's to re-load the object cache, which should be modified in memory directly by the data it was passed instead.

#2 @sambauers
13 years ago

INSERT ... ON DUPLICATE KEY may be an option if we decide not to do anything with the idea in #629, basically doing this closes that door pretty much completely. I'm happy to have a conversation about the merits of that other ticket as well.

The second part (from the previous comment) about reloading the topic is quite necessary I think. Firstly, updates don't happen all that often and secondly, the only sure fire way to make sure your cache matches your database after an update is to read from the database again. Considering that topics and their meta are stored as one object in the object cache, the rereading is required unless we create some way to selectively update parts of the cached objects. But since the object cache is fairly generic and... well... dumb, it doesn't have anything like that. In the end rereading is a lot easier from a maintenance of code perspective.

#3 @_ck_
13 years ago

Does WordPress really work that way? If so, that's insane because it's a tremendous performance hit to not have a mysql key on the meta_key and just serialize the data inside if it's an array.

To update a specific value in such a pseudo array using multiple rows, a plugin author would have to specify the old value and the new value, otherwise all values for the meta_key would be replaced (and duplicated). Is that functionality even in wordpress's update meta? I am not familiar with the newest versions but old versions didn't have it.

#4 @(none)
13 years ago

  • Milestone 1.0-beta deleted

Milestone 1.0-beta deleted

#5 @sambauers
13 years ago

  • Milestone set to 1.0

#6 @sambauers
12 years ago

  • Milestone changed from 1.0 to 1.5

#7 @johnjamesjacoby
11 years ago

Sadly, WordPress suffers this exact same fate, so the bbPress plugin for WordPress does not see an improvement in this area. Closing as wontfix since changing the way meta queries work is highly unlikely due to stand-alone relying on BackPress, and the plugin relying on WordPress.

#8 @johnjamesjacoby
11 years ago

  • Resolution set to wontfix
  • Status changed from new to closed

#9 @johnjamesjacoby
10 years ago

  • Milestone changed from Future Release to 1.1
  • Version 0.9.0.2 deleted
Note: See TracTickets for help on using tickets.