DB issues with WPMU

[Owen Taylor]

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


_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure

[Jeff Waugh-7]

<quote who="Owen Taylor">

>  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.

Great, thanks.

> 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.

Yeah, using the database as the persistent cache for feeds and such is there
mostly for "run a single WordPress easily without doing insane stuff on
every page load" use cases.

I'll add the APC-based object cache support to WPMU, and watch how it goes
to nut out a sensible size for the APC user cache.

Ultimately, I'd love to put blogo on another machine, so its rather unique
abuse of Apache and MySQL -- compared to anything else we run -- can be
segmented away from more, uh, critical infrastructure. :-) Thoughts on this?

Thanks,

- Jeff

--
linux.conf.au 2010: Wellington, NZ                http://www.lca2010.org.nz/
 
   "We are peaking sexually when they are peaking. And two peaks makes a
                        hell of a good mount." - SMH
_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure

[Jeff Waugh-7]

<quote who="Jeff Waugh">

> I'll add the APC-based object cache support to WPMU, and watch how it goes
> to nut out a sensible size for the APC user cache.

This is now in place (with a temporary apc.php interface exposed so I can
monitor it)... watching and tweaking now.

- Jeff

--
linux.conf.au 2010: Wellington, NZ                http://www.lca2010.org.nz/
 
   "[Congressional pages] are the fluffers of liberty." - Samantha Bee on
                               The Daily Show
_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure

[Jeff Waugh-7]

<quote who="Owen Taylor">

> 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.

I just mentioned this on a bug report upstream, where the possibility of
adding an index on autoload has been discussed (for WordPress 2.9):

  http://core.trac.wordpress.org/ticket/2699

- Jeff

--
linux.conf.au 2010: Wellington, NZ                http://www.lca2010.org.nz/
 
    "Basically my philosophy on release management is that it should be
                like police brutality." - Maciej Stachowiak
_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure

[Owen Taylor]

On Tue, 2009-10-27 at 14:49 +1100, Jeff Waugh wrote:

> <quote who="Owen Taylor">
>
> >  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.
>
> Great, thanks.
>
> > 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.
>
> Yeah, using the database as the persistent cache for feeds and such is there
> mostly for "run a single WordPress easily without doing insane stuff on
> every page load" use cases.
>
> I'll add the APC-based object cache support to WPMU, and watch how it goes
> to nut out a sensible size for the APC user cache.

Sounds good.

> Ultimately, I'd love to put blogo on another machine, so its rather unique
> abuse of Apache and MySQL -- compared to anything else we run -- can be
> segmented away from more, uh, critical infrastructure. :-) Thoughts on this?

My idea for this would probably be:

 - Put the web front end in a smallish VM

 - Run multiple mysqld instances on drawable.gnome.org. Bugzilla isn't
   stressing this machine out even a bit, and we could easily host more
   databases in the same mysqld instance, but it would be nice to
   segment connection and other resources limits.

   Running multiple mysqld instances will take a fair bit of effort and
   configuration, so I don't think it's a short-term project compared
   to some of the other outstanding work.

- Owen


_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure

[Jeff Waugh-7]

<quote who="Owen Taylor">

> > Ultimately, I'd love to put blogo on another machine, so its rather
> > unique abuse of Apache and MySQL -- compared to anything else we run --
> > can be segmented away from more, uh, critical infrastructure. :-)
> > Thoughts on this?
>
> My idea for this would probably be:
>
>  - Put the web front end in a smallish VM

Right, that would be a great first step -- are we running VMs already? How
can I set this up? (And does it have to be RHEL?)

>  - Run multiple mysqld instances on drawable.gnome.org. Bugzilla isn't
>  stressing this machine out even a bit, and we could easily host more
>  databases in the same mysqld instance, but it would be nice to segment
>  connection and other resources limits.
>
>  Running multiple mysqld instances will take a fair bit of effort and
>  configuration, so I don't think it's a short-term project compared to
>  some of the other outstanding work.

Hmm, so what else is using MySQL on button at the moment? Perhaps we already
have enough segmentation -- at least for blogs vs. bugzilla. I have mostly
been avoiding dicking with MySQL because of bugzilla -- if I can spend some
time fixing up button for non-bugzilla workloads, that'd be an easy start.

- Jeff

--
linux.conf.au 2010: Wellington, NZ                http://www.lca2010.org.nz/
 
   "Instead you're doing circle jerks with the Care Bears of Censorship."
                            - Siduri on Slashdot
_______________________________________________
gnome-infrastructure mailing list
gnome-infrastructure@...
http://mail.gnome.org/mailman/listinfo/gnome-infrastructure