Support » Fixing WordPress » 3.7 Million Subscribers and radically slow Admin from INNER JOIN wp_usermeta

  • We set up two servers to compare:
    http://dev.oursite.local/wp-admin/edit.php is supposed to load all the posts, it takes forever and we see this at the bottom: (usagedd monitoring tool) 112Q 1.68 94.31s 60.7M and 114 Queries | 1.91s TTFB | 77.56s Page Load | 60.7M Memory Consumed -those big discrepancies between TTFB and total time to load the page are interesting

    http://basicdev.oursite.local/wp-admin/edit.php
    same page, loads instantly and i see this at the bottom 50Q 1.14s 1.21s 46.7M and 50Q 0.89 0.96s 46.7M

    Only difference is that the slower site has 3.7 million users, bulk of them subscribers role.

    When we use Query Monitor we can identify this particular query as being the number one culprit:

    SELECT wp_users.ID,wp_users.user_login,wp_users.display_name
    FROM wp_users
    INNER JOIN wp_usermeta
    ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( wp_usermeta.meta_key = ‘wp_user_level’
    AND wp_usermeta.meta_value != ‘0’ )

    if you remove the check on the wp_usrmeta.meta_value field, the performance is fine. the problem is its running a compare against 3.7M LONGTEXT fields for the value ‘0’

    HAVE SEEN THIS as an identified issue as of 5 years ago. But not finding a concrete solution to it. Some of many external references:

    https://wordpress.org/support/topic/wp_usermeta-3-milion-wor-has-slowly-query/
    https://core.trac.wordpress.org/ticket/28160

Viewing 10 replies - 1 through 10 (of 10 total)
  • Moderator Ipstenu (Mika Epstein)

    (@ipstenu)

    🏳️‍🌈 Plugin Review Team Rep

    So. Good news first! This CAN be handled.

    WordPress.com is that level of WordPress Multisite. And technically so is WordPress.org.

    The bad news is that you’ll need to look into database sharding (HyperDB or SharDB are the most popular options – .com made and maintains HyperDB)

    * https://wordpress.org/plugins/hyperdb/
    * https://wordpress.org/plugins/shardb/

    And that’s bad because those are super complicated and a hassle to maintain. You need a really good sysadmin who’s well versed in database magic.

    @ipstenu thanks for the reply. This is not a multisite though. Single site, but, lots of subscribers. We’re using WordPress to authenticate subscribers. They have a profile which allows they to see scholarships they applied for and such. Could possibly authenticate those users a different way and keep them out of WP?

    Do big sites like NY Times or BBC America handle subscriber users outside of WordPress? Or do they hire sysadmins 🙂

    Moderator Ipstenu (Mika Epstein)

    (@ipstenu)

    🏳️‍🌈 Plugin Review Team Rep

    It’s going to be the same answer. You have a MASSIVE database, so you need to look at sharding.

    As for the subscribers question, those sites DO NOT permit users to sign up via WP. They handle subscriptions (and email) via other systems that are specifically built for that.

    They also hire sysadmins 😀

    also… This plugin hasn’t been tested with the latest 3 major releases of WordPress. It may no longer be maintained or supported and may have compatibility issues when used with more recent versions of WordPress.

    I can certainly understand if that warning message scares you but, with that huge number of users, you need that sharded database.

    The only other suggestion would be to look into something like the Mail Chimp type services that you might offload the subscriber users to but that’s not something I can advise you on.

    That huge ‘join’ needs a database server of its own and that is a very complicated system.

    One thing which might help you in the interim is to consider running a second server with your full database on it instead of having that huge database and the webserver there on a single host. You’ll want that second server ‘in the same building’ as your webserver is in.

    In practice, the quickest, easiest way is to leave the database where it is and copy the files to the new host. Then it’s a quick DNS change for the new host’s IP, a slight tweak to the database entry in the copied over wp-config, and make sure the old host can answer remote database queries.

    You’ll need that second server to get any real benefit from sharding anyway if you later go that route. ShardDB starts off on multiple databases on the same second server.

    HyperDB is probably going to require a minimum of 4 replicated database servers to get any real benefit from that.

    The problem is actually doing a JOIN operation using a table column that isn’t indexed. Neither HyperDB nor SharDB will be of any help to resolve this…they have zero effect on what happens within the MySQL application.

    As you found, removing the conditional on the wp_usermeta.meta_value column resolves the issue. If the table is InnoDB and you have an extra 16GB of memory floating around, you can increase the innodb_buffer_pool_size value in my.cnf to 16G and hope the query remains cached. You can also put MySQL on its own dedicated server that has a fast CPU and clock speed, and at least six cores. And I suppose you can write a plugin that caches the IDs of the users that match the conditional, and alters the query to use the ID list instead of a JOIN to wp_usermeta.

    Oh…those discrepancies between TTFB and total time are almost always due to a plugin/theme making inefficient queries after headers have been sent. This case is the exception; the WP core itself is making an inefficient query.

    My thinking was to throw more horsepower at the problem which is the huge database by splitting off the server tasks. ShardDB seems a good fit for that as you can then dedicate one database to the users tables and the other shards to split the load.

    The query? I didn’t really worry about that too much as that’s quite outside of the WordPress itself and I believe the OP was advised to consider hiring a good database admin.

    As the OP was concerned about the two plugins I figured I’d throw my two cents in there.

    3.7 million users is quite large. At those numbers, you need lots of horsepower and some really good help.

    The problem is running queries on non-indexed columns. You could split the database across 10 servers, and the problem will still exist because one unlucky server will be running that inefficient query. The query time may drop from 90 seconds to 15 seconds if the unlucky server was fast and highly optimized, but it will still be unacceptably large. The best solution is to do whatever possible to avoid queries using the meta_value column in WHERE, AND, GROUP BY, ORDER BY, etc clauses. I take that back…the best solution is a redesign of the _????meta tables.

    Thank you everyone! We’re trying a few different paths. Will report out how they worked out by the end of this week.

    We added a key on wp_usermeta {meta_key, meta_value} and performance is much improved. They key should be in that order.

Viewing 10 replies - 1 through 10 (of 10 total)
  • You must be logged in to reply to this topic.