optimization

What is the current status of web servers…Is Apache 2.x “fast enough?”

Automattic uses Lightspeed (for PHP), nginx (for static content), and Apache (for media uploads). For WordPress-generated content, all server options are approximately the same speed.

What about APC?

Automattic uses beta versions of APC, and provides a 3-5x performance increase. It’s tied closely to the PHP version, so Automattic recently switched from PHP 4 to PHP 5.

Databases?

MySQL scales well and is easy enough to use that there’s little reason to consider other DBs for WordPress content. Other applications may have different needs. Note: FriendFeed uses MySQL to store schema-less data. Single-table key lookups in MySQL are faster than getting the data from Memcached.

Caching?

Automattic uses Batcache for full-page caching (.002 to .003 second), Memcached persistent object cache, very limited MySQL query cache (never larger than 256MB), sufficiently large key buffer.

HyperDB?

HyperDB solves DB scaling problems.

Backups

User-data backed up every hour, if something changed. Every blog backed up every 12 hours. Dedicated MySQL slaves do LVM snapshots for backups.

Steve Souders: 10% of the problem is server performance, 90% of problem is browser activity after the main html is downloaded. He wrote the book and developed YSlow, so he should know.

JavaScripts are downloaded serially and block other activity. Most JavaScript functions aren’t used at OnLoad. We could split the JS and only load essential functions up front, and load all the rest later. How much might that help? He says 25% to 50%. This quickly gets complex, but he’s got a simple plan that considers three questions:

• Is the script URL on the same host as the main HTML?
• Should the browser indicate it’s busy, or not?
• Does script execution order mater?

And at that point things started to get too interesting to take publishable notes. I clearly need to pay more attention to this guy.

Stats he mentioned without being specific about the source:

• Google: 200ms longer download time cut revenue by 20%
• Yahoo: 100ms of latency costs … big.

When doing a bulk insert/update/change to a MySQL table you can temporarily disable index updates like this:

…do stuff…

From the docs:

ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

Yes, I’m still trying to squeeze more performance out of MySQL. And since small changes to a query can make a big difference in performance…

Here are two really easy things to be aware of:

• Never do a COUNT(*) (or anything *, says Zach). Instead, replace the * with the name of the column you’re searching against (and is hopefully indexed). That way some queries can execute entirely in the keycache (while * forces MySQL to read every matching row from the table).
• When joining two large tables, but only searching against one, put the join statement at the end. Why join the two entire tables when you only have to join the matching rows?

I mention these because, well, I’ve known them forever, but upon seeing them again I realized I hadn’t really obeyed those simple rules in some of my queries.

Separately, there’s some pretty good info on what server variables affect what at mysqlperformanceblog too.

Ryan Boren wrote about using memcached with WordPress almost a year ago:

Memcached is a distributed memory object caching system. WordPress 2.0 can make use of memcached by dropping in a special backend for the WP object cache. The memcached backend replaces the default backend and directs all cache requests to one or more memcached daemons. You must have a memcached daemon running somewhere for this to work. Unless you’re managing the server on which your blog is running, you probably can’t run a memcached daemon, making this backend useless to you. The memcached backend is targeted at ISPs and those running WPMU. If you are using WPMU and distributing DB requests across multiple servers, memcached will come in very handy. Using memcached for a single blog isn’t really worth it. In my tests, it was sometimes slower than using the default object cache backend.

The plugin is here, a bug-fix note is here.

I’ve been following WP2.1 development, but Aaron Brazell’s post in the development blog wrapped up a lot of questions all at once.

The short story is that 2.1 is going to bring some really good changes that will allow more flexibility and better optimization of WPopac. Of the four changes Brazell names, the last two, the addition of the post_type column and a change in usage of the post_status column, are where the money is.

I’m awaiting the final release of 2.1 before building the necessary changes into WPopac, but the benefits will be worth it.

I have over 1000 posts here at MaisonBisson, but even so, the table with all those posts is under 3MB. Now I’ve got a project with 150,000 posts — yes, 150,000 posts! — and the table is about 500MB. An associated table, structured sort of like WP’s postsmeta, has over 1.5 million records and weighs in at over 100MB (not including the 150MB of indexes).

Up to now I’ve been a “throw more hardware at it” sort of guy — and in a server with only 1GB of RAM, that’s probably the best solution — but I also think it’s time I learned some MySQL optimization tricks. Zach‘s been pushing me to get mytop installed for quite a while, and I finally got around to it.

mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed. And with Term::ANSIColor installed you even get color. If you install Time::HiRes, you’ll get good real-time queries/second stats.