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