How To Reduce server mysql load on Multisite Networks?

Hello,

We're running a dedicated server with 7 different Wordpress networks, with approximately 20-40 sites per network. The websites are primarily informative, involving a low amount of images. Nothing too crazy going on with them.

Our server specs are as follows:
- Intel Core i3-3220 3.30 Ghz
-
- 2 drives 1000GB each
- 12,000GB bandwidth

mysql is running around 300-400% CPU and is making the server unstable - causing it to crash randomly.

We aren't currently using any cache plugins, maybe that would help?

Do you have any suggestions on reducing the load of mysql?

  • tony_cd

    @
    dfathers,

    A few questions you need to review are: what's your uptime? did you ever have this problem before? what changes recently (configuration, more user activity?...). Ask the host provider if there's any problem with your system.

    Anyway, it seems like you have a hung process or a bad query (ie.e table scan big multi table joined). It's hard to say unless you spend time profiling and tracking it down. Easy solution:
    - shutdown and restart mysql database
    - if that doesn't work shutdown and restart linux

    If that worked, great but you still don't really know what and why it happened. However, if it happened frequently, then you really have to spend time to identify the culprit. Good luck...

  • dfathers

    Hi Tony,

    Thank you for the reply.

    We don't have uptime measuring in place currently - However we're having to reboot the server almost daily.

    We've been having the problem for almost a month now - to my knowledge there haven't been any configuration updates on the server.

    The hung process / bad query sounds like it could be the culprit. What is the process to try and isolate an issue like this?

    ----

    Hi Kimberly - We aren't using a WP dedicated host - what are the main differences between a WP server vs a standard Linux Dedicated?

  • tony_cd

    @dfathers,

    You definitely have a big problem because you have to reboot it daily, especially since WP's running in linux/mysql environment, not Winblows :slight_smile: I normally have to go to low level linux/mysql (login into the box) and start monitoring & trouble-shooting. It's a tedious process. If we can identify the culprit (very likely application level) then we can give feedback to developer to fix it. I can help you (yes, FREE as in beer) if you trust and give me access to the box.

    Regards...

  • aecnu

    Greetings dfathers,

    Thank you for your great question and I am chiming in as Kimberly requested.

    WP server vs a standard Linux Dedicated

    The biggest difference between the two though identical in hardware is the configurations.

    The i3-3220 indeed has a fair bit of power rating of 4,277 on my benchmarking scale and though you mentioned the two hard drives - you did not mention the speed of the hdd's which should be no less then 7200 rpm or Enterprise grade.

    Bandwidth is irrelevant since unless the host throttles it like Blue Host does, it does not come into play.

    You did not mention RAM either.

    As Mark De Scande demonstrates here:

    https://premium.wpmudev.org/forums/topic/what-would-be-the-best-hosting-for-a-network#post-288348

    Even with great expensive hardware the server may not perform and it indeed comes down to configuration - software configuration included.

    Now I am not going to give away trade secrets that have taken years to learn and have my servers out performing the rest and their specific configurations but I will also submit that you should run an optimizer like this from ssh:

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    That should clean things up a bit and restore some performance.

    If you need or want further consulting on making this server run much better let me know and at $80 an hour with a one hour cap I can probably get it performing much much better. But I will need to dig in using ssh to make this happen.

    Just let me know aecnu (at) incsub.com

    The above ssh command should indeed help and restore some performance.

    Cheers, Joe

  • ez123abc

    It is more than likely a database problem, and it should be fixed.

    This is my two cents..

    Talk to your hosting provider, let them fix the database...See, I'm a lazy butt, sure they might or might not charge, but it will be taken care of. But with all the sites you have, just use the money that you are charging to pay for it.

    Still, it doesn't mean you have to play very nice with the hosting provider, have them take care of it. They want your business too, so they will be glad to help.

    If you are not sure what you are doing, ask your hosting provider, but before you do, make sure you back it all up, then hand them the work.

    My two cents

  • aecnu

    Greetings dfathers,

    As you know I am hot on it and I have indeed identified some issues and the plugins causing the problem pretty much limited to one site causing all the grief.

    So in this case not even Multi DB will break that one sites db's up.

    But if we figure out the exact plugin causing the grief bringing the server to its knees, we should bring it to the authors attention the grief it is causing and see if they can come up with a way for it to more efficiently address the database.

    I have been monitoring your site today on and off and it is sure that you are having intermittent DDOS attacks as well.

    Some of the IP have been certainly blocked by cPanel but I would further recommend Word Fence plugin be added to all you network sites and have it block countries that are not in your market. I have no vested interest in your purchasing a Word Fence license in any way shape of form - it is a clean recommendation.

    I also wanted to install Fail2ban but it is not yet compatible with Centos 6.+

    I will indeed keep you posted and look forward to stopping these garbage resource draining attacks.

    Cheers, Joe

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.