dbDelta uses unrestricted "show all" which doesn't scale or work with DB sharding plugins
|Reported by:||brianlayman||Owned by:|
The dbdelta function does a straight "show tables" which is not scalable to large multisite installs.
The routine is built to look at and upgrade or create all of the tables in the 'global' table list (blogs, sitemeta & etc) as well as tables for the individual site being upgraded (comments, options & etc). In the current version, it asks MySQL to list all of the tables it knows and then iterates that entire list picking out just those tables listed above. On large sites, this simple "show tables" request can take 5+ minutes to complete.
This patch uses the standard MySQL show tables call with a where clause to limit the query to the just the global tables and the tables that start with the prefix for the site we are looking at.
It should be fully compatible with both standalone and MS WordPress installations.
It requires MySQL 5.0 and therefore will only work with WordPress 3.2+
I use this in the field with two independent clients each with more than 10,000 sites in their MS network. With over 90,000 tables, the one site would have taken 8 days to complete its upgrade. With this patch, it took less than 4 hours to complete the upgrade. The other network was timing out with every new user that signed up. This caused inconsistent issues ranging from tables never being created for the blogs to the default themes and settings never being applied. Sometimes, if another blog was created right away, a blog creation would succeed. I think this was when the query result was still cached by the MySQL server.
Change History (29)
comment:4 brianlayman — 2 years ago
- Keywords reporter-feedback removed
- Milestone changed from Awaiting Review to 3.3
comment:10 uglyrobot — 2 years ago
- Keywords needs-patch added; has-patch removed
- Summary changed from New Site & Upgrade process uses unrestricted "show all" causing timeouts to dbDelta uses unrestricted "show all" which doesn't scale or work with DB sharding plugins