Make WordPress Core

Opened 9 years ago

Last modified 5 days ago

#37687 new enhancement

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

Reported by: fliespl's profile fliespl Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Networks and Sites Keywords: good-first-bug has-patch has-test-info
Focuses: multisite, performance Cc:

Description

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.

Change History (8)

#1 in reply to: ↑ description @fliespl
9 years ago

  • Component changed from General to Networks and Sites
  • Focuses multisite added

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

Last edited 9 years ago by fliespl (previous) (diff)

#2 @ocean90
9 years ago

  • Focuses performance added

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

This ticket was mentioned in Slack in #core-performance by swissspidy. View the logs.


4 months ago

#4 @swissspidy
4 months ago

  • Keywords needs-patch good-first-bug needs-testing added
  • Milestone changed from Awaiting Review to Future Release

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 ticket was mentioned in PR #8341 on WordPress/wordpress-develop by @sainathpoojary.


4 months ago
#5

  • Keywords has-patch added; needs-patch removed

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

#6 in reply to: ↑ description @sainathpoojary
4 months ago

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!

#7 @SirLouen
5 weeks ago

  • Keywords needs-test-info added; needs-testing removed

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?

#8 @SirLouen
5 days ago

  • Keywords has-test-info added; needs-test-info removed

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_optionswp_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_optionswp_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

Last edited 5 days ago by SirLouen (previous) (diff)
Note: See TracTickets for help on using tickets.