Does Wordpress ever need multiple databases?

Question

Is there ever a reason to run a Wordpress install that uses more than a single Database to run? Like for instance, could a Wordpress installation get so large or gain so many users that it needs another database? I'm asking because of a situation where I learned of a Wordpress install that claims to use two MySQL databases to run on and I'm uncertain as to why that would even be necessary.

Answer 1

could a Wordpress installation get so large or gain so many users that it needs another database?

If you mean sharding of a database to spread data across multiple servers for sheer quantity of data, then yes, but only for 3 or 4 websites in existence, aka WordPress.com.

If you think you need this for your site or a clients site, then the answer is no you do not. We're talking ultra high traffic multisites with tens of millions of active worldwide users, and tens of billions of page views per day.

How do normal multi-database server setups differ from what you described?

Normal multi-DB servers don't split up data, they're just copies that load balance so that more requests can be served. Each copy contains the full database, usually with a single server acting as the source of truth that handles writes/updates/deletes and replicates those changes to the others.

You might see this in a dual load balancing configuration, though normally these are unnecessary as the operators are unaware of persistent object caches or frontend caches such as varnish.

This is also something an administrator or hosting company might upsell you on if you're a high traffic client who spends a lot. WordPress does not require it, but maybe it will get faster if you throw cash at the problem? ( Sometimes it does! Everything has to work in some situations right? )

What Would It Take To Require Sharding of Data?

For a WordPress site to require multiple database servers in the way you describe, you would need HyperDB to shard data across servers, and you would need a multisite serving tens of millions of users and sites.

A prime example of this is WordPress.com.

Note that a lot of fortune 100 sites running WordPress at enterprise scale serving billions of page views per day do not use sharded databases such as those described in your question.

So unless you're planning to enter the world of ultra high scale WordPress Enterprise hosting and work at Automattic or a major WordPress hosting company, this is purely academic and not useful. Even then most roles in those companies do not need to know about this.

Disclosure: I used to work at Automattic on their VIP Enterprise hosting division

I'm asking because of a situation where I learned of a Wordpress install that claims to use two MySQL databases to run on and I'm uncertain as to why that would even be necessary.

What's likely is that the site had poor performance, and the people running it decided to use a second database server to increase the number of requests it could handle. This is likely due to the use of poor queries, and using uncached functions in the site.

Installing Redis and a persistent redis object cache dropin, or memcached/mongodb equivalents, would have significantly improved performance without the need to add a second database server. Likewise avoiding NOT IN type queries, or filtering/searching for posts by their post meta values/fields would significantly improve performance and traffic scaling. Logged out traffic can also be cached via tools such as Varnish or equivalent full page caches that bypass PHP altogether.

Most people are unaware that if they follow several basic common sense rules such as those then they can host high traffic sites on the cheapest of Digital Ocean plans. Sadly most people will also rely heavily on those things and consider such advice silly. Most plugins and themes rely heavily on post meta searches for example.

Answer 2

@TomJNowell is right. In general in the database world, scaling up via server replication or scaling out via sharding are last resorts after improving table keys and query algorithms. It's really expensive to maintain multiple replicas or shards, both in server costs and people time. You're frequently troubleshooting some problem or other, and frequently at night. Ask me how I know this when you have a few hours to spare.

Adding better table keys and fixing some lookup algorithms is the first step to scaling up a site. The next step is adding a persistent object cache (such as redis). The third step is provisioning a bigger MariaDB / MySQL server machine with enough RAM for a big buffer pool.

I have written a couple of free non-monetized GPL plugins to address the keying and lookup algorithm issues. The WordPress performance team is aware of this work; I hope they make my plugins obsolete sometime soon.

For rekeying, try using Index WP MySQL For Speed.

You mentioned sites with many users having poor performance. Try using Index WP Users For Speed to address that problem.

The database-query code in core WordPress is grossly inefficient at handling sites with tens of thousands (or more) of registered users. It does queries like this (simplified) example, to find the first few "editor" users.

SELECT CALC_FOUND_ROWS wp_users.ID
  FROM wp_users
  JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID 
                  AND wp_usermeta.meta_key = 'wp_capabilities'
 WHERE wp_usermeta.meta_value LIKE '%"editor"%'
 ORDER BY wp_users.display_name
 LIMIT 20

This query contains the notorious MySQL performance killer meta_value LIKE '%something'. It's a performance killer because the leading % wildcard requires MariaDB / MySQL to examine the data for every user. In db jargon, it must do a full table scan on both wp_users and wp_usermeta. (See you after lunch. :-)

It does queries like this every time you display the Users, Posts, or Pages panels in your dashboard.

The Index WP MySQL For Speed plugin addresses it by adding some easier-to-access rows to wp_usermeta. It changes that example query to

SELECT wp_users.ID
  FROM wp_users
  JOIN wp_usermeta ON wp_usermeta.user_id = wp_users.ID 
                  AND wp_usermeta.meta_key = 'wp_iufsr:editor'
 ORDER BY wp_users.display_name
 LIMIT 20

That query exploits a key on wp_usermeta and finishes in a few milliseconds, even on sites with many users.