Fix high CPU usage by WordPress and MySQL

Today one of our wordpress sites had very high server load and it was being caused by MySQL

So I went to the mysql console, and looked up the process list:

So this guy is appearing a lot
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Let’s see how it’s behaving with explain
explain SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

It’s scanning 226k rows to get its search results!

Probably some moronic plugin is doing this and wordpress does not add an index on that table. The solution is simple, let’s add an index!

ALTER TABLE wp_options ADD INDEX (`autoload`);

Now let’s run explain again

From scanning 226k it went down to 408!, 3 orders of magnitude drop.

And now the CPU load went below 4%, crisis averted.

Philip Antoniades from Sun-MySQL presents at NYPHP.org

Last night I had the opportunity to attend to my first NYPHP.org meetup. In this ocassion, Philip Antoniades formerly MySQL’s Sales Engineering Manager, now Sun-MySQL’s Sales Engineering Manager gave a talk related to Sun’s recent purchase of MySQL for USD $1,000,000,000.

Project Indiana
I must say at the beginning of the presentation he switched from talking about the MySQL acquisition to selling us a new version of Open Solaris called Project Indiana, which seems to be led by no less than Ian Murdock creator of Debian. I suppose its his job now to sell sun and he had to do it, but things got a lot more interesting specially in the Q&A.

So for Project Indiana, as you can imagine having someone like Ian Murdock there means Open Solaris will have apt capabilities (like Debian or Ubuntu).

The other thing that caught my eye about Project Indiana (coming from the Linux world) is that Sun will require all drivers to be open sourced, otherwise they won’t be accepted to the distribution. I’m not sure if this has done anything for projects like Debian where they’re really strict, but Sun is a monster with a lot of corporate influence, maybe their new Open source approach will help things change (manufacturers releasing drivers with open licenses), and finally we’ll get lots of drivers available for Linux as well.

They also mentioned an “open source” license called CDDL (“cuddle” as they call it), no clue about its differences, if its good or bad, it seems to me there might be something weird behind it, but I’m just talking out of my ass here, I’ve not read it yet. It’d be good if someone could complement this article with their thoughts on CDDL vs GPL2 vs LGPL vs others…

How things have changed

Quoting Antoniades:

“Basically we have an influx of cash”

MySQL AB went from a 400 employee company to being part of an organization of about 34,000 employees, and now they have a lot more cash, the purchase according to Antoniades was mostly paid in cash (and you know cash is king baby!).

Entrance of IBM New York, where the meeting was held for NYPHP.orgPhilip even mentioned they’re hiring 2 SysAdmins for their New York office, (contact him if you’re interested), also, when they need a feature, they just don’t put it in the wish list, if its important enough they get 30 engineers from Sun to help them.

He made a lot of emphasis about how they already got a guru focused on optimization of MySQL and how they’re pushing for more threading (parallel programming) on MySQL. He said that Sun has a lot of DB experience, that they basically helped Oracle in getting their DB to run spotless on Solaris and they have the knowledge about DB development problems as well as a lot of smart people to help them.

On the Q&A session I finally learned that Yes there’s plans between Java/MySQL integration (stored procedures in java could be a project to come, and they mentioned that a couple of the MySQL AB engineers had already done something along those lines, and as the acquisition was happening the people from Sun brought up the fact they knew about such efforts. MySQL AB has always been a good Java shop, and they claimed their jdbc driver was always one of their best products). MySQL basically wants to learn a little more of that engineering discipline from Sun, and it seems like a nice blend between 2 very different cultures.

Other interesting topics of conversation during Q&A were about using Memcached as a storage Engine, so that you can use MySQL as a client for Memcached, maybe it sounds as an extra layer with a certain overhead, but you could certainly do a lot of more complex stuff just by using SQL, seems interesting, didn’t know you could actually do that (actually opens a lot of possibilities in my mind for stuff we need in MyBloop.com that we were planning to use raw key-value-approach on memcached)

Also learned about a new storage engine called Maria, that will be the successor of Innodb.

So I think that now, we can be sure that MySQL will be churning out a lot more fixes (in shorter periods, maybe MySQL 6.0 will come out sooner now) given the cash and new added man power, probably the folks using MySQL enterprise will be even more benefited by this, they talked about a lot of enterprise tools to optimize queries, analyze queries, very interesting stuff.