New to the community? Start here

WordPress Multisite crashes server on backup

Hi, we run a SaaS service based on WordPress Multisite and Woocommerce and unfortunately we are hosted by SiteGround. We have about 500 subsites and we run on a Cloud dedicated with this setup:

8 CPUs
34 GB RAM
100 GB SSD

We optimized all the content and scripts that can be optimized and, during the day (when users use the service) we run at 25/30% server capacity with about 10.000 visits per day.

Everything works fine until their auto-backup tool starts (Schedule you can’t avoid, even on managed hosting). So we scheduled it at night (from 1:30 UTC) and it takes about 2 hours to be done. Site starts giving 500 Errors when backup reaches database and server goes down, causing all the connected services to fail (like cron, etc). Server is overloaded for a lot of time, for about 7/8 hours. We can re-start the service usually at 8/9 in the morning. This is clearly not acceptable.From our WPMUdev dashboard we have 78% uptime lol.

After submitting various tickets they told us to reduce DB size because that was the issue. Server got overloaded when trying to backup mySQL server. So we run optimization via WP Cli and reduced DB size from 9GB to 1.6GB, which is, honestly, an adequate size for a wpmu with 500 woocommerce subsites. They keep saying that our mySQL is too huge and that’s te issue, but we have a very powerful server setup, is it possible to really be a 1.6GB mySQL to cause all these issues?

Here’s their final answer: 

Indeed during backup generation the website starts displaying database errors and 500 ISE errors. The reason for that is due to the fact that during backup generation or should I say during database dump, the database is locking its tables:

| 581 | up9wqa9qzzs5k | localhost:46468 | dbkjz5p97rxey7 | Query   | 95   | Waiting for table metadata lock | INSERT INTO hxl_406_woocommerce_sessions (<code>session_key</code>, <code>session_value</code>, <code>session_expiry</code>) VALUES ( | 0         | 0             |

This is an example query which is being stuck and piling up in the MySQL. Now, since it takes a lot of time for the backup to dump your database, for the entirety of the time the tables remain locked and the processes are unable to execute properly which is why the 500 ISE errors are happening. Practically, the entire issue here is caused by the fact that the database is too large and it takes way too much time for the backup creation tool to dump it.

I’m writing here because we optimized everything with your services and they are perfect, so, have you got any advice for us? Can the db be further optimized or we should look for another hosting provider? I’m not a system administrator, but it is difficult to understand how a 1.6GB mySQL server can crash a 34GB/8CPU server at night with no traffic.

Tue, 23 Mar 2021 10:19:07
  • Jorge Berenguer
    • The Bug Hunter

    Hi Alex,

    It seems surprising that a plan that imposes no limits on DB size (Cloud) would then have these issues with the managed backups. It’s likely possible to optimize the DB further, but I agree that the current size and your server resources appear to be sufficient to deal with it. Our hosting does not impose any DB size limits and our hosting backups can deal with large DBs.

    I imagine you will have tried most of the things in this article with WP CLI. If you haven’t done all of them you could try the steps below.

    After adding:define( 'WP_ALLOW_REPAIR', true ); to your wp-config.php file, visit easy-delivery.it/wp-admin/maint/repair.php. Make sure to have a backup of the DB before doing this. You can then “Optimise and Repair” the DB (again, I gather you’ve already done this)

    You can further reduce the size of the DB by doing the following:

    * Reduce the number of post revisions or disable the post revision feature:
    define( 'WP_POST_REVISIONS', 2 );

    or

    define( 'WP_POST_REVISIONS', false );

    This will only apply to new posts though. To free up more space you’d have to delete existing post revisions. You can do so with this plugin or directly from phpMyAdmin.

    * Reduce the time that items are kept on the trash table. You can set it to any day.

    define( 'EMPTY_TRASH_DAYS', 5 ); // 5 days

    * Another thing that can contribute to bloating is WordPress Transients. You can use the Transient Cleaner plugin

    These are some of the main points outlined in the above mentioned blog post. There are other suggestions as well there that are worth looking into.

    I hope this helps.

    Kind regards,
    Jorge.

    • Alex
      • WPMU DEV Initiate

      Hi Jorge,
      thank you very much for your answer. I looked at your plans but is it possible to have a bigger plan than Platinum? Can we talk about it in a separate private thread? I have got a few questions.

      Regarding original question, we had to disable WPMUdev Defender Audit Logging, there was clearly a bug in audit log erasing, which I think has been fixed in recent updates but previous not erased logs were not touched. I’m attaching screenshot, db table grew up to 1,8 Million records.

      After doing that, all the hints you’ve reported are already done, but we keep going down during DB backup. I think that’s more of a provider (Siteground) issue than ours, I really optimized everything. :slight_frown:

  • Jorge Berenguer
    • The Bug Hunter

    Hi Alex,

    Of course, feel free to create a new Forum ticket from the support page and mark the ticket as private (the checkbox at the bottom of the form to make it private should be checked by default). We’ll be happy to answer all your questions there.

    We do have hosting above Platinum, Enterprise hosting. You can fill in the form here and provide as much info as you can about your current setup and characteristics of your site so that the Enterprise hosting team can provide a quote.

    Regarding Defender’s Audit Logging table, you’re right. The setting to control the duration of the log storage was fixed in version 2.4.7. because, indeed, it was causing that table to grow very large. You’ll have to delete the old ones manually.

    Kind regards,
    Jorge.