Discover Centmin Mod today
Register Now

MariaDB High CPU usage on page load - htop shows mysqld

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rxdc, Jun 25, 2017.

  1. rxdc

    rxdc New Member

    4
    2
    3
    Jun 25, 2017
    Ratings:
    +2
    Local Time:
    3:16 AM
    • CentOS Version: CentOS 7 64bit ?
    • Centmin Mod Version Installed: 123.09beta01
    • Nginx Version Installed: 1.13.1
    • PHP Version Installed: 5.6.30
    • MariaDB MySQL Version Installed: 10.1.24-MariaDB
    Hello,

    I am currently managing a wordpress site which is kinda loaded with plugins and since I am not the owner - just managing the server - I can't disable plugins as I want to, most of them are needed anyways, but the admin will hunt some plugins which aren't needed, so maybe I can increase the speed a bit with some config tweaking.

    My current nginx.conf looks like this:

    Code:
    user              nginx nginx;
    worker_processes 2;
    #worker_priority -10;
    
    worker_rlimit_nofile 260000;
    timer_resolution 100ms;
    
    pcre_jit on;
    include /usr/local/nginx/conf/dynamic-modules.conf;
    
    
    pid         logs/nginx.pid;
    
    events {
        #orig: worker_connections  50000;
        worker_connections 8096;
        accept_mutex off;
        accept_mutex_delay 200ms;
        use epoll;
        multi_accept on;
    }
    
    http {
     map_hash_bucket_size 128;
     map_hash_max_size 4096;
     server_names_hash_bucket_size 128;
     server_names_hash_max_size 2048;
     variables_hash_max_size 2048;
    
    limit_req_zone $binary_remote_addr zone=xwplogin:16m rate=40r/m;
    #limit_conn_zone $binary_remote_addr zone=xwpconlimit:16m;
    
    more_set_headers "Server: nginx centminmod";
    more_set_headers "X-Powered-By: centminmod";
    
    # uncomment cloudflare.conf include if using cloudflare for
    # server and/or vhost site + setup cron job for command
    # /usr/local/src/centminmod/tools/csfcf.sh auto
    # run the auto command once to popular cloudflare ips
    #include /usr/local/nginx/conf/cloudflare.conf;
    # uncomment incapsula.conf include if using incapsula for
    # server and/or vhost site + setup cron job for command
    # /usr/local/src/centminmod/tools/csfincapsula.sh auto
    # run the auto command once to popular incapsula ips
    #include /usr/local/nginx/conf/incapsula.conf;
    include /usr/local/nginx/conf/maintenance.conf;
    #include /usr/local/nginx/conf/vts_http.conf;
    include /usr/local/nginx/conf/geoip.conf;
    include /usr/local/nginx/conf/webp.conf;
    include /usr/local/nginx/conf/botlimit.conf;
    #include /usr/local/nginx/conf/pagespeedadmin.conf;
    include /usr/local/nginx/conf/fastcgi_param_https_map.conf;
    
    log_format  main  '$remote_addr - $remote_user [$time_local] $request '
                    '"$status" $body_bytes_sent "$http_referer" '
                    '"$http_user_agent" "$http_x_forwarded_for" "$gzip_ratio"'
                    ' "$connection" "$connection_requests" "$request_time"';
    
    log_format  ddos-proxy '$remote_addr for $http_x_real_ip - $remote_user [$time_local] $request '
                    '"$status" $body_bytes_sent "$http_referer" '
                    '"$http_user_agent" "$http_x_forwarded_for" "$gzip_ratio"'
                    ' "$connection" "$connection_requests" "$request_time"';
    
    log_format  main_ext '$remote_addr - $remote_user [$time_local] "$request" '
                             '$status $body_bytes_sent "$http_referer" '
                             '"$http_user_agent" "$http_x_forwarded_for" '
                             'rt=$request_time ua="$upstream_addr" '
                             'us="$upstream_status" ut="$upstream_response_time" '
                             'ul="$upstream_response_length" '
                             'cs=$upstream_cache_status' ;
    
    access_log  logs/access.log combined buffer=128k flush=5m;
    error_log   logs/error.log warn;
    
        index  index.php index.html index.htm;
        include       mime.types;
        default_type  application/octet-stream;
        charset utf-8;
    
            sendfile on;
            sendfile_max_chunk 512k;
            tcp_nopush  on;
            tcp_nodelay on;
            server_tokens off;
            server_name_in_redirect off;
           
            keepalive_timeout  30;
            keepalive_requests 500;
            lingering_time 20s;
            lingering_timeout 5s;
            keepalive_disable msie6;
    
        gzip on;
        gzip_vary   on;
        gzip_disable msie6;
        #gzip_disable "MSIE [1-6]\.";
            gzip_static on;
        gzip_min_length    10240;
            #gzip_min_length   1400;
            gzip_buffers      32 8k;
            gzip_http_version 1.0;
            gzip_comp_level 5;
            gzip_proxied    any;
            #gzip_types text/plain text/css text/xml application/javascript application/x-javascript application/xml application/xml+rss application/ecmascript application/json image/svg+xml;
        gzip_types text/plain text/css application/x-javascript text/javascript application/javascript;
    
     client_body_buffer_size 256k;
     client_body_in_file_only off;
     client_body_timeout 10s;
     client_header_buffer_size 64k;
    ## how long a connection has to complete sending 
    ## it's headers for request to be processed
     client_header_timeout  5s;
     client_max_body_size 50m; 
     connection_pool_size  512;
     directio  4m;
     ignore_invalid_headers on;      
     large_client_header_buffers 8 64k;
     output_buffers   8 256k;
     postpone_output  1460;
     proxy_temp_path  /tmp/nginx_proxy/;
     request_pool_size  32k;
     reset_timedout_connection on;
     send_timeout     10s;
     types_hash_max_size 2048;
    
    # for nginx proxy backends to prevent redirects to backend port 
    # port_in_redirect off;
    
    
    #ORIG
    open_file_cache max=50000 inactive=60s;
    open_file_cache_valid 120s;
    open_file_cache_min_uses 2;
    open_file_cache_errors off;
    open_log_file_cache max=10000 inactive=30s min_uses=2;
    
    #open_file_cache max=200000 inactive=20s;
    #open_file_cache_valid 30s;
    #open_file_cache_min_uses 2;
    #open_file_cache_errors on;
    
    ## limit number of concurrency connections per ip to 16
    ## add to your server {} section the next line
    ## limit_conn limit_per_ip 16;
    ## uncomment below line allows 500K sessions
    # limit_conn_log_level error;
    #######################################
    # use limit_zone for Nginx <v1.1.7 and lower
    # limit_zone $binary_remote_addr zone=limit_per_ip:16m;
    #######################################
    # use limit_conn_zone for Nginx >v1.1.8 and higher
    # limit_conn_zone $binary_remote_addr zone=limit_per_ip:16m;
    #######################################
    
     include /usr/local/nginx/conf/conf.d/*.conf;
    }
    
    vhost conf:

    Code:
    # Centmin Mod Getting Started Guide
    # must read http://centminmod.com/getstarted.html
    # For SPDY SSL Setup
    # read http://centminmod.com/nginx_configure_https_ssl_spdy.html
    
    # redirect from www to non-www  forced SSL
    # uncomment, save file and restart Nginx to enable
    # if unsure use return 302 before using return 301
     server {
           listen   80;
           server_name mydomain.com www.mydomain.com;
           return 302 https://$server_name$request_uri;
     }
    
    server {
      listen 443 ssl http2;
      server_name mydomain.com www.mydomain.com;
    
      include /usr/local/nginx/conf/ssl/mydomain.com/mydomain.com.crt.key.conf;
      include /usr/local/nginx/conf/ssl_include.conf;
    
      http2_max_field_size 16k;
      http2_max_header_size 32k;
      # mozilla recommended
      ssl_ciphers EECDH+CHACHA20:EECDH+CHACHA20-draft:EECDH+ECDSA+AESGCM:EECDH+aRSA+AESGCM:EECDH+ECDSA+SHA256:EECDH+ECDSA+SHA384:EECDH+aRSA+SHA256:EECDH+aRSA+SHA384:EECDH+AES128:!aNULL:!eNULL:!LOW:!3DES:!MD5:!EXP:!PSK:!SRP:!DSS:!RC4:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA:!CAMELLIA;
      ssl_prefer_server_ciphers   on;
      #add_header Alternate-Protocol  443:npn-spdy/3;
    
      # before enabling HSTS line below read centminmod.com/nginx_domain_dns_setup.html#hsts
      #add_header Strict-Transport-Security "max-age=31536000; includeSubdomains;";
      #add_header X-Frame-Options SAMEORIGIN;
      #add_header X-Xss-Protection "1; mode=block" always;
      #add_header X-Content-Type-Options "nosniff" always;
      #spdy_headers_comp 5;
      ssl_buffer_size 1369;
      ssl_session_tickets on;
    
      # enable ocsp stapling
      resolver 8.8.8.8 8.8.4.4 valid=10m;
      resolver_timeout 10s;
      ssl_stapling on;
      ssl_stapling_verify on;
    
    
    # ngx_pagespeed & ngx_pagespeed handler
    #include /usr/local/nginx/conf/pagespeed.conf;
    #include /usr/local/nginx/conf/pagespeedhandler.conf;
    #include /usr/local/nginx/conf/pagespeedstatslog.conf;
    
      # limit_conn limit_per_ip 16;
      # ssi  on;
    
      access_log /home/nginx/domains/mydomain.com/log/access.log main_ext buffer=256k flush=60m;
      error_log /home/nginx/domains/mydomain.com/log/error.log;
    
      include /usr/local/nginx/conf/autoprotect/mydomain.com/autoprotect-mydomain.com.conf;
      root /home/nginx/domains/mydomain.com/public;
      # uncomment cloudflare.conf include if using cloudflare for
      # server and/or vhost site
      #include /usr/local/nginx/conf/cloudflare.conf;
      include /usr/local/nginx/conf/503include-main.conf;
    
      # prevent access to ./directories and files
      #location ~ (?:^|/)\. {
      # deny all;
      #}
    
    include /usr/local/nginx/conf/blockbots.conf;
    
      location / {
      include /usr/local/nginx/conf/503include-only.conf;
    try_files $uri $uri/ /index.php?$uri&$args;
    
         location ~* \.(jpg|jpeg|gif|png|css|js|ico|xml)$ {
             access_log        off;
             log_not_found     off;
             expires           30d;
         }
    
     location /xmlrpc.php {
          deny all;
        }
    
    # block common exploits, sql injections etc
    #include /usr/local/nginx/conf/block.conf;
    
      # Enables directory listings when index file not found
      #autoindex  on;
    
      # Shows file listing times as local time
      #autoindex_localtime on;
    
      # Enable for vBulletin usage WITHOUT vbSEO installed
      # More example Nginx vhost configurations at
      # http://centminmod.com/nginx_configure.html
      #try_files    $uri $uri/ /index.php;
    
        #try_files $uri $uri/ /wp/index.php?q=$request_uri;
    
      }
    
      include /usr/local/nginx/conf/staticfiles.conf;
      include /usr/local/nginx/conf/php.conf;
      include /usr/local/nginx/conf/drop.conf;
      #include /usr/local/nginx/conf/errorpage.conf;
      include /usr/local/nginx/conf/vts_server.conf;
    #include /usr/local/nginx/conf/phpwpcache.conf;
    }
    
    my.cnf:
    Code:
    [client]
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    max_allowed_packet = 64M
    
    [mysqld]
    ignore_db_dirs=cmsetiofiotest
    local-infile=0
    ignore_db_dirs=lost+found
    character-set-server=utf8
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    #bind-address=127.0.0.1
    # optimized my.cnf for MariaDB 5.5.x
    # by eva2000
    # vbtechsupport.com
    
    tmpdir=/home/mysqltmp
    
    innodb=ON
    #skip-federated
    ##skip-pbxt
    ##skip-pbxt_statistics
    #skip-archive
    #skip-name-resolve
    #old_passwords
    back_log = 512
    max_connections = 500
    key_buffer_size = 256M
    myisam_sort_buffer_size = 256M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    table_definition_cache = 8192
    table_open_cache = 4096
    thread_cache_size = 256
    wait_timeout = 1800
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 256M
    max_allowed_packet = 64M
    #max_seeks_for_key = 4294967295
    group_concat_max_len = 1024
    max_length_for_sort_data = 1024
    net_buffer_length = 16384
    max_connect_errors = 100000
    concurrent_insert = 2
    read_rnd_buffer_size = 512K
    bulk_insert_buffer_size = 8M
    # query_cache boost for MariaDB >10.1.2+
    # https://community.centminmod.com/posts/30811/
    #query_cache_limit = 1024K
    query_cache_limit = 256K
    query_cache_size = 80M
    query_cache_type = 1
    query_cache_min_res_unit = 2K
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = InnoDB
    
    log_warnings=1
    slow_query_log=0
    long_query_time=1
    slow_query_log_file=/var/lib/mysql/slowq.log
    #log-error=/var/log/mysqld.log
    
    # innodb settings
    innodb_large_prefix=1
    innodb_purge_threads = 1
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    innodb_open_files = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 512M
    
    ## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
    innodb_buffer_pool_instances=1
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    #innodb_thread_concurrency = 8
    innodb_lock_wait_timeout=50
    innodb_flush_method = O_DIRECT
    innodb_support_xa=1
    
    # 200 * # DISKS
    innodb_io_capacity = 400
    innodb_io_capacity_max = 800
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    innodb_flush_neighbors = 0
    
    # mariadb settings
    [mariadb]
    #thread-handling = pool-of-threads
    #thread-pool-size= 20
    #mysql --port=3307 --protocol=tcp
    #extra-port=3307
    #extra-max-connections=1
    
    userstat = 0
    key_cache_segments = 1
    aria_group_commit = none
    aria_group_commit_interval = 0
    aria_log_file_size = 768M
    aria_log_purge_type = immediate 
    aria_pagecache_buffer_size = 768M
    aria_sort_buffer_size = 192M
    
    [mariadb-5.5]
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    
    #ignore_db_dirs=
    query_cache_strip_comments=0
    
    innodb_read_ahead = linear
    innodb_adaptive_flushing_method = estimate
    innodb_flush_neighbor_pages = 1
    innodb_stats_update_need_lock = 0
    innodb_log_block_size = 512
    
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mysqld_safe] 
    socket=/var/lib/mysql/mysql.sock
    #log-error=/var/log/mysqld.log
    #nice = -5
    open-files-limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 64M
    
    [myisamchk] 
    tmpdir=/home/mysqltmp
    key_buffer = 256M 
    sort_buffer = 32M
    read_buffer = 32M
    write_buffer = 32M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mariadb-10.0]
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    
    # 2 variables needed to switch from XtraDB to InnoDB plugins
    #plugin-load=ha_innodb
    #ignore_builtin_innodb
    
    ## MariaDB 10 only save and restore buffer pool pages
    ## warm up InnoDB buffer pool on server restarts
    #innodb_buffer_pool_dump_at_shutdown=1
    #innodb_buffer_pool_load_at_startup=1
    innodb_buffer_pool_populate=0
    ## Disabled settings
    performance_schema=ON
    innodb_stats_on_metadata=OFF
    innodb_sort_buffer_size=2M
    innodb_online_alter_log_max_size=128M
    query_cache_strip_comments=0
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    [mariadb-10.1]
    innodb_file_format = Barracuda
    innodb_file_per_table = 1
    
    ## wsrep specific
    # wsrep_on=OFF
    # wsrep_provider
    # wsrep_cluster_address
    # binlog_format=ROW
    # default_storage_engine=InnoDB
    # innodb_autoinc_lock_mode=2
    # innodb_doublewrite=1
    # query_cache_size=0
    
    # 2 variables needed to switch from XtraDB to InnoDB plugins
    #plugin-load=ha_innodb
    #ignore_builtin_innodb
    
    ## MariaDB 10 only save and restore buffer pool pages
    ## warm up InnoDB buffer pool on server restarts
    #innodb_buffer_pool_dump_at_shutdown=1
    #innodb_buffer_pool_load_at_startup=1
    innodb_buffer_pool_populate=0
    ## Disabled settings
    performance_schema=OFF
    innodb_stats_on_metadata=OFF
    innodb_sort_buffer_size=2M
    innodb_online_alter_log_max_size=128M
    query_cache_strip_comments=0
    log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
    
    # Defragmenting unused space on InnoDB tablespace
    innodb_defragment=1
    innodb_defragment_n_pages=7
    innodb_defragment_stats_accuracy=0
    innodb_defragment_fill_factor_n_recs=20
    innodb_defragment_fill_factor=0.9
    innodb_defragment_frequency=40
    
    
    Other relevant infos:
    Code:
    System Info
    -----------
    Processor       : Intel Core Processor (Haswell, no TSX)
    CPU Cores       : 2
    Frequency       : 2399.994 MHz
    Memory          : 7632 MB
    Swap            :  MB
    
    OS              : \S
    Arch            : x86_64 (64 Bit)
    Kernel          : 3.10.0-514.21.1.el7.x86_64
    
    I have also a forum on the same VPS as a subdomain which runs totally fine, it is just the wordpress site. I assume it is mostly because of the plugins?

    Any more infos needed? Let me know.

    Thanks for the help!
     
  2. eva2000

    eva2000 Administrator Staff Member

    29,051
    6,592
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,788
    Local Time:
    11:16 AM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    29,051
    6,592
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,788
    Local Time:
    11:16 AM
    Nginx 1.13.x
    MariaDB 5.5
    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.
    Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.
    Notes:
     
    • Like Like x 1