id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
17210,Massive duplication of oEmbed postmeta,archon810,Viper007Bond,"Hey guys,

Ever since my blog grew to a considerable size (a few million PVs a month) and started slowing down and exploding my server, I've been looking and implementing various optimizations. During one such passes through the data, I noticed this really weird oEmbed related behavior, which I've been observing for a number of WP version upgrades.

I use [embed] shortcodes a lot, and every new post after a few minutes ends up with a ton of oembed caches that don't belong to it at all - they're all from other posts. Posts that don't even have [embed]s at all still have over 100 oembed entries in wp_postmeta.

Here's an example of just a small subset of data residing in the table:

[[Image(http://farm6.static.flickr.com/5230/5641419581_0610c9e267_b.jpg)]]

There are now about 150,000 entries in the wp_postmeta table due to this, half of which are duplicated _oembed entries, which I think has heavy impact on server load. Not only that but I'm sure WP is filling the table up with values by redoing oEmbed queries, which may explain that load shoots up very high at times when publishing.
{{{
mysql> select count(*) from wp_postmeta where meta_key like '_oembed%';
+----------+
| count(*) |
+----------+
|    81499 |
+----------+
1 row in set

mysql> select count(*) from wp_postmeta;
+----------+
| count(*) |
+----------+
|   148451 |
+----------+
1 row in set
}}}

Just look at how many times this random video embed value shows up in the table. I'm sure it was used in only one actual post:
{{{
mysql> select count(*) from wp_postmeta where meta_value like '%p2oWELcd-lI%';
+----------+
| count(*) |
+----------+
|      815 |
+----------+
1 row in set
}}}

Just to clarify - I don't have 815 updates to a single post that may have explained this - these are completely unrelated, separate, published posts.

To put things in perspective, here are the top 20 offenders:
{{{
mysql> select distinct meta_key, count(*) as cnt from wp_postmeta where meta_key like '_oembed%' group by meta_key order by cnt desc limit 20;
+------------------------------------------+-----+
| meta_key                                 | cnt |
+------------------------------------------+-----+
| _oembed_5607e41abb700707540a854ae76182cf | 864 |
| _oembed_984bc07d3bc0f61b6b35230cd2fa7ced | 859 |
| _oembed_da8ae36275b4576cfcd92c0ed455be96 | 859 |
| _oembed_71dd4068a9a6911f50dbe57b3ff477c5 | 858 |
| _oembed_9f817e820c23ccbfac9b22b3474e5dd3 | 858 |
| _oembed_f3c1c03a81bc301b5f1a063f65119328 | 857 |
| _oembed_31bf10d95cb7c8e9f646d9d6e5728da0 | 857 |
| _oembed_25d0ebf59c994050cb604900cf04f53f | 856 |
| _oembed_6265dae657e38579c0a8ddb66132d526 | 852 |
| _oembed_562dd8c13888905cbd15dbd74e8699cc | 849 |
| _oembed_30ea17d1cc73acd925a74373d2be32ec | 848 |
| _oembed_87f16916b4da6571f454266bfbfaebe0 | 847 |
| _oembed_9f1f038d43e973bd60929201eee24f57 | 843 |
| _oembed_d46317d44fe11c0d90ef2cc3b45bce57 | 843 |
| _oembed_b1f8685ba405feee46baf9408eb632f7 | 841 |
| _oembed_1b56f492eba4c4ea698d816d0ecf2d51 | 840 |
| _oembed_fe597714de4081e6e7e78a88256c7db4 | 840 |
| _oembed_fb843e7b604cbc4e1ffa144d4eb300c8 | 839 |
| _oembed_97b12f2f1e59ee6eff95c61095aa5bef | 838 |
| _oembed_2b94d9f7c28ee37bfbead0a622c8be85 | 838 |
+------------------------------------------+-----+
20 rows in set
}}}

I'm quite at a loss here and would appreciate the next debugging steps. I haven't been able to determine where things are going wrong on my own.

Thank you.

P.S. The site in question is AndroidPolice.com",defect (bug),reopened,normal,Future Release,Embeds,3.1,normal,,dev-feedback needs-patch,admin@… Denis-de-Bernardy xoodrew@… alanpae