/ Register

Learn about Centmin Mod LEMP Stack today
Become a Member

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

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

Tags:
Previous Thread Next Thread
Loading...
  1. Dec 29, 2016 #1
    nikolaikapustin

    nikolaikapustin Member

    38
    5
    8
    Oct 5, 2016
    Ratings:
    +7
    Local Time:
    1:32 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. Dec 29, 2016 #2
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Dec 29, 2016 #3
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Dec 30, 2016 #4
    rdan

    rdan Well-Known Member

    5,451
    1,412
    113
    May 25, 2014
    Ratings:
    +2,206
    Local Time:
    7:32 AM
    Mainline
    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. Dec 30, 2016 #5
    SFLC

    SFLC Active Member

    223
    59
    28
    Dec 4, 2016
    The Canadas
    Ratings:
    +112
    Local Time:
    1:32 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. Dec 30, 2016 #6
    rdan

    rdan Well-Known Member

    5,451
    1,412
    113
    May 25, 2014
    Ratings:
    +2,206
    Local Time:
    7:32 AM
    Mainline
    10.2
    High Traffic Sites can reach this.
    My XF forum has an average of 30mb per second i/o.
     
  7. Dec 30, 2016 #7
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  8. Jan 1, 2017 #8
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:32 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. Jan 1, 2017 #9
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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. Jan 1, 2017 #10
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    7:32 AM
    1.15.x
    MariaDB 10.3.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.
     
  11. Jan 1, 2017 #11
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    sounds like only something Wordpress plugins would cause rather than Wordpress core code ?
     
  12. Jan 2, 2017 #12
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    7:32 AM
    1.15.x
    MariaDB 10.3.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.
     
  13. Jan 2, 2017 #13
    eva2000

    eva2000 Administrator Staff Member

    55,425
    12,257
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,840
    Local Time:
    9:32 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    indeed :)
     
Previous Thread Next Thread
Loading...

.