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: |
|
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
@
9 years ago
- Component changed from General to Networks and Sites
- Focuses multisite added
#2
@
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
@
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
@
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
@
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
- Activate Multisite
- Install Query Monitor Plugin
- Go to Network Sites
/wp-admin/network/sites.php
- Open Query Monitor and
Add Site
- ❌ 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
@
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
- Activate Multisite
- Install my Plugin: https://raw.githubusercontent.com/SirLouen/ms-site-creation-tracker/refs/tags/1.0.0/ms-site-creation-tracker.php
- Go to Network Admin > Sites > Site Tracker
- Add a new site
- By default, it's reporting 545 queries
- Also go to the database and save the record content
wp_x_options
⇾wp_X_user_roles
(being X the site ID)
Expected Results
- There are many unnecessary queries because each single
add_role
andadd_cap
inside thepopulate_roles
executes a query only if thewp_db
property is set totrue
- Verify if the record in
wp_x_options
⇾wp_X_user_roles
is identical before and after the patch.
Actual Results
- ✅ Issue resolved with patch: Reporting, by default, 201 queries
- ✅ 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
This was supposed to be marked as "Multisite" instead of General.