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: # u...@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 _______________________________________________ gnome-infrastructure mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnome-infrastructure
