Memory usage for scalable usermeta queries

by

Problem

Some time ago I posted a question about the scalability of wp_usermeta architecture: my concern, as my client’s database of users is growing fast, is now the memory usage of querying the wp_usermeta table.

In my situation, I am managing a CRM with thousands of users, each of which with around 15 meta fields. As I am getting this informations out of the database, the memory usage is exponentially growing, as much that now the 96M I set as a limit is not enough.

Data I collected (testing, profiling)

I have tried profiling and refactoring my code, by narrowing down exactly what I have to do, and I indeed optimized my queries as much as I could, depending on the situations:

  1. In most situations, I have to get the metadata associated to one user only. In this case, both running get_user_meta and get_userdata work fine.
  2. In some situations, I have to get the some metadata (~5 fields) associated to some users: using get_userdata exhausts my memory after ~2000 users; using get_user_meta with no $key (hence getting all the user associated metadata) exhausts my memory after ~3500 users; getting only the $keys I need actually exhausts the memory after ~3000 users.
  3. In a few situations, I have to get some metadata (~5 fields) associated to all users: this is where, obviously, I feel the problem the most.

I have tried several options: using the WordPress APIs is apparently highly inefficient for this kind of situation, as I pointed out above. Examining the saved queries on $wpdb->queries also gives me a huge amount of unnecessary queries (I guess this is why getting all metas above is more efficient than getting a few metas).

It must be noted that, I also have to run a preliminary get_users to get the IDs to run the above mentioned functions on.

Since all those APIs also cache the results, I thought it might be a good idea to run my own DB queries, and indeed I save up loads of memory, using just around a total of 40M for around ~6000 users (my total now) with this query:

$wpdb->get_results( 
    "SELECT user_id, meta_value
     FROM $wpdb->usermeta
     WHERE meta_key='a' OR meta_key='b' OR meta_key='c' OR meta_key='d'"
);

Questions

  1. Am I doing something wrong in the approach?
  2. I have never worked with this amount of data, what is the memory usage I should expect for this kind of situation? (In order to understand my hosting needs)
  3. Would I be better off making a new database table? (The only thing that came to my mind, but I didn’t try yet).
  4. If the custom DB query is a viable approach, how should I organize the data collected by that query in some WP_User-like object structure?

1 Answer
1

Just to put here the suggestion that came up after discussing the requirement on chat.

Due to the complexity of user filtering/search required, the wordpress user data tables is not a very good place to store the user’s attributes. Instead it will make more sense to have a CPT and related taxonomies to store the user attributes required for the GUI, while maintaining basic information in the wordpress user table for login/authentication purposes.

Leave a Comment