Multisite Archives - Spacedmonkey

Making user queries in WordPress scale

Jonny Harris

06/05/2019
As one of the maintainers of the users component in WordPress core, the question of how well sites with lots of registered users scale, is an important one to me. The idea of sites with hundreds of thousands of register user is make more complex when you add multisite into the mix.

So for a client project an educational site I had built a site that required users to register and login to get access to more lessons. This site became pretty popular, getting over 100,000 registered users in the first 3 months of the site going live. But I noticed one interesting thing, many of the users screens in the CMS, became unusable. This is unfortunately because of how user roles are stored in the WordPress database. User role are stored in user meta, in a serialized array. Meaning to query users by their role (something core does in a number of places), core has to query on the user table, with a join to the user meta that does a like search on an unindexed field (meta value). As you might have guessed, the result of that queries is extremely slow and when you have over 100k users, unless there is massively powerful database server running the query, that query will simply times out.

So, I had tracked down the badly performing queries, what are the next step to fixing them? Well, first, I needed to review what core functions / classes accessed the users tables and what were the caches were in place. After hours of reviewing core code, I realised one thing. User queries are a mess. Unlike almost all other parts of core, there were so many places in core that were still using raw SQL instead of using the WP_User_Query class. There were many places that should be using caches, but weren’t. There were places that weren’t even using the user cache invalidation function.

So I set-able fixing a number of these issues. These issues include
There were others and are I am still working on more. Props to the other members of the core team, specially Adam Silverstein .who worked with me to get all these tickets into WordPress 5.1 and 5.2. But what this means in the end, that is much more constancy on how User data is queried (using WP_User_Query where possible) and how it cached. This by itself it a massive improvement, but I decided push it a little more. I built a feature plugin for core called WP User Query Cache. This plugin use new filtered that were added in 5.1 to cache the result of the query, so that those expensive user queries, need only be run once and the cached. One of the biggest issues, is that sites with lots of users, will likely have those caches invalidated by other users updating their profiles and new users registering. This is not something that is easily solved, however, some level of caching is better than nothing, as it makes the CMS user edit screen accessible again.

If you are a site that has lots of users and have object caching enabled, please take a look at the WP User Query Cache plugin consider installing and help me test and improve this plugin. Hope to try and push for this to get into core and if it doesn’t this code can always live on as plugin.
Development improvements in WordPress 5.1

Jonny Harris

02/05/2019

This post is extremely late to publish, but I thought I would see publish it anyone, as someone might find it useful. Here are a list of important tickets in the WordPress 5.1 release.

Multisite

Introduce shared wp_blogmeta database table

Improve site creation in multisite

Implement `wp_initialize_site()` and wp_uninitialize_site()

Introduce `ms-site.php` and `ms-network.php` files

Use total users in WP_User_Query in WP_MS_Sites_List_Table

Users

Add Limit 1 to User query

Add pre filter in count user function

New filter to short circuit WP_User_Query results

Options

Add support for an optional `$options` parameter to `populate_options()`

Ensure a network’s `notoptions` cache is an array to reduce lookup overhead

Meta api

Add LIKE support to meta_key comparisons in WP_Meta_Query

Outsource network option handling from `populate_network()` to a new `populate_network_meta()`

Introduce new `populate_site_meta()` function

Thumbnails

Add filter to has_post_thumbnail() to override default thumbnail use

Coding standards

Upgrade WPCS to 2.0.0

Debug

When WP_DEBUG is true show better errors for establishing database connection

Expand WP_DEBUG_LOG and make WP_DEBUG_DISPLAY work as expected

WSODs protection returns incorrect content type for JSON Requests
The state of fields API and Meta in core.

Jonny Harris

16/11/2017
So this is a post that I have been meaning to write for a long time. I think that the fields API and how core handles meta is without doubt the most important problem that is facing the WordPress project. But looking the work done to date on the fields api, it can get a little confusing, the project has changed owners and scope a number of times. In this post, I hope to clear up a little of the history and explain my ideas for the fields API going forward.

So what is the fields api anyway and not having it is such a problem?

The fields API is a bit of a different name for what it really is. It is in short, a library to build custom meta boxes and UI to interact with meta data. The first reference to field api goes all the way back to 2011, in ticket 18179 – WP_Meta_Box. Basically the original idea what to make the need for plugins like advanced custom fields, fields manager and CMB 2 mute by making it easy for developers to register meta boxes and UI for post meta data in core. This would make the job of plugin / theme developers much easier, as it would make adding new fields the post screen extremely easy and would mean that testing on WordPress core updates, much simpler. Plugin / theme developers have long wanted this, as building out custom meta, is a key part of the developing anything custom for WordPress. I believe it true to say, that it is the first thing that most WordPress developers do when building a project, is to set up either ACF or CMB 2. This setup is normally a manual process, as currently there is no way to require interdependencies with plugins (if this plugin /theme is installed, then install this library / plugin). This gives developers two choices. Either hard code the meta boxes / fields that you want and build out their own libraries or bundle a meta box library in with their plugin. Either solution is not great. First, building your own meta boxes, takes time and testing. It means that whenever WordPress core updates itself, that plugins require retesting. Option 2, means having a whole library in the plugin, that adds overhead. If any other plugin is including the same library, it may mean that having both plugins activate at once will break your site.

So having the a fields api in core seems to make sense, from both a user and developer level. It makes developers lives easier and makes WordPress a more usable piece of software for users. But the fields api should not just be limited to post meta, users, terms and comments all to have meta in core. Also multisite too, will have some meta tables, with some work that I am currently working on, by adding site (blog) and network ( site ) meta. So my previous blog post on the subject on site (bl0g) meta and check on the tickets for site and network meta. It is possible to make the fields api the interface for all meta data in core. However, I have also seen people discussing the idea of using the fields api, as a general api for drawing UI for any data stored in the database. That would mean it could also be used in on the core objects like Posts, Users and Options. Where on the face of it, this may seem like a good idea, I am a strongly against this. For me, this muddies the water of the fields api and makes the scope of the project limitless. It makes the project a silver bullet for the project and nearly impossible to ship. The fields api project has been going since 2013 and has had a number different versions and teams working on it. For some reason, this project hasn’t taken hold or got the support it should have. What is worse, is with Matt’s three focuses of customiser, editor and API that this and many other projects are being sidelined.

An API driven future

The REST api was merged in WordPress 4.7 and is started to be used widely by the WordPress community, even by the new editor Gutenberg. It is fair to say with the raise of frameworks like Vue and react, that the world is going towards a more javascript central front end. Matt himself said, “learn javascript deeply” in his state of the Word. For the core data types like Posts, this is fine, as these endpoints are already in core. However, you may we shocked to find that there is little support for meta data in the core API. This means react based Calypso can only handle editing core data types and limited meta keys. It means that if you have a plugin / theme registers a meta boxes, this simply doesn’t appear in the Calyso interface. This is of course a horrible user interface, as settings once important part of workflows on desktop (wp admin interface), just disappear in Calyso or on the iOS or Andriod apps.

The reason for limited meta support in the core endpoints is explained in Ryan Mccue’s post The (Complex) State of Meta in the WordPress REST API. In short, meta data isn’t added core endpoints for two reasons.
1. Protected data
  Core developers have no idea of knowing what data is private / sensitive and should not be displayed publicly.
2. Serialised data
  WordPress can store serialised objects in meta data and these can be a security problem. There is no way of knowing data type
There is much more to Ryan’s post, do check it out. But it comes down to one problem, meta is not registered anywhere, so there is no way to know, if it should be in the api or not, it’s data type, what are valid values and permissions to read / edit / delete are not defined.

A new hope

In WordPress 4.6 Jeremy Felt made some changes to a little known function called register meta. These changes were designed to allow developers to register some details about the meta data. It allows for some important information to be registered, such as.
- Authorization callback
  Adds a permissions call to each meta key. Allow for secure meta data
- Sanitize callback
  Allows for meta data to formatted on save, meaning data meta can be trusted to be the correct format / data type.
- Type
  Does type hinting, so gives core an idea of what data type of data stored.
- show_in_rest
  Allows developers to flag weather or not this meta data in the api at all.
As you might understand, this is a big step forward for meta data. For the first time, meta data is much more control on it’s processing, handling and saving.

