WordPress is an extremely flexible piece of software, and it comes with many different settings. Some are made visible to users via Admin > Settings and others are stored invisibly so users aren’t bothered by them, but all of them are saved in a single database table named wp_options. Today, it looks something like this:

The wp_options database table schema

This database table actually has a few interesting qualities to it. Conceptually, it’s a very simple key/value approach to storing any kind of arbitrary information. It’s a distant cousin to all of the meta database tables WordPress comes with (for posts, comments, terms, and users) and I’m a big fan of the entire meta-data API – it’s now fully implemented across all major object types (except blogmeta and term_relationshipmeta) and, honestly, it’s one of the few “complete” APIs you’ll interact with inside of WordPress today, aside from probably roles & rewrite rules.

The options API, however, is actually quite a bit different from meta, enough to warrant this blog post, and enough for me to have spent the past 4 days studying it, researching it, and generally trying to find ways to improve how it performs under stress and load. (I’ve come to appreciate meta even more now, though I have some concerns about it that I’ll share near the end of this post.)

And for all the flexibility that WordPress allows us, once we start taking advantage by adding plugins, themes, multisite, and so on, we are likely to reach a point where the wp_options database table becomes a graveyard of abandoned options that never get used again. “Full” is a funny (but purposeful) word avoidance choice here. A quick search of the web will tell you that the maximum number of rows a database table like wp_options can have is around 1 billion rows, but because of how WordPress interacts with them, “full” could actually mean only a few hundred rows, or 1 huge row if you’re being terrible.


Name, Value, Autoload

This table has 2 unique keys – option_id and option_name – though WordPress only ever concerns itself with option_name.  A unique key means no two rows can contain the same value in that column, which is like saying “no two switches control the same light-bulb” or “no two drivers licenses identify the same one person.” In WordPress, this means that every setting only has 1 unique entry to look for, making it very easy to know that it’s doing what you’ve asked it to.

The option_value column is a LONGTEXT, which means under today’s typical MySQL configurations, one row could contain around 4 billion bytes (or about 4 gigabytes.) You probably don’t want to store that much information there, but I think it helps to understand the theoretical maximum.

Lastly, the autoload column exists to minimize the number of peeks into the database WordPress needs to load all of the settings, and will only ever have a value of yes or no. (This is pretty silly today, and it wouldn’t be an unusual old MySQL performance hack if the autoload column were indexed.) The reason this column is important, is because every question takes time to answer, and in this case, asking WordPress 100 separate times to look in the database for 100 options would take 99 times longer than asking it to look 1 time for all of them – if you know exactly what options to load automatically, you can get all of them at once – at least, that’s the intended logic anyways.


Load All of the Options!

I won’t bore you with all of the details, but the number of relatively complex decisions that WordPress makes just to load the correct page on a website is both impressive and dumbfounding. Before any of that can happen, though, it needs to peek into the wp_options database table to understand how to make those decisions in the ways you’ve configured it to. Here’s a screenshot of some of the oldest and most common entries:

Because WordPress is a friendly piece of software, it doesn’t obfuscate anything in the database by default, and it uses a generally concise and simple naming scheme for almost all things – an option like use_smilies determines whether or not to, use smilies.

Since WordPress 2.2.0, a function named wp_load_alloptions() (previously named get_alloptions()) is used to ask one time for all options where autoload is equal to yes. Inside this function is where the first option optimizations occur, including our dramatic introduction to the object cache that comes with WordPress by default. It’s another rudimentary key/value storage system, only instead of in a database table like options or meta, it’s in memory in PHP, so it’s locally “hot” and very, very fast (much faster than querying a database, for example.)

Every single time you load a page that’s powered by WordPress, it loads all those options so they’re “primed” and ready to be referred back to at anytime until the page has completely finished loading. The nice thing about this, is you can call the same get_option( 'use_smilies' ) function a hundred times, and it will return the same result without ever needing to ask the database whether or not that option exists.

At least, that’s the case with autoloaded options. Everything else… well… that’s another story.


Once, upon a time…

It was a pretty clever idea to load all of the options in one fell swoop, and more-so to allow certain options to be excluded from that. Some options aren’t necessary on every single page-load, so if it’s only used conditionally or periodically, it would be wasteful to always ask for it, keep it cached, and never actually refer to it.

But the autoload column actually comes with a bunch of added complexity. What happens if you need an option that doesn’t exist in the database yet? Or if an option needs to switch from being autoloaded, or to not be? That means each option you need that isn’t autoloaded will cost 1 additional database query to go out and get. And it means switching an option’s autoload property requires updating the database and the object cache at the same time to reflect those changes in the live environment.

