Multisite - Enormous number of update queries during site creation (user roles)

[fliespl]

This is mostly because the wp_roles+caps are added one by one causing a very nice overhead.

query: INSERT INTO `wp_86_options` (`option_name`, `option_value`, `autoload`) VALUES ('wp_86_user_roles', 'a:1:{s:13:\"administrator\";a:2:{s:4:\"name\";s:13:\"Administrator\";s:12:\"capabilities\";a:0:{}}}', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)

query: UPDATE `wp_86_options` SET `option_value` = 'a:2:{s:13:\"administrator\";a:2:{s:4:\"name\";s:13:\"Administrator\";s:12:\"capabilities\";a:0:{}}s:6:\"editor\";a:2:{s:4:\"name\";s:6:\"Editor\";s:12:\"capabilities\";a:0:{}}}' WHERE `option_name` = 'wp_86_user_roles'

query: UPDATE `wp_86_options` SET `option_value` = 'a:3:{s:13:\"administrator\";a:2:{s:4:\"name\";s:13:\"Administrator\";s:12:\"capabilities\";a:0:{}}s:6:\"editor\";a:2:{s:4:\"name\";s:6:\"Editor\";s:12:\"capabilities\";a:0:{}}s:6:\"author\";a:2:{s:4:\"name\";s:6:\"Author\";s:12:\"capabilities\";a:0:{}}}' WHERE `option_name` = 'wp_86_user_roles'


query: UPDATE `wp_86_options` SET `option_value` = 'a:5:{s:13:\"administrator\";a:2:{s:4:\"name\";s:13:\"Administrator\";s:12:\"capabilities\";a:3:{s:13:\"switch_themes\";b:1;s:11:\"edit_themes\";b:1;s:16:\"activate_plugins\";b:1;}}s:6:\"editor\";a:2:{s:4:\"name\";s:6:\"Editor\";s:12:\"capabilities\";a:0:{}}s:6:\"author\";a:2:{s:4:\"name\";s:6:\"Author\";s:12:\"capabilities\";a:0:{}}s:11:\"contributor\";a:2:{s:4:\"name\";s:11:\"Contributor\";s:12:\"capabilities\";a:0:{}}s:10:\"subscriber\";a:2:{s:4:\"name\";s:10:\"Subscriber\";s:12:\"capabilities\";a:0:{}}}' WHERE `option_name` = 'wp_86_user_roles'

[...] One hunder queries later:

