I have developed a CRM plugin for a client integrated with WordPress user management and I stored additional information for each user under the wp_usermeta table.
However, the client’s customer database is growing exponentially, and we are now in the thousands, which gives us several tens of thousands rows in wp_usermeta: at this point I am starting to be worried about scalability of this architecture.
Does anybody have any experience on managing such an amount of users the WordPress way? Should I add columns to the wp_users table instead of relying on the wp_usermeta one? How can I diagnose/profile WordPress and my own code performance at this stage?
I have never worked on such an amount of data and at this increasing rate, and any pointer would be valuable.
The size of the table isn’t really the issue, the queries you’re running on that table might be.
For example, if you’re selecting users based on data stored in the user-meta table, then that query will be highly unoptimized, because meta_value is not an indexed field. In which case you may need to add extra indexes or consider storing that particular data in a different manner, such as with a custom taxonomy.
Generally speaking, stuff that you store as meta should never be something that you will exclusively search based on. This applies across all the meta tables in WordPress. Meta is mainly designed to be pulled out by the meta_key, not by the meta_value. Taxonomies limit the possible values to a set and organize the information differently, so they do better when the “value” counts as what you’re selecting on.
Note, selecting by both meta_key and meta_value is generally okay, because mySQL will optimize the query to be based on the meta_key first, reducing the amount of data to search to a (hopefully) manageable limit. If even this becomes a problem, you can “fix” it by adding a new index to the meta table with both meta_key and meta_value on the index, however because meta_value is LONGTEXT, you need to limit the length of that index to something reasonable, like 20-30 or something, depending on your data. Note that this index may be much, much larger than your actual data and will drastically increase the storage space needed. However, it will be much faster at those types of queries. Consult a qualified DBA if this ever becomes a real issue.
For reference, on WordPress.org we have approximately 11 million users registered. The amount of meta varies per user, with probably a minimum of 8 rows per, and maybe a max of around 250-ish. The users table is about 2.5 GB, the usermeta table around 4 GB. Seems to run okay, for the most part, but every once in a while we find some oddball query that we have to go optimize.
Unless you are running your own queries instead of using the API, the size of table doesn’t matter that much as wordpress runs queries by the indexes of the table and MYSQL supposed to optimize this kind of queries. Each query also fetches all meta information in one query.
If you insist you can split the user meta table into several tables using an hash on the user ID as the table name, but then you will probably have to write a replacement to the wp_db class to access the right table based on the query. If you are interested in following this path then look for solutions for handling big network installs with many blogs.
But if you don’t have any performance issues now then you can grow much further without doing any significant adjustment. When you start getting performance issues just move the DB to a faster server, it will be more cost effective than any manipulation you can do to the way WP access that info.