[wp-trac] [WordPress Trac] #49545: update_meta_cache issue while getting record from user meta table in multi site

[WordPress Trac]

#49545: update_meta_cache issue while getting record from user meta table in multi
site
-------------------------------------------------+-------------------------
 Reporter:  classicalrehan                       |      Owner:  (none)
     Type:  defect (bug)                         |     Status:  new
 Priority:  normal                               |  Milestone:  Awaiting
                                                 |  Review
Component:  Users                                |    Version:  5.3.2
 Severity:  blocker                              |   Keywords:
  Focuses:  accessibility, multisite,            |
  performance, coding-standards                  |
-------------------------------------------------+-------------------------
 Hi All,

 I am facing a strange wp problem. I have a multisite setup and around 10K
 sites are running over it. Lots of sites are created with superuser i.e.
 user_id=1


 every time when site hit in the browser then following query executed:

 Filename: wp-includes/meta.php
 Method: update_meta_cache
 Line No: #825
 My Application Traffic: 50million/day

 SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (1)
 ORDER BY umeta_id ASC;

 Problem:

 if suppose 5k sites created with user_id=1 and then whenever traffic come
 over my application then it will fetch all rows against this user_id =1
 from user meta table and as now around 20k ROWS_EXAMINED and ROWS_SENT in
 every query but I, in reality, it will require only 4-5 rows based on
 current blog id (site id).

 There should be a check for blog id (site id) inside this function because
 other returning row is useless and due to this my query going slow.

 I have tried the following things:

 1. Change usermeta table engine. MyISAM to InnoDB but its not work There
 is no possibility of row-level locking, relational integrity in MyISAM but
 with InnoDB this is possible. MyISAM has table-level locking [Not worked]

 2. Try table engine MyISAM to Memory but it's also not working because
 usermeta contain meta_value and whose data type blob but unfortunate
 Memory engine will not work with blob so can't able to change the engine
 to Memory [Not worked]

 3. Can I comment this function call or add return null [Not try as of now
 because this is wp core file and whenever I will update wp version it will
 show conflict and also not a good approach to change wp core file]

 4. Can I some other cache inside here? [not tested because again it's wp
 core file]

 5. Can I add current blog ID check inside this function and retrieve only
 those records whose belong to this site only [but again this check need to
 put inside wp core file ie. wp-includes/meta.php]

 6. is there any hooks or something which I can use over here?

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49545>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform