Welcome to Centmin Mod Community
Register Now

MariaDB MySQL i/o traffix causing High Load, solutions?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by nikolaikapustin, Dec 29, 2016.

Tags:
  1. nikolaikapustin

    nikolaikapustin Member

    37
    5
    8
    Oct 5, 2016
    Ratings:
    +7
    Local Time:
    2:28 AM
    Hello,

    Ramnode just wrote me: "Your MySQL was using 60-70MB/s i/o usage consistently and was severely impacting the node. That type of usage/traffic unfortunately is not going to work in a shared environment. If you can not reduce the load you may have to consider looking into moving to a dedicated server environment."

    and they shutdown the machine (3 gb, 4 core, ssd).

    The vps is hosting a 700mb db, php 7, mariadb 10, nginx 11.1.8, redis cache. Just a wordpress site. Is there a way to monitor mysql behaviour? Can i set a limit to mariadb?

    Thank you.
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    woah that's crazy for 60/70MB/s !

    could be a wordpress plugin issue or your wordpress install may have been compromised ?

    with machine shutdown, there's no way to find out what was causing the specific mysql i/o load though.

    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation or troubleshooting of the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    Checking PHP-FPM etc logs



    You'll also need to check into your PHP-FPM, Nginx and MariaDB logs which you can find as outlined at How to troubleshoot Centmin Mod initial install issues

    Server logs include Nginx, PHP-FPM, MariaDB MySQL error logs as well as others. You can find your Centmin Mod install/menu logs at FAQ 7 and server logs at FAQ 19 at Centmin Mod FAQ (most up to date info in FAQ so always read that first). Spoiler tag below has info too but may not be up to date.

    However, there's many linux tools and scripts that can help you figure out what was causing the load issues and when.

    Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.
    Notes:
    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    also did you edit /etc/my.cnf and/or add or enable additional settings ? enabling slow query logging, general log or binary logging on a very active mysql usage site can result in alot of mysql related disk i/o being written as well.

    do you use wordfence ? i haven't used it but it has a feature for Enable Live Traffic View that writes an insane amount of data to mysql database if your wordpress site is under attack or highly traffic'd ? could be related

     
    Last edited: Dec 29, 2016
  4. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    8:28 AM
    1.11
    10.2
    That indicates you don't have Full Page caching for guest.
    Wp Super Cache or WordPress Cache Enabler Plugin should help minimize that.
     
  5. SFLC

    SFLC Active Member

    224
    59
    28
    Dec 4, 2016
    The Canadas
    Ratings:
    +112
    Local Time:
    2:28 AM
    1
    10
    Just as @eva2000 mentioned, i'm willing to bet it was a plugin you had installed, wordpress is interesting that way, how are you going to look into this with your server being off though?

    It would be an inconvenience for you to have to move to a dedicated server to resolve your problem then move back, if you didn't want/need the server.
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,899
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    8:28 AM
    1.11
    10.2
    High Traffic Sites can reach this.
    My XF forum has an average of 30mb per second i/o.
     
  7. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    yes wordpress full page caching would help minimise the disk i/o though really depends on what is causing the issue from what i read Wordfence enable live traffic view scans and writes to database itself, so no full page caching would prevent that though
     
    • Like Like x 1
  8. negative

    negative Member

    216
    21
    18
    Apr 11, 2015
    Ratings:
    +49
    Local Time:
    3:28 AM
    1.9.10
    10.1.11
    How can we measure the mysql (or other services like elastic, nginx, php-fpm, postgresql) i/o usage on server ? I want to see that. Thanks
     
  9. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    posted links above to tools to monitor everything including disk i/o

    Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.
     
  10. Xon

    Xon Active Member

    130
    57
    28
    Nov 16, 2015
    Ratings:
    +171
    Local Time:
    8:28 AM
    1.11.x
    MariaDB 10.1.x
    I've seen Wordpress websites with a lack of database indexes do this. Fulltable scans on their session table for every page load over ~3 million rows does that :p

    Or a malformed join which pulls in ~22 billion rows, so when a bot crawls the broken page with a bad SQL query it fills up ~80gb of diskspace; then MySQL falls over and the temp diskspace is freed.

    As a provider; I think I "fixed" that one by making MySQL hard error if that MySQL server encounters a join which pulls in +1 million rows or a select which returns that many rows.
     
    • Informative Informative x 1
  11. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    sounds like only something Wordpress plugins would cause rather than Wordpress core code ?
     
  12. Xon

    Xon Active Member

    130
    57
    28
    Nov 16, 2015
    Ratings:
    +171
    Local Time:
    8:28 AM
    1.11.x
    MariaDB 10.1.x
    Yes, but wordpress plugin can do crazy things to the default schema. And if you upgrade from some ancient version (or don't upgrade at all :eek:) there might be a missing schema/index upgrade.
     
    • Agree Agree x 1
  13. eva2000

    eva2000 Administrator Staff Member

    30,131
    6,778
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,128
    Local Time:
    10:28 AM
    Nginx 1.13.x
    MariaDB 5.5
    indeed :)