Want more timely Centmin Mod News Updates?
Become a Member

MariaDB Site keeps going down and Mysql goes to high CPU usage

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Naytrix, Jan 29, 2022.

  1. Naytrix

    Naytrix New Member

    14
    0
    1
    Apr 1, 2021
    Ratings:
    +3
    Local Time:
    4:31 PM
    So my website keeps going down and I look in "top" and mysqld is at 300% CPU usage and php-fpm is using so many processes at around 50-70% cpu usage each, load average is at 50+

    How can I fix this? whats caused this?


    Please see screenshot: https://prnt.sc/26l4r2e
     
  2. cloud9

    cloud9 Premium Member Premium Member

    431
    117
    43
    Oct 6, 2015
    England
    Ratings:
    +217
    Local Time:
    4:31 PM
    1.25.3
    10.6.x
    You could have a problem with an mysql database and you need to track it down

    See this link
     
  3. eva2000

    eva2000 Administrator Staff Member

    54,582
    12,224
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,793
    Local Time:
    2:31 AM
    Nginx 1.27.x
    MariaDB 10.x/11.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.

    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 some commands which are also available automatically when you run cminfo top command
    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"
    


    Then in Centmin Mod 123.09beta01, you have access to cminfo sar commands for summary stats like
    Code (Text):
    cminfo top
    cminfo sar-cpu
    cminfo sar-mem
    cminfo phpstats
    


    For posting code you might want to use CODE tags for code How to use forum BBCODE code tags :)
     
  4. Naytrix

    Naytrix New Member

    14
    0
    1
    Apr 1, 2021
    Ratings:
    +3
    Local Time:
    4:31 PM


    Hi, thank you very much for this

    cminfo sar-cpu: ------------------------------------------------------------------ CPU Utili - Pastebin.com

    cminfo sar-mem: ------------------------------------------------------------------ Memory Us - Pastebin.com

    cminfo phpstats: ------------------------------------------------------------------Total PHP- - Pastebin.com

    Hope this helps,

    Regards
     
    Last edited by a moderator: Jan 30, 2022
  5. eva2000

    eva2000 Administrator Staff Member

    54,582
    12,224
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,793
    Local Time:
    2:31 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    removed your cminfo top link stats as it contains sensitive server info/hostname/mysql database names but from I can tell CPU utilisation for 95-99% percentile is at 95-97% idle CPU so most of the time the server isn't using much CPU. But sar CPU stats in cminfo top stats show between 2:30 am to 3:30am and 4:40 am to 6:30 am CPU load average hits up to 52 which on your 32 CPU thread server is actually only 52/32 = 1.625 cpu load average or 162.5% cpu load. Still high but not as high as cpu load 52 is without a context as to how many cpu threads you have.

    cminfo top will also report your sar cpu load average date/timestamps for when you go over the max 32 cpu thread x 1.0 = 32 cpu load averages filtered at 1min and 5min intervals. Notice the pattern of times? Maybe you're running some MySQL intensive processes during that time? If you run cminfo top command during high cpu load times, you would be able to capture some of those processes too.

    1min
    Code (Text):
    ------------------------------------------------------------------
    Filter sar -q for times cpu load avg (1min) hit/exceeded cpu threads max
    
    03:00:01 AM        54       668     42.72     34.08     29.16         0
    03:05:01 AM        50       664     51.10     44.95     35.27         0
    03:15:01 AM        53       607     50.72     36.87     17.29         0
    03:20:01 AM        52       609     51.15     46.04     26.70         0
    04:40:01 AM        52       656     38.77     13.20      5.55         0
    04:45:02 AM        49       653     51.72     37.60     18.29         0
    04:50:01 AM        51       653     51.62     46.39     27.37         0
    04:55:01 AM        51       654     51.30     49.60     34.04         0
    05:00:01 AM        52       664     50.93     50.52     38.76         0
    05:05:02 AM        52       655     52.47     51.70     42.53         0
    05:10:01 AM        52       654     50.01     51.50     45.04         0
    05:15:01 AM        51       653     50.68     51.52     46.87         0
    05:20:01 AM        52       655     48.44     50.30     47.69         0
    05:25:01 AM        53       652     50.54     50.45     48.50         0
    05:35:01 AM        51       653     51.86     48.79     48.07         0
    05:40:02 AM        53       652     51.98     50.67     49.08         0
    05:45:01 AM        52       652     50.33     50.52     49.46         0
    05:50:01 AM        51       652     51.79     51.11     50.00         0
    05:55:01 AM        52       652     51.93     51.54     50.50         0
    06:00:01 AM        53       656     52.77     52.20     51.07         0
    06:05:01 AM        52       654     51.10     51.86     51.26         0
    06:10:01 AM        50       653     51.20     51.46     51.26         0
    06:15:01 AM        51       652     51.43     51.30     51.24         0
    06:20:01 AM        51       652     50.50     51.01     51.14         0
    

    5min
    Code (Text):
    Filter sar -q for times cpu load avg (5min) hit/exceeded cpu threads max
    
    03:00:01 AM        54       668     42.72     34.08     29.16         0
    03:05:01 AM        50       664     51.10     44.95     35.27         0
    03:15:01 AM        53       607     50.72     36.87     17.29         0
    03:20:01 AM        52       609     51.15     46.04     26.70         0
    04:45:02 AM        49       653     51.72     37.60     18.29         0
    04:50:01 AM        51       653     51.62     46.39     27.37         0
    04:55:01 AM        51       654     51.30     49.60     34.04         0
    05:00:01 AM        52       664     50.93     50.52     38.76         0
    05:05:02 AM        52       655     52.47     51.70     42.53         0
    05:10:01 AM        52       654     50.01     51.50     45.04         0
    05:15:01 AM        51       653     50.68     51.52     46.87         0
    05:20:01 AM        52       655     48.44     50.30     47.69         0
    05:25:01 AM        53       652     50.54     50.45     48.50         0
    05:30:01 AM        14       653     29.70     44.13     46.87         0
    05:35:01 AM        51       653     51.86     48.79     48.07         0
    05:40:02 AM        53       652     51.98     50.67     49.08         0
    05:45:01 AM        52       652     50.33     50.52     49.46         0
    05:50:01 AM        51       652     51.79     51.11     50.00         0
    05:55:01 AM        52       652     51.93     51.54     50.50         0
    06:00:01 AM        53       656     52.77     52.20     51.07         0
    06:05:01 AM        52       654     51.10     51.86     51.26         0
    06:10:01 AM        50       653     51.20     51.46     51.26         0
    06:15:01 AM        51       652     51.43     51.30     51.24         0
    06:20:01 AM        51       652     50.50     51.01     51.14         0
    



    Your cminfo top output sar cpu stats
    Code (Text):
    ------------------------------------------------------------------
     CPU Utilisation % Last 7 days (32 CPU Threads):
    ------------------------------------------------------------------
    %CPU  min:  %user:  1.72   %nice:  0.00  %system:  0.15   %iowait:  0.00  %steal:  0.00  %idle:  1.39
    %CPU  avg:  %user:  8.83   %nice:  0.00  %system:  0.57   %iowait:  0.01  %steal:  0.00  %idle:  90.59
    %CPU  max:  %user:  93.25  %nice:  0.04  %system:  16.83  %iowait:  0.18  %steal:  0.00  %idle:  98.09
    %CPU  50%:  %user:  5.50   %nice:  0.00  %system:  0.36   %iowait:  0.01  %steal:  0.00  %idle:  94.11
    %CPU  75%:  %user:  7.41   %nice:  0.00  %system:  0.48   %iowait:  0.01  %steal:  0.00  %idle:  95.65
    %CPU  90%:  %user:  9.31   %nice:  0.00  %system:  0.60   %iowait:  0.01  %steal:  0.00  %idle:  96.59
    %CPU  95%:  %user:  11.56  %nice:  0.00  %system:  0.76   %iowait:  0.01  %steal:  0.00  %idle:  97.01
    %CPU  99%:  %user:  87.70  %nice:  0.00  %system:  5.15   %iowait:  0.02  %steal:  0.00  %idle:  97.51
    
    ------------------------------------------------------------------
     CPU Utilisation % Daily Last 7 days (32 CPU Threads):
    ------------------------------------------------------------------
    Jan 29 2022 %CPU
    %CPU  min:  %user:  2.59   %nice:  0.00  %system:  0.17  %iowait:  0.00  %steal:  0.00  %idle:  1.39
    %CPU  avg:  %user:  24.29  %nice:  0.00  %system:  1.43  %iowait:  0.01  %steal:  0.00  %idle:  74.27
    %CPU  max:  %user:  93.25  %nice:  0.03  %system:  6.60  %iowait:  0.15  %steal:  0.00  %idle:  97.20
    %CPU  95%:  %user:  92.90  %nice:  0.00  %system:  5.43  %iowait:  0.01  %steal:  0.00  %idle:  96.75
    Jan 28 2022 %CPU
    %CPU  min:  %user:  2.06   %nice:  0.00  %system:  0.16  %iowait:  0.01  %steal:  0.00  %idle:  11.36
    %CPU  avg:  %user:  19.41  %nice:  0.00  %system:  1.09  %iowait:  0.01  %steal:  0.00  %idle:  79.49
    %CPU  max:  %user:  83.93  %nice:  0.02  %system:  5.39  %iowait:  0.16  %steal:  0.00  %idle:  97.78
    %CPU  95%:  %user:  83.35  %nice:  0.00  %system:  4.49  %iowait:  0.01  %steal:  0.00  %idle:  97.25
    Jan 27 2022 %CPU
    %CPU  min:  %user:  1.93   %nice:  0.00  %system:  0.15  %iowait:  0.01  %steal:  0.00  %idle:  88.23
    %CPU  avg:  %user:  5.16   %nice:  0.00  %system:  0.33  %iowait:  0.01  %steal:  0.00  %idle:  94.51
    %CPU  max:  %user:  11.40  %nice:  0.02  %system:  0.60  %iowait:  0.02  %steal:  0.00  %idle:  97.90
    %CPU  95%:  %user:  7.98   %nice:  0.00  %system:  0.49  %iowait:  0.01  %steal:  0.00  %idle:  97.02
    Jan 26 2022 %CPU
    %CPU  min:  %user:  1.82   %nice:  0.00  %system:  0.15  %iowait:  0.01  %steal:  0.00  %idle:  88.56
    %CPU  avg:  %user:  5.28   %nice:  0.00  %system:  0.36  %iowait:  0.01  %steal:  0.00  %idle:  94.35
    %CPU  max:  %user:  10.97  %nice:  0.04  %system:  0.69  %iowait:  0.02  %steal:  0.00  %idle:  98.02
    %CPU  95%:  %user:  8.28   %nice:  0.00  %system:  0.55  %iowait:  0.01  %steal:  0.00  %idle:  97.14
    Jan 25 2022 %CPU
    %CPU  min:  %user:  1.74   %nice:  0.00  %system:  0.20   %iowait:  0.01  %steal:  0.00  %idle:  9.08
    %CPU  avg:  %user:  5.59   %nice:  0.00  %system:  0.53   %iowait:  0.01  %steal:  0.00  %idle:  93.87
    %CPU  max:  %user:  75.33  %nice:  0.04  %system:  16.83  %iowait:  0.18  %steal:  0.00  %idle:  98.05
    %CPU  95%:  %user:  8.67   %nice:  0.00  %system:  0.60   %iowait:  0.01  %steal:  0.00  %idle:  97.18
    Jan 24 2022 %CPU
    %CPU  min:  %user:  1.72   %nice:  0.00  %system:  0.15  %iowait:  0.01  %steal:  0.00  %idle:  86.77
    %CPU  avg:  %user:  5.39   %nice:  0.00  %system:  0.35  %iowait:  0.01  %steal:  0.00  %idle:  94.24
    %CPU  max:  %user:  12.51  %nice:  0.02  %system:  0.78  %iowait:  0.02  %steal:  0.00  %idle:  98.09
    %CPU  95%:  %user:  8.63   %nice:  0.00  %system:  0.54  %iowait:  0.01  %steal:  0.00  %idle:  97.05
    Jan 23 2022 %CPU
    %CPU  min:  %user:  2.33   %nice:  0.00  %system:  0.20  %iowait:  0.01  %steal:  0.00  %idle:  84.65
    %CPU  avg:  %user:  6.26   %nice:  0.00  %system:  0.45  %iowait:  0.01  %steal:  0.00  %idle:  93.28
    %CPU  max:  %user:  14.63  %nice:  0.02  %system:  1.70  %iowait:  0.04  %steal:  0.00  %idle:  97.46
    %CPU  95%:  %user:  9.86   %nice:  0.00  %system:  0.70  %iowait:  0.01  %steal:  0.00  %idle:  96.47
    Jan 22 2022 %CPU
    %CPU  min:  %user:  2.48   %nice:  0.00  %system:  0.18  %iowait:  0.01  %steal:  0.00  %idle:  86.53
    %CPU  avg:  %user:  6.85   %nice:  0.00  %system:  0.45  %iowait:  0.01  %steal:  0.00  %idle:  92.68
    %CPU  max:  %user:  12.72  %nice:  0.02  %system:  0.76  %iowait:  0.02  %steal:  0.00  %idle:  97.33
    %CPU  95%:  %user:  10.82  %nice:  0.00  %system:  0.68  %iowait:  0.02  %steal:  0.00  %idle:  96.37
    


    You have some MySQL table locking in Wordpress database from cminfo top output for queries like, so that's probably contributing
    Code (Text):
    Creating sort index
    SELECT   wpu5_posts.ID FROM wpu5_posts  WHERE 1=1  AND (((wpu5_posts.post_title LIKE '%need%') OR (wpu5_posts.post_excerpt LIKE '%need%') OR (wpu5_posts.post_content LIKE '%need%')))  AND (wpu5_posts.post_password = '')  AND wpu5_posts.post_type = 'post' AND ((wpu5_posts.post_status = 'publish'))  ORDER BY (CASE WHEN wpu5_posts.post_title LIKE '%need for s%' THEN 1 WHEN wpu5_posts.post_title LIKE '%need%' THEN 2 WHEN wpu5_posts.post_excerpt LIKE '%need for s%' THEN 4 WHEN wpu5_posts.post_content LIKE '%need for s%' THEN 5 ELSE 6 END), wpu5_posts.post_date DESC LIMIT 0, 4
    

    I'd look at your Wordpress setup/plugins as any MySQL table locks will queue up and drive up MySQL cpu loads as well as queue up PHP-FPM processes/requests. MySQL load is quite considerable at 3,800+ queries/second but not that high for your server specs with AMD EPYC 7351P 16C/32T cpu server.

    Did you create Wordpress using centmin.sh menu option 22 Wordpress auto installer ? Which full page HTML cache setup did you choose ? Cache Enabler, Redis Nginx level cache or Super Cache?

    cminfo top reports you're using PHP 7.4.16, that's pretty old with 7.4.27 being latest in 7.4 branch.

    If you haven't already done so, you can install Redis server https://community.centminmod.com/th...er-on-centmin-mod-lemp-stack.4546/#post-91249 and then install Wordpress Redis Object cache plugin it caches and offloads MySQL queries to reduce load to MySQL somewhat - see https://community.centminmod.com/th...ss-redis-object-cache-plugin-installer.20418/. While that preview script isn't publicly available, you can manually install and configure it as per Redis Object Cache after you install Redis server.

    As already stated, Centmin Mod is provided as is, so troubleshooting load issues is left to end user to do. But hope the above tips help. Other forum users are free to chime in too.
     
  6. Naytrix

    Naytrix New Member

    14
    0
    1
    Apr 1, 2021
    Ratings:
    +3
    Local Time:
    4:31 PM
    Yes I did and I believe super cache? Ill run the cminfo once the server is overloaded.
     
  7. Naytrix

    Naytrix New Member

    14
    0
    1
    Apr 1, 2021
    Ratings:
    +3
    Local Time:
    4:31 PM
    I have the cminfo top link from when the sites down, wheres the best place to send it?
     
  8. eva2000

    eva2000 Administrator Staff Member

    54,582
    12,224
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,793
    Local Time:
    2:31 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    As I stated previous, Centmin Mod is provided as is, so you can take that cminfo top output to analyze for yourself to give you hints as to the issues. Or hire someone to troubleshoot the issues.