 Hey, my name is Ben Greeley. I live up in Brunswick, which is about a 30 minute drive from here. If you guys are visiting Portland and visiting Maine, you should make a drive up the highway and visit Brunswick because it's a beautiful little downtown and a great place to check out. But I'm not here to promote Brunswick. I'm here to talk about WordPress and the WordPress Office Table. I am Director of Engineering at TenUp. TenUp is a digital agency where we specialize in developing large websites for enterprise clients. If you have any questions for me about TenUp or about the presentation that I'm about to give, definitely reach out to me. You're seeing after the presentation. But yeah, I'm going to be talking about the WordPress Options Table, which it can cause many headaches and can cause people to get their hair out sometimes. So a little background of what the WordPress Options Table is. The WordPress Options Table is where WordPress stores all sorts of site settings such as plugin settings, theme settings, and pretty much anything else that developers may want stored there. It's a pretty simple table. You can see that basically you specify an option name, a value for the option, and whether or not the option should be uploaded into memory. And that's about it. But even though this is a simple table, you should pay particular attention to this presentation to develop WordPress themes or plugins, or even just maintain a WordPress website. Because like I said before, left untamed, the WordPress Options Table can slow down your website, or even worse, as I learned, the hard way. And I'll tell you a little bit about that story. So a while back, I was working on a high-traffic website. There were thousands of pages on the site. It got lots of traffic every day. And we started getting complaints that the website was running much slower than usual. Also started hearing weird complaints about the WordPress admin not saving correctly. When people would go into the WordPress admin and try to save a setting, the changes weren't being reflected on the front end for some reason. So I decided to take a look and see what was going on. My natural instinct was to blame caching, because every time we reset the cache, it seemed to magically fix it. So basically I reset the cache every day for a couple of weeks until I figured out that something else must be going on. And decided to take a deeper look into the situation. So when I took a look at the Options Table, I saw quite a few things going on there that were problematic. It was about 20 megabytes in size, which is over 20 times larger than it should be. It turns out a popular WordPress plugin had synced with Google Search Console and pulled over all the data for that large website. So Google Search Console stores keywords for how people found your website. It also says what the top pages are that people found based on these keywords. And so there's a ton of data for a lot of pages, and that just filled the Options Table. So that plugin was not doing what it should have been doing. The recommended size for the Options Table is about one megabyte or less. And the reason for that limit is that WordPress, of course, means that many caching plugins, actually, by default, have a one megabyte limit by default. So if you have a caching plugin installed and you try to pull over, like, 20 megabytes of data over on that root page load, that's not going to update your pages on that root page load. So it's going to be storing stale data. And that's basically what was going on with the admin options not saving that I was telling you about earlier. Next, I looked into the themes, the custom themes pfpcode, and saw some of your things going on there that we probably should have caught. The add option and update option functions weren't being used correctly. You can see here, these are the definitions of add option and update option, and you pass over a name for the option, the value, and whether or not you want it to autoload, so yes or no. By default, you can see the autoload is optional. And it defaults to either no or yes. And when it's no, it actually saves as yes. So if you just update option and or add option and don't specify autoload, every single option that you are saving is going to be autoload of every single page load. Autoloaded options are very helpful. So let's say you have 50 options that you want to use on every single page. This basically lets you grab all the options at once. But that's not very good when you have a lot of options that don't need to be on every page. So if you have just some sort of new thing you're storing there for one particular plug-in or one particular page, it's very inefficient to have to load that on every page. To illustrate the point of what should have been happening with those functions, this first example is storing a very large value. So this is the letter X, repeated about a million times, and it's being stored in the options table. And because the autoload is not being specified, this is going to be loaded on every single page on the website. Whereas if we had passed over no as a third parameter for the update option, that would have not loaded on every page and we could have just pulled that option when we needed it. So I talked a little bit about what autoload does. It basically loads things in memory so we don't have to make like 50 calls to the database, which is actually more efficient in the long run. So WordPress runs this very SQL query on every single page on the website. What it's doing is it's selecting all the options in the options table that are set to yes for autoload. And when the table is very small, or I should say it varies a lot, when it's a small number of rows in the table, this is very efficient and it's pretty fast. However, we have a lot of rows in the options table. This can take expert milliseconds. I've seen it actually take an extra second or so to actually execute this and it can slow down your page load on every page of your site. And the reason that this is so slow, you would think that this would be something that we should actually be able to store a lot of options in. But the reason for the slowness is that WordPress does not have an index on the autoload column in WordPress. So a little background on database indexes. Database indexes are a data structure that allows SQL and MySQL to quickly look up values in the options table or any table, really. So this is another lookup table where you could look for the value of yes and it would know exactly what rows to return for that. And without that index, MySQL is basically looking at every single row and saying, are you yes? Are you yes? Are you yes? And that could take a long time. There are some disadvantages to using an index. It does slow the insert, update, and delete performance. And the reason for that is that when you run those commands, it has to update the index again. So let's say you're updating a post in the back end or actually updating an option in the back end. That would take a little bit extra time, but it's not that much slower. Another disadvantage is that it takes extra disk space to have this index lookup. So if you have a hosting company that doesn't want you to store a lot of data, this could be problematic. But if you have a large options table, you might want to think about using an index because the queries will get slower the more options you have in there. And to illustrate this point, this is a graph actually that our director of systems engineering created a while back to illustrate the difference on the options table with an index versus not having an index. So the red line here represents a query or that very same query that we saw back here. So this select in the options table where the all alone equals yes. That particular query on a table that does not have an index versus the blue line on the bottom, if you can tell there's a blue line down there. And that represents a query, that very same query on a table that does have an index. And you can see as the table size grows, the amount of time it takes for the query to execute becomes quite substantial. There is this sweet spot down here where there isn't much of a difference between an index or not having an index. And that's kind of where you want to shoot for for the fault. You know, you could have an index if you needed to store a million records. You definitely could. But by default, most people probably don't. And you know, you should be shooting for that like 1,500 records sweet spot there. Why doesn't WordPress include an index? That was the question I was asking myself as I was looking into this. It turns out there's actually a pretty hot discussion about this going on in the WordPress community. There's a WordPress core ticket going back over like five years or so where developers are debating this. It's quite a lengthy conversation. But that's the great thing about WordPress and open source software is that you can be a part of the conversation and try to change these things that aren't working well. But basically, you know, to summarize what the conversation is, you know, one reason that people are arguing against having an index is that the options table was never meant to store a lot of options or a lot of rows in the options table. So there's really no reason to have it if people keep the options table small. But as we saw with the functions that I was using earlier, those functions are very easy to use. And so if developers don't really know how to store everything that they have in the options table, they can very easily fill it up. So, you know, that might be a reason for us in the future to consider having an index on the options table. If you do need to have an index, it's very easy to do so. You can run a query like the one here in SQL. And what you're doing is say, create an index, give the index a unique name, and you say what table you want is that you want to have that index index. And once you run that, it's pretty quick and there's really no other steps needed. So, going back to the original story I was telling, there were some other things going on besides that large plugin that pulled over from Google Search Console. There was another bad plugin, I wouldn't say bad, but a plugin that had a bug that ended up filling the options table with over 5,000 rows. And what it was doing was it was performing a sync, and so every minute that the plugin was performing a sync, it would add a row to the options table. That sync might run for a couple of minutes at a time, but after many weeks, after many months or years, the options table got filled up and there was just a bug in the plugin that let those live on and didn't clean them up later. As I was talking about earlier where you should be shooting for the options table, you should try to make sure that you have less than 500 rows in the options table for optimal performance. The only way that you can do that is to make sure that you are regularly checking the options table. You don't really think of having to check because it's something that's actually very difficult to check unless you're familiar with SQL or some other tools. I'll talk about those tools in a little bit of how you can check. This is just an example of what an options table looks like when it's filled up. In this particular example, the plugin WPSEO which is Yoast, it added a cache validator record which it uses internally, I assume, for something. I didn't actually look up to see what it uses this for, but it basically is just a temporary record that's adding to the options table and this is a very common issue that you see if you use Yoast. You either have to fix some day or maybe they already have fixed it. This is just for illustrative purposes. I figured out what's going on. To fix it, these are the steps I took to do so. The first thing I did is I ran a SQL query to delete all those extra rows. In this SQL query, it deletes all the rows from the options table that start with WPSEO site map. All those 5000 rows that got pulled over, got cleaned up and that definitely helped performance. I also ran another SQL query. This one updates specific options to non-autoload. I figured out maybe that big option that I stored that stores the letter X a million times. That probably doesn't need to be on every page so I made sure to run update command to make sure that wasn't loading on every page and that definitely helped performance. I went into the team's PHP code and I updated the options the functions that updated options and added options to specify whether or not I wanted the option to autoload. For the most part, most of the options did not need to autoload so I just passed a no. I fixed that issue moving forward. These were some pretty easy fixes I made too. The plugin that synced to a Google search console talks to the client and they didn't have all Google search consoles data in WordPress. They could just go to Google search console and go through it so we turned that sync off which was a toggle in the plugin and also it took the opportunity to clean up any unnecessary plugins on the site. There were some things that were just adding some options that weren't huge but if you have 10, 20 plugins that aren't being used and they're each adding 20 options that can add up over time so I just uninstalled things that were needed. I talked a little bit about the tools you can use or actually that's what I'm going to talk about now. I alluded to the fact that I'm going to talk about the tools. One of the tools I highly recommend is Numeric. What Numeric is it's a service that shows you specific performance on your site so in this graph here you can see that this is the performance of a specific page or maybe the website in general over a period of time and it shows that the previous key performance is this and you can kind of just see where the slowness is in this particular graph the slowness is in my SQL queries so if I was going to try to optimize this site that's probably where I try to spend most of my time but Numeric is awesome it also shows you reports like this one here which help you drill into specific repressed hooks and functions I was trying to figure out a particular page why it took 146 seconds for the page to load which is a pretty slow page if you can imagine and this particular page was the admin Ajax.php you can drill into like the do action so you can see it was actually an action that was being slow and if you look at the first one create initial post types I'm not sure that was actually a pretty slow that's actually a pretty fast process or function that ran that took three milliseconds you can just go through and look at the time it took there was this one down here that was four capabilities to add caps took 146 seconds for some reason and that gives me the information to dig into the PHP code and figure out why that was slow this example doesn't have anything to do with the options table but if the options table was being slow I could basically drill into the performance like was to see exactly how slow that was and be able to tweak things as needed another tool that I highly recommend using is WPCLI which is a command line tool which lets you execute WordPress commands via your command line this particular command here lets you see all the options in the options table and also let you see the size in bytes of each option to kind of show you what that looks like run this on the site that has a pretty good options table so this returns like all the options in the options table this is a number of bytes that are on each option and you can see that everything is pretty small like this is like a kilobyte four kilobytes so there's really not a lot here that I'm too worried about there's probably like a hundred options or so here so it's a pretty small options table but if I ran this on another site that was having issues so in this one you can see the numbers are much larger so this options table is full there's WPSEO it's pretty problematic I always say problematic by adding a lot of options to the options table the overall number isn't that different I think in this one as well there's like a hundred rows in the options table but the size is what I'm concerned about here so this just lets you check in and see what plugins or how plugins are filling your options table another tool you can use is just using SQL or MySQL if you have access to run SQL commands basically run command like the one here and this command tells me all the like a sum of the size of all the options that are set to auto load so if you remember I told you you should have your auto load options if you're less than one megabyte there's a difference on one of those databases so I'm going to run this on the on the database of options in the options table so you can see that the size is actually 3.5 megabytes for auto load options that's problematic especially if I was using a caching plugin because that wouldn't be refreshing on the page likewise if I had a site that was doing well so I just switched to a different database and so this one is actually more around what you should be seeing and this is a fresh WordPress install so that's why you're seeing it that small it's okay so to summarize what I've been talking about today the best practice to make sure that your options table is less than one megabyte in size always make sure that you're monitoring your website to make sure that it's less than a megabyte be sure to check the functions in your PHP codes to specify whether or not the options should be auto loaded that's something that's easily missed especially if you don't understand what the auto load parameter does keep the number of rows in the options table less than 500 you know you can go up and above that if you need to but you know performance is degraded especially if you have hundreds of thousands of rows so it's just a good rule of thumb if you're small and if you need to have a lot of rows you definitely can store a lot of data in the options table if you need to but you just need to add an index on the auto load column but yeah I can also answer any more specific questions that anyone has about like how to store data there's a lot to talk about here and I just wanted to make sure I got through the best practices for options before I dug into other stuff yeah I put them on my Twitter feed and tried to I think I can send them so we can't name them so one thing I did not touch on but I think we have a little time is that true it's like you know what if you didn't want to store a lot of data somewhere like obviously the options table isn't where you want to store everything for your plugin you know I would recommend to actually create a custom post type that is private and that nobody can ever see and just use that to store all the data and so that adds your data that you need to add your plugin into the post table and the post table has indexes that are very efficient it works very well and you can keep you know millions of records in there without slowing down too much that's you know a natural progression from the options table also the options table stores transients so transients is like WordPress's way of caching and if you don't have a caching plugin set up it stores things in the options table and that can also fill the options table up and so having object caching plugin definitely will help out performance as well any questions? sir could you write like a small plugin to like monitor the size of the options table and then just like report to you when it hits 500 rows or like 1 meg or something definitely I think some people actually have that notify you or have it notify you when it hits like 500 or something I think it definitely could be done I know that there are some plugins out there that people created to basically look at the same thing so maybe the plugins already do this as long as they themselves don't write to the options table let me just make one thing clear like it's not bad to store things to the options table just excessive amounts of things to the options table like maybe it's not where you should store a record of everybody that logged in over the past year but it might be the right place to store the setting that somebody wants to have on their site like whether they want to be able to do background or write background and you said the better option is to put your data into the WP post that's one recommendation? yeah so you can create custom post types and WordPress and those can be private and not accessible from the front end and you can basically set your plugin function or things that basically store anything you want there that'll be a recommendation but I think it all depends some people decide to make custom tables I've never run into a situation around you to create a custom table really but it all depends on what you need why not a custom table just to figure out indexing and stuff? yeah so a lot of WordPress functions work really well with the posts so like there's a lot of WordPress functions that basically assume you're using the post table and so you can pass over the post type and you can reuse all those if you have your own table you have to rewrite a lot of those basically and that would just take a little bit of time I would want to get into you certainly could, I know a lot of plugins do that I think Gravity Forms has a lot of tables I think it all depends on how much data you want to store too I know that Gravity Forms actually blogs every single view of form and that's probably nothing you want to have in your post table I don't want to have that separate so it all depends but that's one thing that I would probably look at thank you there's a plugin called WPCFM I think that sort of saves your options allows you to load and reload them are you familiar with that? does it collide? what's that do? it's a way of saving a complete configuration of websites so you can load all your plugins and just load it all the options it all wants to get to a start you can pass the options table for one it's kind of cool yeah that's interesting I think the options table is one area where it's often overlooked for improvements but as you can see these queries are run on every single page so it's definitely an area that WordPress is looking to improving it by default I haven't actually commented on that thread yet but I'm kind of inspired to actually put my two cents in there because I've given this talk before and the more I talk about it the more I don't really understand why we don't have an index in there despite defaults especially when people are just going to track around it is it really that simple to make an index which you wrote that one line? yeah so I can execute it and just prove that I hope it works yeah that one yeah so if I go into this database that was pretty inefficient so this basically now has an index in the options table if I go to options I think I can view the data structure here it's there and creates in time what's that? it's there in the created table statement oh down here okay here we go oh okay there we go out of those index so yeah it's pretty easy to make them but it does have some performance downsides as I was pointing out but if you have people using the admin they might be a little more patient they can wait a half a second it's not like it adds next for 5 seconds it's more like milliseconds or maybe a couple of seconds if you're saving something to the options table if you're saving a post it shouldn't it shouldn't affect it much exactly I mean posts have their own indexes that's updating every time sometimes people find that the post table can be slow for certain queries and they add their own indexes in there as well and how fact does the auto index help it it helps it significantly you have the two graphs it's just it's kind of mind-boggling it's just right right there in one line it auto indexes and your chart is going to be totally different I mean there's a lot more stuff going on behind the scenes so this could be detrimental to a site if you have a million rows in your table and you don't have much disk storage for whatever reason you have like a $2 a month hosting company or something like that you might not have that distortion program with but yeah it's pretty easy to have that and this is built into most SQL databases so you can this isn't just a MySQL functionality Any other questions? Thank you, we'll post this on the screen