UPDATE `wp_86_options` SET `option_value` = 'a:5:{s:13:\"administrator\";a:2:{s:4:\"name\";s:13:\"Administrator\";s:12:\"capabilities\";a:61:{s:13:\"switch_themes\";b:1;s:11:\"edit_themes\";b:1;s:16:\"activate_plugins\";b:1;s:12:\"edit_plugins\";b:1;s:10:\"edit_users\";b:1;s:10:\"edit_files\";b:1;s:14:\"manage_options\";b:1;s:17:\"moderate_comments\";b:1;s:17:\"manage_categories\";b:1;s:12:\"manage_links\";b:1;s:12:\"upload_files\";b:1;s:6:\"import\";b:1;s:15:\"unfiltered_html\";b:1;s:10:\"edit_posts\";b:1;s:17:\"edit_others_posts\";b:1;s:20:\"edit_published_posts\";b:1;s:13:\"publish_posts\";b:1;s:10:\"edit_pages\";b:1;s:4:\"read\";b:1;s:8:\"level_10\";b:1;s:7:\"level_9\";b:1;s:7:\"level_8\";b:1;s:7:\"level_7\";b:1;s:7:\"level_6\";b:1;s:7:\"level_5\";b:1;s:7:\"level_4\";b:1;s:7:\"level_3\";b:1;s:7:\"level_2\";b:1;s:7:\"level_1\";b:1;s:7:\"level_0\";b:1;s:17:\"edit_others_pages\";b:1;s:20:\"edit_published_pages\";b:1;s:13:\"publish_pages\";b:1;s:12:\"delete_pages\";b:1;s:19:\"delete_others_pages\";b:1;s:22:\"delete_published_pages\";b:1;s:12:\"delete_posts\";b:1;s:19:\"delete_others_posts\";b:1;s:22:\"delete_published_posts\";b:1;s:20:\"delete_private_posts\";b:1;s:18:\"edit_private_posts\";b:1;s:18:\"read_private_posts\";b:1;s:20:\"delete_private_pages\";b:1;s:18:\"edit_private_pages\";b:1;s:18:\"read_private_pages\";b:1;s:12:\"delete_users\";b:1;s:12:\"create_users\";b:1;s:17:\"unfiltered_upload\";b:1;s:14:\"edit_dashboard\";b:1;s:14:\"update_plugins\";b:1;s:14:\"delete_plugins\";b:1;s:15:\"install_plugins\";b:1;s:13:\"update_themes\";b:1;s:14:\"install_themes\";b:1;s:11:\"update_core\";b:1;s:10:\"list_users\";b:1;s:12:\"remove_users\";b:1;s:13:\"promote_users\";b:1;s:18:\"edit_theme_options\";b:1;s:13:\"delete_themes\";b:1;s:6:\"export\";b:1;}}s:6:\"editor\";a:2:{s:4:\"name\";s:6:\"Editor\";s:12:\"capabilities\";a:34:{s:17:\"moderate_comments\";b:1;s:17:\"manage_categories\";b:1;s:12:\"manage_links\";b:1;s:12:\"upload_files\";b:1;s:15:\"unfiltered_html\";b:1;s:10:\"edit_posts\";b:1;s:17:\"edit_others_posts\";b:1;s:20:\"edit_published_posts\";b:1;s:13:\"publish_posts\";b:1;s:10:\"edit_pages\";b:1;s:4:\"read\";b:1;s:7:\"level_7\";b:1;s:7:\"level_6\";b:1;s:7:\"level_5\";b:1;s:7:\"level_4\";b:1;s:7:\"level_3\";b:1;s:7:\"level_2\";b:1;s:7:\"level_1\";b:1;s:7:\"level_0\";b:1;s:17:\"edit_others_pages\";b:1;s:20:\"edit_published_pages\";b:1;s:13:\"publish_pages\";b:1;s:12:\"delete_pages\";b:1;s:19:\"delete_others_pages\";b:1;s:22:\"delete_published_pages\";b:1;s:12:\"delete_posts\";b:1;s:19:\"delete_others_posts\";b:1;s:22:\"delete_published_posts\";b:1;s:20:\"delete_private_posts\";b:1;s:18:\"edit_private_posts\";b:1;s:18:\"read_private_posts\";b:1;s:20:\"delete_private_pages\";b:1;s:18:\"edit_private_pages\";b:1;s:18:\"read_private_pages\";b:1;}}s:6:\"author\";a:2:{s:4:\"name\";s:6:\"Author\";s:12:\"capabilities\";a:10:{s:12:\"upload_files\";b:1;s:10:\"edit_posts\";b:1;s:20:\"edit_published_posts\";b:1;s:13:\"publish_posts\";b:1;s:4:\"read\";b:1;s:7:\"level_2\";b:1;s:7:\"level_1\";b:1;s:7:\"level_0\";b:1;s:12:\"delete_posts\";b:1;s:22:\"delete_published_posts\";b:1;}}s:11:\"contributor\";a:2:{s:4:\"name\";s:11:\"Contributor\";s:12:\"capabilities\";a:5:{s:10:\"edit_posts\";b:1;s:4:\"read\";b:1;s:7:\"level_1\";b:1;s:7:\"level_0\";b:1;s:12:\"delete_posts\";b:1;}}s:10:\"subscriber\";a:2:{s:4:\"name\";s:10:\"Subscriber\";s:12:\"capabilities\";a:2:{s:4:\"read\";b:1;s:7:\"level_0\";b:1;}}}' WHERE `option_name` = 'wp_86_user_roles'

Possible idea: instantiate wp_roles with use_db false during blog creation and force saving roles data into database after populate_roles(); function.

[fliespl]

This was supposed to be marked as "Multisite" instead of General.

[ocean90]

I had SAVEQUERIES enabled while creating a new site and got a fatal error because the memory size was exhausted.

This is the value of $GLOBALS['wpdb']->queries before the wpmu_new_blog action is fired: ​https://gist.github.com/ocean90/016029ea76ce70f574283e0b27910731#file-log-L156

[swissspidy]

Possible idea: instantiate wp_roles with use_db false during blog creation and force saving roles data into database after populate_roles(); function.

That makes sense to me.

This PR optimizes user role updates during site creation in a multisite network. Previously, user roles were updated individually, leading to multiple database queries. This change batches role assignments, reducing the number of queries and improving performance.

Trac ticket: #37687

[sainathpoojary]

Possible idea: instantiate wp_roles with use_db false during blog creation and force saving roles data into database after populate_roles(); function.

Hey @swissspidy,

I have implemented the suggested solution in the PR. Please have a look whenever you have a moment.

Thank you!

[SirLouen]

Bug Reproduction Report

Description

❌ This report can't validate that the issue can be reproduced.

Environment

• WordPress: 6.9-alpha-60093-src
• PHP: 8.2.28
• Server: nginx/1.27.5
• Database: mysqli (Server: 8.4.5 / Client: mysqlnd 8.2.28)
• Browser: Chrome 136.0.0.0
• OS: Windows 10/11
• Theme: Twenty Thirteen 4.4
• MU Plugins: None activated
• Plugins:
  • Query Monitor 3.17.2
  • Test Reports 1.2.0

Bug Reproduction Steps

1. Activate Multisite
2. Install Query Monitor Plugin
3. Go to Network Sites /wp-admin/network/sites.php
4. Open Query Monitor and Add Site
5. ❌ I can't really see a significant number of queries after creation of a new Site in the multisite setup.

Additional Comments

Although the problem seems legit, I can't seem to reproduce it
@fliespl @sainathpoojary @ocean90 can any of you provide with some Testing Instructions or see what I'm doing wrong to reproduce this issue?
Since this issue was mentioned 9 years ago, maybe it has been already sorted by other means?

[SirLouen]

Test Report V2

Description

✅ This report validates that the indicated patch works as expected.

Patch tested: ​https://github.com/WordPress/wordpress-develop/pull/8341.diff

Environment

• WordPress: 6.9-alpha-60093-src
• PHP: 8.2.28
• Server: nginx/1.27.5
• Database: mysqli (Server: 8.4.5 / Client: mysqlnd 8.2.28)
• Browser: Chrome 137.0.0.0
• OS: Windows 10/11
• Theme: Twenty Twenty-Five 1.2
• MU Plugins: None activated
• Plugins:
  • MS Site Creation Query Tracker 1.0.0
  • Test Reports 1.2.0

Test Reproduction Steps

1. Activate Multisite
2. Install my Plugin: ​https://raw.githubusercontent.com/SirLouen/ms-site-creation-tracker/refs/tags/1.0.0/ms-site-creation-tracker.php
3. Go to Network Admin > Sites > Site Tracker
4. Add a new site
5. By default, it's reporting 545 queries
6. Also go to the database and save the record content wp_x_options ⇾ wp_X_user_roles (being X the site ID)

Expected Results

1. There are many unnecessary queries because each single add_role and add_cap inside the populate_roles executes a query only if the wp_db property is set to true
2. Verify if the record in wp_x_options ⇾ wp_X_user_roles is identical before and after the patch.

Actual Results

1. ✅ Issue resolved with patch: Reporting, by default, 201 queries
2. ✅ Roles creation are identical pre and post patch.

Additional Notes