If the autoloaded options all live under the alloptions cache key, not-autoloaded and nonexistent options naturally live under the notoptions cache key. I’m not kidding – WordPress also caches the “other” options requests anyways, because if it didn’t, calling get_options( 'bedtime_story' ) 100 times would hit the database 100 times looking for an option that doesn’t exist in the database.

So if all options are cached, and not-options are cached, what’s the point of having an autoload column at all? There’s an argument to be made for abandoning its usage, but backwards compatibility isn’t a feature, it’s an ethos – it can’t just be ignored – and this code goes all the way back to the very first versions of WordPress, so we can’t easily unplug it after all these years, can we?


Oh yeah…

All of that explanation up there leads us to a hidden limitation of the options API deep down in its core. While WordPress comes with a simple local object cache, some persistent object caching drop-ins (Memcache, Redis, etc…) have limitations on how much memory 1 cache key can occupy, and those limitations are much smaller than the 4GB LONGTEXT theoretical maximum of 1 row, let alone 1 billion theoretical maximum rows all in one alloptions cache key.

For example, Memcached has a 1MB (default) upper limit for the entire alloptions array of autoloaded option names & values, and another 1MB upper limit for the notoptions names and values array. That’s not per-option, it’s per array, for the cumulative size of all items in each individual cache-key, so autoloading them together into 1 primary key becomes a huge problem very quickly.

It’s actually one of those problems that any good web hosting company has been silently solving for you forever, and subsequently a problem that I’m working on solving for them, and for you, and for WordPress going forward.


What’s Next?

13 years ago, WordPress was obviously a very different piece of software than it is today – and we were very different people, so with any luck, our approaches to solving problems back then should be different than how we’d do it today, too. I’ve spent the last 4 full work days doing the deep dive into why the current approach was good then, what seems good now, and what problems might we have going forward.

This post represents part 1 of those 3 steps.

I’ve written and rewritten hundreds of lines of code already, and determined that abandoning the alloptions and notoptions cache keys completely is the most desirable option, and using the option_name as the cache key for each individual option will provide the most headway going forward. I have a few other hypotheses to test, around storing entire objects vs. only the option_value, around having a separate cache group for notoptions keys to reside in, and about adding a separate wp_optionmeta database table to allow for each 1 option to have its own arbitrary attributes.

For me, I get to spend a few more days trying to implement my ideas and testing that they actually perform as good as (or better than) what WordPress is already capable of, and that it adequately spreads the pain around when drop-ins like Memcache or Redis are in use. I’ll report back my findings here, and share them on the relative WordPress core Trac tickets once there’s something worthy of a RFC.


You forgot something

Oh yeah! About the meta-data API, right? Interestingly, when trying to get a piece of meta data about an object in WordPress, the update_meta_cache() function works similarly to wp_load_alloptions() in that it queries it’s database table for all rows relative to the object’s ID. One key difference is that all meta is always loaded for the object (there is no autoload option.) Another is that any changes to meta-data will delete the relevant cache, but options modify the cache directly without deleting it so subsequent calls don’t typically generate additional database queries.

As custom post types start to store more and more things in wp_postmeta, cache keys may start to reach the same theoretical upper limit that wp_options has been plagued with because they are not unique. For example, if you store a thousand matched meta keys for a single post, and each of those keys is of any significant size, it could totally hit the same Memcache 1MB default boundary. I don’t know of this happening to anyone yet, but it seems plausible if not inevitable.

If you’ve made it this far in this post, you care about a silly dumb problem as much as I do, so we are instantly friends. If you’re also independently working in this area of WordPress, I’m excited to compare notes and Voltron up and blazing sword our way through this together. 💜


Relevant reading

For most of my life, whenever someone would ask what kind of music I was into, I struggled to come up with a clear and obvious response. I’ve always enjoyed many different artists, songs, and genres, each for a myriad of reasons, and the one style of music that I consider “my favorite” isn’t one that most people can relate to.

And that’s really what the “what kind of music do you listen to” question is designed to do – relate. It’s like when someone asks “what is your favorite sports team” and you enjoy sports but aren’t passionate about any specific type or group.

Then you tell them you love jai-alai, and either have to explain why, or skip over the conversation entirely.

It’s really hard to relate to people when the universe has gifted you with a heart that craves the unrelatable.

I love hip-hop, old-school, funk, edm, d&b, electro, Miami-bass, and a bunch of other genres that most people would go “oh… cool.” without actually thinking it was cool at all, because they don’t know anything about them.

But late last year, I came to realize it wasn’t really any “kind” of music that I liked as much as it was the “instrument” used to produce individual songs. There was one specific sound that hooked me when I was 11 years old, and it came from the Roland TR-808 drum machine.

