update_meta_cache issue while getting record from user meta table in multi site

[classicalrehan]

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?

[afercia]

@classicalrehan thanks for your report. I'm going to remove the accessibility focus as this issue doesn't seem related to web content accessibility (WCAG and the like). Seems something more appropriate for the Multisite team.

[classicalrehan]

Replying to afercia:

@classicalrehan thanks for your report. I'm going to remove the accessibility focus as this issue doesn't seem related to web content accessibility (WCAG and the like). Seems something more appropriate for the Multisite team.

@afercia can you please help me to solve this issue as tried everything but nothing going work until unless I change the core file which I want to avoid.

Is there any way where I can override "update_meta_cache"?

As I check in function.php with different hook i.e. init, wp_head, etc cache available but update_meta_cache always update the result set.

[classicalrehan]

can someone help me to resolve this issue?

[SergeyBiryukov]

Hi there, welcome to WordPress Trac! Thanks for the report.

Just removing the update_cache_meta keyword as it's not one of the ​workflow keywords, adding dev-feedback instead to follow up later.

[classicalrehan]

@SergeyBiryukov is there any solutions you would suggest?

Replying to SergeyBiryukov:

Hi there, welcome to WordPress Trac! Thanks for the report.

Just removing the update_cache_meta keyword as it's not one of the ​workflow keywords, adding dev-feedback instead to follow up later.

[spacedmonkey]

Related: #58001