This register meta function, is basically the fields api. It would require little work to core, we could add some new params to this function to define the kind of input that is displayed to user (select, textarea, date, text etc), a save callback, location of input and default value(s). Once these params are in, this could be returned API. Once in the API, these fields could be read by something like Calypso and metabox / meta data could automatically register this interface. This would make plugins automatically supported by javascript interfaces. It would just require the javascript interface to implement the fields.

It also makes improvement to multisite a lot easier. Multisite has been missing a settings api for a long time. As under the hood, network settings are just (site) meta data, the fields api could replace the need for a network settings api.

This doesn’t even mean that plugins like ACF would die as a plugin. I would still have a place, support fields that core didn’t like repeaters and google maps. But under the hood ACF could be converted to use the fields api, converting many sites already using this plugin, a more structured data set in the database.

Conclusion

With the fields api in core, we can work on having the user experience an enjoyable and consistent user interface across all platforms. With form fields being generated from one place, we can make the process of translation and accessibility much easier, making aWordPress and much more user friendly piece of software. It would allow those using the WordPress via apps or third party connected software, have a much consent experience and hopefully mean less breakable in the future.

But this gets down to the core issue, I have with the WordPress project. The user comes first. All the work that is currently being done on core, is about making end user experience better. I understand this desire, but it forgets one simple thing, that developers are users too. That a part like having a fields api, is a problem for both developers and users, and WordPress would be better if we solved this problem ahead of other things. The fields api, makes things cleaner, faster and more reliable for custom development. It means that developers, can save time and add value to the projects they are working on in other ways.

Thanks

I would like to thank some people for their tireless work to date.

Thanks go out to Jeremy Felt, Ryan McCue and Eric Andrew Lewis
Adding the Networks endpoint to WordPress core

Jonny Harris

07/09/2017
So as part of the Nijmegen WordCamp, I run the core team at the contributor day. Even through this took up a lot of my time, I still had a little time to work on networks endpoint for core. There was an existing ticket for this and it is on the Multisite roadmap that we have been working on. But of the two require endpoints for full support of the multisite is core (sites and networks), networks may seem like the less important of the two. After all, networks are created rarely (in most cases only once) compared to sites, where sites can be created all the time. However, there is one important feature of multisite that doesn’t have api rest endpoint that really needs it and that is network options. Adding a networks endpoint would add an api not only for networks but also it’s options (meta) as well.

Network options, a history

Network options (or site options as they are sometimes known) different from site level options and are not a feature of WordPress that many people know about it. Network level options are used to core network level configuration in multisite, however not many plugins bother with network level settings. This is partly because many developers do not bother to test their plugins with multisite. The other reason is there isn’t currently a developer api to add network pages and settings easily. For single site WordPress, there is a settings api that makes it easy for developers to build out there own settings panels in the admin terminal. However, there is nothing like this for multisite. Currently the process adding a network options is manual, adding to manual posts requests and manually saving values in the database. This means that developers have to do all their own data sanitation and escaping themselves. There is a ticket to add a similar network settings api written by one Joost de Valk the creator of WordPress SEO however this ticket at time of writing is over 7 years old. With a new endpoint for networks in core, we have an opportunity to improve the whole network options interface. Once there is api for networks, developers can use the CRUD (create, read, update and delete) methods to interact with options and not need to have to do all the data sanitation themselves. We can can extend this more, by creating a network settings interface, that allows developers to register settings to network settings pages. There network settings pages, then become a single page application written in React or similar. This could result in some really nice user interface and make network settings a better than site level settings.

Blocker to this bright future

