Speed up multisite/network installs

[ablears]

Hi, I see another open request about supporting WP networks. While a single UI at the network parent level would be great, I can use a bash script to loop through all sites applying the WP CLI command, so it's not a huge issue personally.

But, I run several Woo networks, with one hosting over 200 Woo (using HPOS) sites. And adding to cart on this network is slow (around 5 seconds round-trip), presumably due to the number of sites on the network. Caching is Object Cache Pro / WP Rocket/Varnish/Cloudflare APO, and it's only uncached actions like adding to cart that are slow. I was hopeful this plugin would suggest database keys for the WP network level, but it doesn't seem to have any suggestions. Just adding this in case it could be useful for others.

[rjasdf]

Is this issue about installing a plugin on hundreds of sites? Or about the speed of a web page.(add to cart, etc)? Our plugin is aimed mostly at the latter.

Can you provide the SQL statements that are causing the slowdown?

[OllieJones]

Hello, sorry it took me a while to notice this issue.

Multisite has a couple of tables that could stand to have new keys added. But if you're using Till Kruess's Object Cache Pro with a performant redis server, the boost from the keys will probably be minimal.

With that many WooCommerce subsites on a single multisite network, you are operating a MariaDB / MySQL database (schema) with many thousands of tables in it. And, you're bottlenecking all your database operations for all those stores through a single database server. That's a heavy load that requires specialized database server tuning. Open-file cache sizes might be insufficient, for example.

If you were to use this plugin to upload metadata and some monitors, we could take a look.

[OllieJones]

There are two multisite-specific tables, wp_site and wp_sitemeta.

Here are SQL statements to convert the keys on those tables from the default prefix keys to non prefix keys, and to make them into slightly faster compound keys.

ALTER TABLE wp_sitemeta
   DROP KEY meta_key, ADD KEY meta_key(meta_key,site_id), 
   DROP KEY site_id, ADD KEY site_id(site_id, meta_key);
ALTER TABLE wp_site
   DROP KEY domain, ADD KEY domain(domain, path);

You can put these SQL statements into phpmyadmin or another SQL client. Or you can use wp-cli to run them like this.

wp db  query "ALTER TABLE wp_sitemeta DROP KEY meta_key, ADD KEY meta_key(meta_key,site_id), DROP KEY site_id, ADD KEY site_id(site_id, meta_key);"
wp db  query "ALTER TABLE wp_site DROP KEY domain, ADD KEY domain(domain, path);"

Again, I don't think this rekeying job will help much. Those tables aren't very large even with 200 sites in your multisite installation. But it is worth a try.

And, I'm reluctant to suggest how to tune a database with thousands of tables, especially without looking at it myself. But you may find this to be informative. https://dev.mysql.com/doc/refman/8.0/en/table-cache.html

[ablears]

Thank you for the suggestions Ollie. Yes, I was looking for multisite table optimisations, and I'll run those via WP-CLI. Unfortunately the monitors returned 0 results, so they are not capturing any data on any of the sites I tried this on. It works on the staging server but not production. But not to worry, I think this plugin has done everything it can.