Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB CPU Intensive with XenForo

Discussion in 'Install & Upgrades or Pre-Install Questions' started by Oxide, Jun 16, 2015.

  1. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    I think it's MariaDB.

    I have 4 cores * Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz

    Each time i submit a request from check-host.com it sends around 15 requests at once, the cpu's goes straight to 100% each one of them.

    I have no idea why? I'm using XenForo.
     
  2. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    7:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    check-host.com directs to google for me ?? what are you testing ? url wise ?
     
  3. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    It's not Mariadb, its PHP-FPM that is CPU hog.
     
  4. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    7:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    yeah probably..
     
  5. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    Maraidb is not really CPU intensive, just memory intensive if you have lots of data/database.
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    Hope to see OLS on centminmod, so that we can compare PHP-FPM and LSAPI performance :).
     
    • Like Like x 1
  7. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    7:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    @Oxide throwing the relevant info out there for you to read

    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 ? .07 stable or .08 beta ? If .08 beta when was it installed and when was last time you updated the .08 beta code (there's constant updates to the code).
    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.
    Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.
    Notes:

    Nginx 502 Bad Gateway Errors



    Bad gateway 502 is usually related to Nginx timing out waiting on PHP-FPM to respond as PHP-FPM is overloaded or overwhelmed with requests, so may need to tune PHP-FPM values. It also maybe due to PHP-FPM in turn being queued and backed up waiting on MariaDB MySQL server to respond - so also need to look at MySQL.

    You'll need to tune your PHP-FPM settings and this is left up to end user to do but here's a thread for starters to enable php status page output outlined at PHP PHP-FPM - Centmin Mod - Menu based Nginx installer for CentOS servers and PHP-FPM - pm.max_children | Centmin Mod Community which outlines the official PHP-FPM config documentation as well.

    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.

    Some of Centmin Mod's installed software will have their own access and error logs which maybe useful for diagnosing errors or give info, notes, or warning notices.

    Note: There's no support provided by me for diagnosing such errors which may occur for various reasons including misconfiguration of installed php/mysql scripts or applications.

    In SSH2 telnet you can use tail command to view the last X number of lines in the file.

    For example for viewing last 10 lines in the file for:

    For Nginx access and error logs:
    Code:
      tail -10 /usr/local/nginx/logs/access.log
      tail -10 /usr/local/nginx/logs/error.log
    
    For specific domainname.com access and error log:
    Code:
      tail -10 /home/nginx/domains/domainname.com/log/access.log
      tail -10 /home/nginx/domains/domainname.com/log/error.log
    
    For other system error logs located at /var/log:

    list /var/log files in ascending time order so the most recently modified files are at the bottom
    Code:
      ls -lhrt /var/log
    
    Code:
    total 2.7M
    -rw------- 1 root  root    0 Aug 29 15:33 tallylog
    -rw------- 1 root  root    0 Aug 29 15:33 spooler
    drwx------ 3 root  root 4.0K Aug 29 15:35 samba
    drwxr-xr-x 2 root  root 4.0K Aug 29 15:35 mail
    -rw-r--r-- 1 root  500     0 Oct  8 18:13 dmesg.old
    -rw------- 1 root  500     0 Oct  8 18:13 boot.log
    -rw-r--r-- 1 root  500     0 Oct  8 18:14 dmesg
    drwx------ 2 root  root 4.0K Oct  8 18:14 httpd
    drwxr-xr-x 2 root  root 4.0K Oct  8 19:08 php-fpm
    -rw-rw---- 1 mysql root 2.3K Oct  9 12:38 mysqld.log
    -rw------- 1 root  root 9.2K Oct 26 10:48 yum.log
    -rw------- 1 root  utmp  94K Nov  7 22:59 btmp
    drwxr-xr-x 2 root  root 4.0K Nov  8 00:00 sa
    -rw------- 1 root  root 269K Nov  8 21:39 messages
    -rw------- 1 root  root 110K Nov  8 23:08 secure
    -rw-rw-r-- 1 root  utmp  43K Nov  8 23:08 wtmp
    -rw-r--r-- 1 root  root 144K Nov  8 23:08 lastlog
    -rw------- 1 root  root  69K Nov  8 23:08 lfd.log
    -rw------- 1 root  root 332K Nov  8 23:08 maillog
    -rw------- 1 root  500  1.6M Nov  8 23:10 cron
    
    For PHP-FPM error log:
    Code:
      tail -10 /var/log/php-fpm/www-error.log
    
    and/or
    Code:
      /var/log/php-fpm/www-php.error.log
    
    For MySQL / MariaDB error log:
    Code:
      tail -10 /var/log/mysqld.log
    
    For CSF firewall LFD log:
    Code:
      tail -10 /var/log/lfd.log
    
    For Mail log:
    Code:
      tail -10 /var/log/maillog
    
    For Cron job logs:
    Code:
      tail -10 /var/log/cron
    

    How to edit php.ini and php-fpm configuration files ?



    Centmin Mod install created command short cuts outlined here to allow you to quickly edit your /usr/local/lib/php.ini file and your /usr/local/etc/php-fpm.conf file. Full list of command shortcuts below:
    • Edit php.ini = phpedit ( /usr/local/lib/php.ini )
    • Edit my.cnf = mycnf ( /etc/my.cnf )
    • Edit php-fpm.conf = fpmconf ( /usr/local/etc/php-fpm.conf )
    • Edit nginx.conf = nginxconf ( /usr/local/nginx/conf/nginx.conf )
    • Edit (nginx) virtual.conf = vhostconf - only edits /usr/local/nginx/conf/conf.d/virtual.conf not the additional vhost domain.com.conf files added later
    • Edit (nginx) php.conf = phpinc ( /usr/local/nginx/conf/php.conf )
    • Edit (nginx) drop.conf = dropinc ( /usr/local/nginx/conf/drop.conf )
    • Edit (nginx) staticfiles.conf = statfilesinc ( /usr/local/nginx/conf/staticfiles.conf )
    • nginx stop/start/restart = ngxstop/ngxstart/ngxrestart
    • php-fpm stop/start/restart = fpmstop/fpmstart/fpmrestart
    • mysql stop/start/restart = mysqlstop/mysqlstart/mysqlrestart
    • nginx + php-fpm stop/start/restart = npstop/npstart/nprestart
    • memcached stop/start/restart =memcachedstop/memcachedstart/memcachedrestart
    • csf stop/start/restart = csfstop/csfstart/csfrestart
    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 :)
     
    • Like Like x 2
  8. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    I am using: Check server: Check host - online website monitoring, Check IP

    Using version, default installation no changes: centmin-v1.2.3mod

    Issue happens when I send check-host.net (http check) to my site, it basically makes around 15 requests at once. All CPU goes 100%.

    I am using BuyVM, i only notice cpu issue when doing it. Ram and everything else is okay, it also disappears instantly..


    See pic (remove it once you saw it http://i.imgur.com/hN84oGp.gif )
     
  9. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    That's very small request :/
    I have a 4GB Linode VPS only, that can handle 5000+ visitors simultaneous without any problem.
     
  10. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    Yes, i don't know if this can be XenForo issue or Centmin.
     
  11. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    My 4GB Linode is hosting 2 XenForo forum and 2 WP sites also.
     
  12. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    How much RAM do you have? SSD ?
     
  13. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    1GB Ram
    SSD
    4 Cores * Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz

    Ram has lots of free space left. I am also using APC.
     
  14. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    So this is a VPS not dedicated server?
     
  15. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    Yes, VPS from BuyVM.

    I've had not a similar issue with any other forum softwares, on other hosts. I don't know if this case is this host, this CPU.. or XenForo.

    Mostly XenForo, nginx is pretty light weight..
     
  16. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
    KVM or OpenVZ?
     
  17. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    OpenVZ.
     
  18. RoldanLT

    RoldanLT Well-Known Member

    3,830
    929
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,258
    Local Time:
    5:18 PM
    1.11
    10.2
  19. Oxide

    Oxide Active Member

    502
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    7:18 PM
    Both of them have lack of DDoS Protection. RamNode is also extremely slow in Seattle, which is the location that has decent protection.
     
  20. eva2000

    eva2000 Administrator Staff Member

    29,033
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,782
    Local Time:
    7:18 PM
    Nginx 1.13.x
    MariaDB 5.5
    if you have 4 cpu cores and 1 hits 100% temporarily = 1/4 = 0.25 cpu load so nothing to worry about