Want to subscribe to topics you're interested in?
Become a Member

Wordpress Sudden high mysql usage problem

Discussion in 'Blogs & CMS usage' started by julliuz, Apr 10, 2019.

  1. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    Hi there

    We've been using centminmod for 4 months now and it has been blazing fast and secure!

    Since this week we've been having a problem however.
    I checked TOP and its showing mysqld is using 60-120% CPU , it never did this before and traffic has remained the same throughout the last months.

    Is there any guidelines on how to find the culprit ? I don't think we are running anything new that might be causing this.

    Thank you :)
     
  2. eva2000

    eva2000 Administrator Staff Member

    39,758
    8,769
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,494
    Local Time:
    3:06 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Wordpress ? what web app ?

    If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.

    FYI, CPU load optimal is 1.0 per cpu thread so 8 cpu thread normal = 8x1 = 8.0. 16 cpu server normal is 16x1 = 16.0

    so cpu load of 8 for
    • 2 cpu server = 8/2 = 4.0 load average
    • 4 cpu server = 8/4 = 2.0 load average
    • 8 cpu server = 8/8 = 1.0 load average
    When you type top command, hit number 1 to break down per cpu stats

    Centmin Mod is provided as is, so troubleshooting load issues is left to end user to do. However, there's many linux tools and scripts that can help you figure out what was causing the load issues and when.

    If you're providing info on this forum, more info might be helpful
    1. What version of Centmin Mod ? .08 stable or .09 beta ? If .09 beta when was it installed and when was last time you updated ?
    2. What's your VPS/Server hardware specifications ? cpu type ? memory available ? disk space ?
    3. Who's your web host ? Different hosts have different limits for server resource usage and some are more restrictive that others so it could just be their resource usage policy you tripped which may or may not be restrictive. If restrictive, then real solution would be finding a better web host. If not restrictive, then it's finding out what caused high cpu load.
    4. If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.
    Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.
    Notes:
    For example what's output for these commands
    Code (Text):
    top -bn1
    

    Code (Text):
    ps aufxw
    

    Code (Text):
    sar -q
    

    Code (Text):
    sar -r
    

    Code (Text):
    sar -q -f /var/log/sa/sa$(date +%d -d yesterday)
    

    Code (Text):
    sar -r -f /var/log/sa/sa$(date +%d -d yesterday)
    

    If you want to look at per process usage stats use pidstat run pidstat every 1 sec for 5 runs and sed is just to replace your main hostname with word hostname so mask your domain for posting on public places
    Code (Text):
    pidstat -durh 1 5 | sed -e "s|$(hostname)|hostname|g"
    

    for posting code you might want to use CODE tags for code How to use forum BBCODE code tags :)
     
  3. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    Hey !

    I always keep it updated, its the latest beta centminmod.
    As I said though, it ran fine for 3 months, low cpu usage, super speed. Nothing changed except MYSQLD starts using lots of cpu. (visitors and plugins remained the exact same)

    I started digging some more and with ngxtop I found out that autoptimize is doing 40req/sec of js and css files for some reason. I don't see how this can be related but it seems odd that after enabling log files it gets flooded with requests from that particular plugin...

    I wish there was a tool like ngxtop to analyze what mysqld is doing.
     
  4. rdan

    rdan Well-Known Member

    4,523
    1,084
    113
    May 25, 2014
    Ratings:
    +1,582
    Local Time:
    1:06 AM
    Mainline
    10.2
    MariaDB 10.3?
    10.3 for me tend to be CPU hungry compare to 10.1/10.2.
     
  5. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    No it is still 10.1 but I think I'm on to something.

    Cache enabler doesn't cache people that are logged in, I have over 3000 people that are logged in now and they seem to be creating enormous amounts of queries because of that. How could I enable cacheing for logged in people as well ? WP supercache used to do that but cache enabler does not.

    Or a way to change cacheing without breaking everything that was setup by centminmod?
     
  6. eva2000

    eva2000 Administrator Staff Member

    39,758
    8,769
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,494
    Local Time:
    3:06 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    yeah cache enabler can't do logged in user caching but i personally haven't used wp super cache for logged in users so can't see first hand. You can follow the cache enable removal guide at https://community.centminmod.com/threads/disable-or-remove-keycdn-cache-enabler.15657/ first and try wp super cache plugin install though it would be missing some of the stuff centmin.sh menu option 22's wp super cache install does but you can manually uncomment the wp super cache include file in your nginx vhost

    edit: question for cache enabler logged in user caching posted ages back at Caching for logged in users ? · Issue #19 · keycdn/cache-enabler interesting a forked version of cache enabler may have logged in user caching # This is a combination of 20 commits. · RedCastor/[email protected] and # This is a combination of 21 commits. · RedCastor/[email protected] ???

    forked version differences keycdn/cache-enabler

    edit: ok forked version doesn't have logged in user cache from change log
    Code (Text):
    == Changelog ==
    = 2.0.2
    * Add bypass if WP_CACHE defined and false
    = 2.0.1
    * Add user can editor clear cache options
    = 2.0.0
    * Add some third party plugins functionality (woocommerce and wpml)
    


    edit: looks like it wasn't a fork but just a fork for pull request sake Add cleaner class for third party plugins compatibility by auban · Pull Request #36 · keycdn/cache-enabler ?
     
    Last edited: Apr 11, 2019
  7. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    Well, I figured out thats not the problem. I force logged out all users and the mysqld usage remained very high :(

    So weird, 3 months, 1 to 2 cpu load max even when there was 1000 people online. Nothing changed, same wordpress, same theme, same kind of plugins.

    any tips/apps that might help me track down the queries that are doing this ? Or any other apps to trace down heavy usage sources? (ngxtop turned up nothing special)
     
  8. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    IOtop is fine, nginx isn't showing anything out of the ordinary, network load is low, just mysqld is using tons of cpu for whatever reason and its making me crazy, I was able to pinpoint stuff in the past but this is a special case.

    0% slow queries as well, doesn't make things easier.
     
    Last edited: Apr 11, 2019
  9. eva2000

    eva2000 Administrator Staff Member

    39,758
    8,769
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,494
    Local Time:
    3:06 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
  10. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    thank you, i'll go deep and report back
     
  11. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    @eva2000 I figured out it was a simple widget in the sidebar that wasn't cached and causing major problems. Simply disabling it solved to performance issues.

    Only have 1 question left.
    Sometimes we have 1000 visitors at the same time, without a problem.
    We have cache enabler option turned off to clear cache on new post but after posting or updating anything in any post, it always clears entire cache causing 5minutes of pure carnage where everyone lags like crazy.

    Checked all the options, I'm 100% sure its disabled everywhere so it should only clear cache every 24hours and not on every small detail we change. Might there be an option in config files I missed ?

    And a happy easter to you :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    39,758
    8,769
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,494
    Local Time:
    3:06 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    happy holidays !

    what was the widget/plugin name ?

    sounds like cache enabler bug then so might want to check with keycdn folks keycdn/cache-enabler

    tried disabling cache enabler plugin from wp admin plugin management page, and re-enabling it ?
     
  13. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    Widget was simply part of the theme called "bimber" which is otherwise completely cache enabled. The widget shows top6 most viewed posts and it has to requery all of them each pageview.

    I'll try re-enabling it, haha, who knows! :)
     
    • Informative Informative x 1
  14. julliuz

    julliuz New Member

    21
    2
    3
    Dec 20, 2018
    Ratings:
    +5
    Local Time:
    7:06 PM
    1.15
    @eva2000

    is there a way to switch from cache enabler to something else without breaking the internal workings of centminmod ? I ask because we start to have too many people that are logged in and its a heavy toll on the servers as cache enabler does not enable cacheing for logged in users. For us it doesn't matter as all pages are static.
     
  15. eva2000

    eva2000 Administrator Staff Member

    39,758
    8,769
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,494
    Local Time:
    3:06 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    If you ran centmin.sh menu option 22 and selected KeyCDN Cache Enabler as your Wordpress caching choice but now want to disable Cache Enabler, you can do so using following steps below:

    1. Disable Cache Enabler WP Plugin from WP Admin Plugin area

    2. In your nginx domain.com.conf and/or domain.com.ssl.conf vhost config file, comment out (add hash # in front) the Cache Enabler try_files rule and uncomment the standard Wordpress try_files rule for WP permalinks
    Code (Text):
      # for wp cache enabler plugin
      #try_files $cache_enabler_uri $uri $uri/ $custom_subdir/index.php?$args;
    
      # Wordpress Permalinks
      try_files $uri $uri/ /index.php?q=$uri&$args;
    


    3. In your nginx domain.com.conf and/or domain.com.ssl.conf vhost config file, comment out (add hash # in front) the include file for Cache Enabler where yourdomain.com is your domain name
    Code (Text):
      #include /usr/local/nginx/conf/wpincludes/yourdomain.com/wpcacheenabler_yourdomain.com.conf;
    


    4. Then restart nginx + php-fpm services and run nginx config check to see if the nginx configuration is working
    Code (Text):
    nginx -t
    nprestart
    
     
..