Even through there is now a patch up for a networks endpoint, there is a lot of work to be done. Currently the first patch, only has read methods. This is because in core there are not currently create, edit and delete network functions. To add this functionality to the api, the functions must be added to core. There is a ticket for this that are based on some of the code in WP Multi Network plugin. The reason these functions have no been added before, is because it would then mean that WordPress core officially support multi network setups. Core has unofficially support multi network for a while, with many commits (#40486, #40590, #40591) going in the last couple of releases. Adding these functions to add a maintenance cost as well, the multisite codebase is large and only has a couple of maintainers work on it at any given time.

Another blocker are the current CRUD functions for network options. Currently there are following functions
- get_network_option
- add_network_option
- update_network_option
- delete_network_option
I worked on these functions as part of the 4.2 release and were introduced in #34777 . However, these functions were just a simple replacement for the existing *_site_options functions. I wanted to go further with these functions and use it as a chance to leverage the meta api. Under the hood, network options are stored in a sitemeta table. This means that data is structured like meta in the database. This means that the meta api used by post / user / comment / term meta could be used here. It adds a number of key benefits, including
- Improved caching profile
- New filters and actions
- Enables use of register_meta
- All improves to meta api, filter down to network options.
- Possible integration with fields api
I have been working on a ticket for a while now, however this is not a small change. It fundamentally change network options and that has effect on every piece of code that uses them. An example of the effect is if code is saving network options for id 0. It is impossible to have a network with id 0, however some plugins have been using 0 as a way to store global settings. This would break if we use the meta api doesn’t allow for 0 or negative numbers.

As you can see, there is a lot of work to done to make this a networks endpoint happen, but I think it is really worthwhile. Once this work is done, it will make developing for multisite much easier.

How you can help

The key tickets that need feedback and code review are
Any help with these tickets is more than welcome. You can also join in on a weekly multisite meetings on slack in the #core-multisite channel every Tuesday at 4pm UTC.
Introducing site meta

Jonny Harris

26/08/2017
There is a new feature that is coming to WordPress multisite is and it is called site meta. This new functionality, was originally purposed by John James Jacoby and it is key building block for the future of multisite. But to understand it’s importance, you must first understand how WordPress multisite is built.

The basics

There are two key concepts that you have to understand about multisite before continuing, sites and networks. Basically, once you install multisite, it installs some new tables in the database. These tables are used to store data from the networks and sites. The default installs of multisite installs one network and a single sites. All sites are part of a network, but it is also possible to have a multi network. See JJJ’s amazing wp-multi-network for more detail. Currently if you want to extending multisite, it is easy to extend networks, as there is already a network settings, which stores networks meta table. Developers can already create their own network options pages and store data as a network. Plugins like yoast and jetpack already do this. But if you want to extend a site, it not currently possible. Developers can store site level data in the options table for each site. This is fine, however, within the multisite context it is hard to query this data out the database because how the data is stored. Take for example, if you wanted to query sites by site name this is impossible. The only way search by name, to get all sites and loop around each one, switch to site and compare the result in php. Something like this.
```
$site_name = 'spacedmonkey';
$site_ids = get_sites(['fields' => 'ids', 'number' => -1]);
foreach($site_ids as $site_id){
    switch_to_blog($site_id);
    $option = get_option('sitename');
    if($option == $site_name){
      // Found it!
    }
    restore_current_blog();
}
```
There are plugins that extend currently extend multisite. The most popular of which is WordPress MU domain mapping which installs a new table ‘wp_domain_mapping’ in the database. Installing new tables into database isn’t a bad thing, but it can be problematic. Firstly, the installation of said table isn’t guaranteed to work. There can be issues database permissions or a table of that name already exists. Also any non standard table, all caching will have to handled manually in code by the developer.

The site meta adds a new table into core database, that enables developers store per site data in a uniform way. There will be a full API to interact with this data in databases. The new functions include.
- add_site_meta
- delete_site_meta
- edit_site_meta
- get_site_meta
- delete_site_meta_by_key
- update_sitemeta_cache
This will make it easy for developers to extend multisite and make many things possible. The new table will on all new multisite installs and the new table will be added when you run the database upgrade in the network admin screen. The is a new helper function called is_site_meta_supported what automatically detects if the table exists.

This new feature is currently in development, your input is value, please leave your comments in trac.

Category: Multisite

Making user queries in WordPress scale

Development improvements in WordPress 5.1

Multisite

Users

Options

Meta api

Thumbnails

Coding standards

Debug

The state of fields API and Meta in core.

So what is the fields api anyway and not having it is such a problem?

An API driven future

A new hope

Conclusion

Thanks

Adding the Networks endpoint to WordPress core

Network options, a history

Blocker to this bright future

How you can help

Introducing site meta

The basics