DB issues with WPMU

[Owen Taylor <otaylor redhat com>]

So, we were having troubles earlier today with all mysql 100 connections
on button.gnome.org being in use, and RT and blogs.gnome.org timing out
because they couldn't get an available connection.

Looking at the slow query log showed lots of queries like:

# User Host: wpmu[wpmu] @ window-back [172.31.1.13]
# Query_time: 187  Lock_time: 0  Rows_sent: 182  Rows_examined: 213
SELECT option_name, option_value FROM wp_1_options WHERE autoload = 'yes';

Note that the lock time is big but the query_time is also big. (The
lock time seems to come from queries like this blocking updates to the same 
table, which then block other queries.)

Looking at the contents of the table revealed two verry different types of rows:

 autoload = yes - actual wordpress options
 autoload = no  - caches of rss feed contents - with values many k big

So it seemed possible that even though that most of the rows in the
table were autoload = yes, adding an index on autoload might allow those
rows to be selected much faster.

I did:

 create index wp_1_options_autoload on wp_1_options (autoload);

And sure enough 'show processlist' suddenly went from 90+ active
connections to just a couple.

(we are still getting some load spikes on button.gnome.org that don't
show up obviously 'show processlist' - I wonder if this is just
tons of tiny little queries that don't take time individually but
cause load in aggregate.)

Even with the index added, caching feed data in the options table seems
weird and perverse. I wonder if it is expected that for a installation
the size of blogs.gnome.org some other object cache is installed, and
the default object cache behavior is just a fallback to get something
going quickly.

That's a question for someone with more knowledge of Wordpress admin
than me - a quick web search turned up quite a bit of discussion, but
nothing that was immediately clear.

- Owen