New to the community? Start here

Best way to check and optimize large multisite database

Hello,

I'm hoping you could point me in the right direction. I'm having server performance issues and it seems to becoming from a large multisite database I'm running. TheBusinessCore.com

The website is setup for a local college and the business students each get their own site on the network. Lately we have been having issues with the server slowing down and even crashing at times when the student are in class and about 30 or so using the site in the computer lab at the same time.

I've added lightspeed to the server and that has help.

The Host, WHC.ca, said that there are errors in the PHP error log and that I should check and optimize the DB.

Here's what the host sent me:

"On checking the PHP error logs I can see that below error logs been showing up for

this site so this seems to be with your DB.

30-Oct-2018 06:17:17 UTC] WordPress database error Table

'thebus_core.wp_509_options' doesn't exist for query SELECT option_value FROM

wp_509_options WHERE option_name = 'blogname' LIMIT 1 made by

require('wp-blog-header.php':wink:, require_once('wp-load.php':wink:,

require_once('/home/thebus/wp-config.php':wink:, require_once('wp-settings.php':wink:,

do_action('plugins_loaded':wink:, WP_Hook->do_action, WP_Hook->apply_filters,

UltimateBrandingPublic->load_modules, ub_load_single_module,

include_once('/plugins/ultimate-branding/ultimate-branding-files/modules/favicons.php':wink:,

ub_favicons->__construct, ub_favicons->set_options, get_blog_details, get_option

[30-Oct-2018 06:17:17 UTC] WordPress database error Table

'thebus_core.wp_509_options' doesn't exist for query SELECT option_value FROM

wp_509_options WHERE option_name = 'siteurl' LIMIT 1 made by

require('wp-blog-header.php':wink:, require_once('wp-load.php':wink:,

require_once('/home/thebus/wp-config.php':wink:, require_once('wp-settings.php':wink:,

do_action('plugins_loaded':wink:, WP_Hook->do_action, WP_Hook->apply_filters,

UltimateBrandingPublic->load_modules, ub_load_single_module,

include_once('/plugins/ultimate-branding/ultimate-branding-files/modules/favicons.php':wink:,

ub_favicons->__construct, ub_favicons->set_options, get_blog_details, get_option

[30-Oct-2018 06:17:17 UTC] WordPress database error Table

'thebus_core.wp_509_options' doesn't exist for query SELECT option_value FROM

wp_509_options WHERE option_name = 'post_count' LIMIT 1 made by

require('wp-blog-header.php':wink:, require_once('wp-load.php':wink:,

require_once('/home/thebus/wp-config.php':wink:, require_once('wp-settings.php':wink:,

do_action('plugins_loaded':wink:, WP_Hook->do_action, WP_Hook->apply_filters,

UltimateBrandingPublic->load_modules, ub_load_single_module,

include_once('/plugins/ultimate-branding/ultimate-branding-files/modules/favicons.php':wink:,

ub_favicons->__construct, ub_favicons->set_options, get_blog_details, get_option

[30-Oct-2018 06:17:17 UTC] WordPress database error Table

'thebus_core.wp_509_options' doesn't exist for query SELECT option_value FROM

wp_509_options WHERE option_name = 'home' LIMIT 1 made by

require('wp-blog-header.php':wink:, require_once('wp-load.php':wink:,

require_once('/home/thebus/wp-config.php':wink:, require_once('wp-settings.php':wink:,

do_action('plugins_loaded':wink:, WP_Hook->do_action, WP_Hook->apply_filters,

UltimateBrandingPublic->load_modules, ub_load_single_module,

include_once('/plugins/ultimate-branding/ultimate-branding-files/modules/favicons.php':wink:,

ub_favicons->__construct, ub_favicons->set_options, get_blog_details, get_option

I suggest you to check and optimize the Database for your website.Please contact

your developer and ask him/her to address your site issues."

Could you point me in the right direction on optimizing the multisite? Or if you can tell if there is something else going on with the site and server that I should be looking at.

Thanks for the help,

Jay

Tue, 30 Oct 2018 11:27:00
  • splaquet
    • WordPress Warrior

    How often do you optimize your database? Are you using InnoDB or MyISAM storage container? If you’re currently using MyISAM, you might see some performance increase by swapping over to InnoDB.

    **WARNING**WARNING**

    Take it from someone who’s previously played with InnoDB, without knowing how to properly stop/start/edit my.cnf, in the correct order.

    **BACKUP**BACKUP**

    I’m not nearly the pro on this stuff as the WPMU folks are… but that looks less like a true database error and more of a plugin and/or subsite issue. SubSite #509 specificalily.

    Do all of the SubSites use the same theme? I have a hunch that #509 is using a different one…

    Here’s a quick and dirty one liner that you can use on the root level in ssh. It runs a MySQL Check & Repair on all DBs on the server:

    [root@]# mysqlcheck -o -A

  • jayjay
    • Site Builder, Child of Zeus

    Hi splaquet ,

    We are using InnoDB. That subsite #509 has been deleted. That’s why I think we are getting the errors.

    We do have some of the older subsites are using a different theme but all the new sites are using Divi by Elegantthemes. Not sure if that would be the cause. I know that Divi needs to have a higher max_execution time.

    I will contact the developer of the site and have him run that MySQL Check & Repair command.

    Thanks for the help,

    Jay

  • Adam
    • Support Gorilla

    Hi jayjay

    I hope you’re well today!

    I second splaquet on that, it’s an excellent advice. But I’d also like to add something :slight_smile:

    First, the error that you quoted. It seems to be coming from our own Ultimate Branding plugin, specifically its “Favicons” module on the site of ID 509. Start with disabling that module for that site and see if the error goes away or if some other error gets “uncovered” (sometimes it happens when an error that you see is actually not a “real” error but the “consequence” of something else; it’s worth checking).

    Second thing is, see if after disabling that module (and if the error goes away and nothing new comes up) the performance is significantly better and the site doesn’t gets down. I doubt this specific error could take the site down but you never know until you test. It’s unlikely but possible that it’s just the proverbial “cherry on top” or a “final nail in coffin”. That has to be checked.

    Then, apart from the database cleanup, repair and optimization, there’s also a matter of the site optimization. The database usually goes down and/or corrupted mostly because the number and/or size of the queries from the site exceeds limits significantly. That’s “clogging the pipe”, so to say, which in turn may cause data corruption.

    The more complex the site is and the more traffic it gets, the more queries it will create. So, the ultimate goal is to make the site as “lightweight” as possible: maybe some themes could be replaced with simpler ones or just custom developed using only core WP features instead of various builders, maybe some plugins could be completely removed or at least kept disabled when not used, some of them might actually also have simpler/lighter versions or be replaced with just some simple custom code… That’s kind of complicated but that alone can sometimes do wonders. But it’s a lot of work and might even sometime require a good developer (by good I mean someone who is capable of creating relatively simple and efficient and well optimized code – which is not that common apparently and not that easy to test!).

    Of course what splaquet already suggested is something to take care of too but also there are quite a few ways to “fine tune” database configuration itself. But again, you need someone well experienced with this and, first of all, your host must allow doing this.

    Finally, there are always some limitations if it comes to hosting so unless it’s a reliable cloud (I’m sorry, I don’t know the host) the “last resort” solution could be moving everything to some serious and scalable cloud like Amazon.

    Best regards,

    Adam

  • splaquet
    • WordPress Warrior

    jayjay , i’d also recommend that you ask your developer to grab & run mysqltuner.pl and/or tuning-primer.sh

    as to what Adam Czajczyk mentioned with regards to ‘mysql being clogged’, i didn’t even think about that at first.

    i’m not sure how many other sites live on the same server instance as this massive multisite, BUT… if it’s only that 1 site, then tuning mysql precisely to your site’s needs can make a HUGE difference. different plugins, themes, operations, etc, all use different types of database calls.

    mysqltuner.pl & tuning-primer.sh will let you know how many ‘operations’ your mysql server needs to use, how many it’s allowed to use, and how optimal the setup/config is for those specific needs.

    also, if you’re using InnoDB, you might want to ask your developer about how much RAM that you have in your machine. again, if it’s the only site on your server, and you’re making *HUNDREDS* of MySQL calls, RAM can be your best friend. And with that, mysqltuner/tuning-primer will let you know how much RAM that you can dedicate to InnoDBs buffer pool.

    also, on a dev server of mine, i’ve installed Redis on it. please don’t quote me, but it essentially takes the most frequently used mysql calls and kicks them back out of cache. so, instead of going back and performing operations 1,2,3,4,5,6,7,8 again, it’ll only perform 1,2,7,8. **serious example and not truly how it works**

    so, on that note… good luck :slight_smile:

  • jayjay
    • Site Builder, Child of Zeus

    Hello splaquet and Adam Czajczyk ,

    I’ve had the developer go in and remove any unnecessary plugins and themes. I’ve also asked the Host to monitor what exactly is using up the resources. I’ve also gone in and removed any non essential websites off the server.

    I only have 39 sites that are on the server. The big multisite is TheBusinessCore.com and it is for the local college. The only time there might be a big load of users on the site was when the students registered in the computer lab at the college. Now the students are doing their sites on their own time. So there hasn’t been a huge number (30+) of students using at the same time.

    This didn’t happen last spring or in past years when students were using the site.

    Here’s the server information:

    Total processors: 8

    Each processor has the following

    Vendor

    GenuineIntel

    Name

    Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz

    Speed

    3799.898 MHz

    Cache

    8192 KB

    Memory information:

    Memory: 5190528k/34340864k available (6871k kernel code, 832956k absent, 794764k reserved, 4502k data, 1760k init

    Physical Disk:

    sd 0:0:0:0: [sda] 468862128 512-byte logical blocks: (240 GB/223 GiB)

    sd 1:0:0:0: [sdb] 468862128 512-byte logical blocks: (240 GB/223 GiB)

    sd 1:0:0:0: [sdb] Write Protect is off

    sd 1:0:0:0: [sdb] Mode Sense: 00 3a 00 00

    sd 1:0:0:0: [sdb] Write cache: enabled, read cache: enabled, doesn’t support DPO or FUA

    sd 0:0:0:0: [sda] Write Protect is off

    sd 0:0:0:0: [sda] Mode Sense: 00 3a 00 00

    sd 0:0:0:0: [sda] Write cache: enabled, read cache: enabled, doesn’t support DPO or FUA

    sdb: sdb1 sdb2 sdb3

    sda: sda1 sda2 sda3

    sd 1:0:0:0: [sdb] Attached SCSI disk

    sd 0:0:0:0: [sda] Attached SCSI disk

    sd 0:0:0:0: Attached scsi generic sg0 type 0

    sd 1:0:0:0: Attached scsi generic sg1 type 0

    Current Memory Usage:

    total used free shared buff/cache available

    Mem: 32734872 2990428 3486736 1388972 26257708 27820860

    Swap: 33552376 0 33552376

    Total: 66287248 2990428 37039112

    I’ve also just added LiteSpeed to the Server which was suppose to reduce the load on the server.

    Not sure if any of this help. Thanks for the help and let me know if you can see anything that would help.

    Regards,

    Jay

  • splaquet
    • WordPress Warrior

    If that’s only hosting that 1 website, you should have plenty enough resources IMO. Also, double check with your developer and make sure that you’re on PHP7.2. It’s MUCH faster than 5.6… which might potentially give you a little added boost, if you don’t already have it activated.