• After my first report, I wasn't very satisfied with the result by using the Query Monitor Plugin, so I put this on my backlog to make a little plugin for further testing. On the paper this report made all sense, but I was unable to reproduce it, so I was uncertain of what was going on, maybe wp_db was being disabled at some other point or something 🤷‍♂️. Now with my plugin, I've been able to clearly verify the problem and confirm the hypothesis.

Supplemental Artifacts

Link to test plugin Repo: ​https://github.com/SirLouen/ms-site-creation-tracker

[rishabhwp]

Test Report

Description

This report validates whether the indicated patch works as expected.

Patch tested: ​https://github.com/WordPress/wordpress-develop/pull/8341.diff

Environment

• WordPress: 6.9-alpha-60093-src
• PHP: 8.2.28
• Server: nginx/1.27.5
• Database: mysqli (Server: 8.4.5 / Client: mysqlnd 8.2.28)
• Browser: Chrome 137.0.0.0
• OS: macOS
• Theme: Twenty Twenty 2.9
• MU Plugins: None activated
• Plugins:
  • MS Site Creation Query Tracker 1.0.0
  • Query Monitor 3.18.0
  • Test Reports 1.2.0

Actual Results

1. ✅ Issue resolved with patch.

Observation:
Prior to applying the patch: ~550 queries during site creation
After applying the patch: ~220 queries

Supplemental Artifacts

Number of queries before patch

Number of queries after patch

[SirLouen]

6.9.0 Milestone proposed during today's <bug-scrub>
​More information

Pinging MS team @realloc @spacedmonkey

[realloc]

@spacedmonkey @johnjamesjacoby The changes look good to me.

[spacedmonkey]

This looks nearly ready to commit. One thing to consider, this change has no test coverage. Has adding tests been considered @realloc @sainathpoojary?

[SirLouen]

Replying to spacedmonkey:

This looks nearly ready to commit. One thing to consider, this change has no test coverage. Has adding tests been considered @realloc @sainathpoojary?

This is a performance upgrade, on MS site creation. Only performance tests can be been run as it has been done.

Unit tests for this are the site creation tests themselves (which I believe is already done).

What tests do you expect?

[johnjamesjacoby]

What tests do you expect?

In this case, I think testing that add_role() and $role->add_cap() result in an identical end result when use_db is either true or false is sufficient; no need to test the upgrade routine specifically.

Set it to true, add everything, get the roles, delete them.
Set it to false, add everything, get the roles, compare them.

What do you think?

[peterwilsoncc]

I've posted a change request on the PR to account for a couple of situations:

• Sites using the $wp_user_roles global
• Sites with the use_db flag set to false prior to populate_roles running

I think @johnjamesjacoby's suggestion for tests above is good.

@SainathPoojary Thank you for updating the code with the suggestions.

I've taken the liberty of pushing some code to your branch but there are no functional changes to your code:

• added the test @JJJ suggested on the ticket
• A minor coding standards fix for ​indentation
• Merged in trunk.

[peterwilsoncc]

The ​pull request tests well and looks good to commit.

I've moved this to the Upgrade/Install component as the same issue is present when creating a single site install.

The number of queries when populating roles is reduced from 347 to 3.

[peterwilsoncc]

In 60614:

Upgrade/Install: Reduce number of DB queries populating roles.

Reduces the number of database queries made when populating roles during install/multisite site creation by 344 (347 queries down to 3).

populate_roles() has been modified to prevent an individual database query each time a role or capability is added to the WP_Roles object. Instead the roles option, {$wpdb->prefix}user_roles is updated once at the end of the function call.

Introduces a test to ensure that updating the roles option via WP_Roles and updating the option in the manner now used by populate_roles() results in the same capabilities been applied to a role.

Props fliespl, johnjamesjacoby, ocean90, realloc, rishabhwp, sainathpoojary, sirlouen, spacedmonkey, swissspidy.
Fixes #37687.

Merged in r60614 / ​https://github.com/WordPress/wordpress-develop/commit/cc8cc8a67ca6423d64df6e0465d1b77fdc122a67