WordCamp Miami had an 80’s theme this year, and that’s really when the 808 found its momentum. During my presentation for BuddyCamp 2017, I mentioned 808 The Movie, which is as adequate of a history lesson about my favorite type of music as will likely exist in my lifetime, though the west-coast omissions of The Egyptian Lover, Arabian Prince, and a few others still leaves me a bit disappointed in how such a huge part of what influenced the 808 movement ended up totally missing.

Anyways…

It’s with a heavy heart I share the following post from Hip-Hop DX, which talks a bit about the life (and recent passing) of the inventor of the instrument that I hear in my imagination everyday when I play back my favorite songs in my head.

EXCLUSIVE: Egyptian Lover speaks on Kakehashi’s legacy.

Source: Music World Mourns Loss Of Roland TR-808 Drum Machine Inventor Ikutaro Kakehashi

Ikutaro Kakehashi, like Gunpei Yokoi, influenced my life in ways that they’ll never know and that I’ll continue to subtly share in the code that I write and the WordPress plugins that I release and help build. They are under-appreciated craftsmen in their respective fields of study, both talented and lucky, who were able to help invent entirely new cultures and movements around their creative visions, and I hope that I’m able to leave something similar behind someday. 💜

Last week sometime, I received a distressing Twitter DM from Chris Coyier (of CSS Tricks fame) – he noticed that the markdown in the CSS Tricks forums started behaving badly, and the shim they put in place seemed to have finally gave way… given way… erm… it stopped working, mostly.

I was on vacation with my wife Keri in Key West at the time, but she was patient and wonderful enough to allow me to – completely guilt free – write a quick plugin to help Chris and his users out. She’s great, FYI.

The problem, was that bbPress has it’s own backtick support for wrapping code in <code> tags, and that was conflicting with Jetpack’s Markdown processing (which is actually pretty cool, and looks to be a direct port of what was on WordPress.com all those years ago.)

Anyways, Chris was kind enough to write some really thoughtful words over on CSS Tricks (and share my latest WordPress experiment Plugins Loaded) and since I am suddenly a content marketer, I’m sharing Chris’s post here:

Plugins Loaded

January & February were very busy and eventful months for me, as I’ve been working full-time on WordPress Core & Community related endeavors thanks to two companies, and largely 2 individuals, who decided to take a bit of a risk and try something relatively new with me.

The scene is WordCamp US 2016 in Philadelphia, PA – the weather is surprisingly warm and the event is abuzz with close to 1800 WordPress fans in attendance. For me, it’s a reunion of sorts, because it’s only a few times per year I’m able to have in-person time with many of my ex-coworkers, current open-source colleagues, and friends I’ve made thanks to WordPress but maybe haven’t collaborated with yet.

I had several really productive conversations, mostly me pitching the idea of asking for financial support to “work on WordPress” in a way that does not place the burden on any one specific company, and Josh from Pagely and Josh from Pantheon both welcomed the experiment with open minds and were excited to get started.

January

I refer to the WordPress 4.7.2 release as “January” as I spent the majority of it reviewing & hardening several important security improvements to the core codebase (namely, the REST API and Press This.) In addition, I upgraded bbPress across all of WordCamp.org, and laid the groundwork for upgrading bbPress across all of WordPress.org in the coming months.

The security team also paired up with liaisons from almost all major hosting companies and CDNs to bring awareness to and monitor for any public exploitation attempts, and I was actively involved in on-boarding and expectation setting while we introduced an entirely new workflow for all of us.

Thanks to Josh & Sally Strebel at Pagely for supporting me and WordPress during this critical month. 💜

February

With WordPress 4.7.2 “January” out, things “settled down” and I switched to LoopConf mode where I gave a talk about how Unix Philosophies apply to WordPress development, and was interviewed by Brian Krogsgard of Post Status fame about the longevity of open-source software (I’ll embed that video below.) LoopConf was great, Salt Lake City was fun, and I’ll do a separate write-up about my experience soon.

I’ve also focused more the backlog of issues and nice-to-haves for the bb’s, largely related to how the WordPress.org Support team works with it as a tool. Working with other volunteer contributors like Boone Gorges, Stephen Edgar, Brandon Allen, and Sergey Biryukov again, has been a treat.

Thanks to Josh Koenig from Pantheon for an awesome groundhog’s day, happily looping for 28 days working on WordPress. 💜

March & April

I have 2 weeks “off” planned in March for WordCamp Miami and my wedding anniversary, and I’ll be speaking at CypherCon 2.0 in Milwaukee and I think a few other events that I can’t remember right now that I’ll come back and update later. Development wise, I suspect will be more of the same: security, on-boarding, WordCamp.org, and bbPress upgrades across WordPress.org.

I’m also going to help Pantheon with some marketing, which is something I’m excited to help with and learn more about, since I’ve spent most of my career helping build the things other people get to pitch to the world.

And here’s that Post Status video I mentioned earlier.