Update: Thanks Matt for the mention and Joseph for the excellent point in the comments that WordPress in fact uses whatever MySQL’s default table handler is, and from 5.5 onwards, that’s InnoDB – and for his comments on InnoDB durability.

My development energy has been focused on WordPress.org a lot during the past few months for the simple reason that I love the publishing platform and it’s hard to resist getting my grubby paws stuck into the awesome plugin API.

To my horror I discovered that WordPress installs using the MyISAM table engine. [Update: via Joseph Scott on the Automattic Akismet team: WordPress actually specifies no table type in the create statements, so it uses MySQL’s default table engine which is InnoDB from version 5.5 onwards. See the rest of his comment below.] I absolutely loathe MyISAM because it has burned me badly in the past when table locking killed performance in very high traffic apps I’ve built. Converting to InnoDB saved the day and so I have a lot of love for the InnoDB storage engine.

Many WordPress hosts like Hostgator don’t support anything but the default MyISAM table type that WordPress uses and they’ve made it clear they don’t plan to change.

While WordPress is a mostly read-only application that doesn’t suffer too badly with read/write locking issues, the plugin I’m developing will be doing a fair amount of writing, so the prospect of being stuck with MyISAM is a little horrifying.

So I set out to figure out exactly how bad my situation is being stuck with MyISAM.

I created a little PHP script (hey, it’s WordPress so gotta go with PHP) to benchmark MySQL with both table types. Here’s what the script does:

• Create a table using the MyISAM or InnoDB table type (depending on what we’re benching)
• The table has two integer columns (one is a primary key) and a 255 character varchar.
• Insert 10,000 records with randomly generated strings of 255 chars in length.
• Fork X number of processes (I start with one and gradually increase to 56 processes)
• Each process has a loop that iterates a number of times so that we end up with 1000 iterations evenly spaced across all processes.
• In each iteration we do an “insert IGNORE” that may or may not insert a record, a “select *” that selects a random record that may or may not exist, and then a “delete from table order by id asc limit 3” and every second delete orders “desc” instead.
• Because the delete is the most resource intensive operation I decided to bench it without the delete too.

The goal is not to figure out if WordPress needs InnoDB. The answer is that it doesn’t. The goal is to figure out if plugin developers like me should be frustrated or worried that we don’t have access to InnoDB on many WordPress hosting provider platforms.

NOTE: A lower graph is better in the benchmarks below.

MyISAM vs InnoDB doing Insert IGNORE and Selects up to 56 processes

The X axis shows number of threads and the Y axis shows the total time for each thread to complete it’s share of the loop iterations. Each loop does an “insert ignore” and a “select” from the primary key id.

The benchmark below is for a typical write intensive application where multiple threads (up to 56) are selecting and inserting into the same table. I was expecting InnoDB to murder MyISAM with this benchmark, but as you can see they are extremely close (look at the Y axis on the left) and are both very fast. Not only that but they are both very stable as concurrency increases.

MyISAM vs InnoDB doing Insert IGNORE, Selects and delete with order by

The X axis shows number of threads and the Y axis shows the total time for each thread to complete it’s share of the loop iterations. Each loop does an “insert ignore”, a “select” from the primary key id AND a “delete from table order by id desc limit 3 (or asc every second iteration).

This is a very intensive test in terms of locking because you have both the write operation of the insert combined with a small ordered range of records being deleted each iteration. I was expecting MyISAM to basically stall as threads increased. Instead I saw the strangest thing…..

Rather than MyISAM stalling and InnoDB getting better under a highly concurrent load, InnoDB gave me two spikes as concurrency increased. So I did the benchmark again because I thought maybe a cron job fired on my test machine…..

While doing the second test I kept a close eye on memory and the machine had plenty to spare. The only explanation I can come up with is that InnoDB did a buffer flush or log flush at the same point in each benchmark which killed performance.

Conclusions

Firstly, I’m blown away by the performance and level of concurrency that MyISAM delivers under heavy writes. It may be benefitting from concurrent inserts, but even so I would have expected it to get killed with my “delete order by” query.

I don’t think the test I threw at InnoDB gives a complete picture of how amazing the InnoDB storage engine actually is. I use it in extremely large scale and highly concurrent environments and I use features like clustered indexes and cascading deletes via relational constraints and the performance and reliability is spectacular. But as a basic “does MyISAM completely suck vs InnoDB?” test I think this is a useful comparison, if somewhat anecdotal.

Resources and Footnotes

You can find my benchmark script here. Rename it to a .php extension once you download it.

I’m running MySQL Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

I’m running PHP:
PHP 5.3.3-1ubuntu9.5 with Suhosin-Patch
Zend Engine v2.3.0

Both InnoDB and MyISAM engines were tuned using the following parameters:

InnoDB:
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 4
innodb_commit_concurrency = 4
innodb_flush_method = O_DIRECT

MyISAM:
key_buffer = 100M
query_cache_limit = 1M
query_cache_size = 16M

Binary logging was disabled.
The Query log was disabled.

The machine I used is a stock Linode 512 instance with 512 megs of memory.
The virtual CPU shows up as four:
Intel(R) Xeon(R) CPU L5520 @ 2.27GHz
with bogomips : 4533.49

I’m running Ubuntu 10.10 Maverick Meerkat
I’m running: Linux dev 2.6.32.16-linode28 #1 SMP