NGINX + PHP-FPM + MYSQL - High CPU, Low RAM

6,415

Not is config problem,

Mysql use 120% of cpu maybe some slow querys.

High php usage, maybe some script or plugin.

Try to cache on wordpres and disable plugins.

Try install apc or xcache and memcached (w3tc or flexicache maybe help they works fine whit memcached )

Share:
6,415

Related videos on Youtube

Lew Ayotte
Author by

Lew Ayotte

Updated on September 18, 2022

Comments

  • Lew Ayotte
    Lew Ayotte over 1 year

    All of a sudden our server started to act really weird, extremely high CPU usage and low RAM usage. I've never seen this before and I'm not sure what to do. I feel like if I could get the system to start using more available RAM the CPU usage would be lower, but I cannot seem to tune it to do that. This is a WordPress single site w/ CometChat using PubNub. The CPU spiking started a couple weeks ago, sometimes it's load average is 10 sometimes it's 40+... I do not seem to be able to track down the cause though. I should have plenty of resources available to handle the traffic we're receiving. 8 x 2.4Ghz cores and 24gb of RAM!?

    I'd appreciate any help anyone could provide. This is some relevant data... (my latest attempt to calm things down)

    nginx.conf

    user www-data;
    worker_processes 4;
    pid /var/run/nginx.pid;
    
    events {
        worker_connections 4096;
        multi_accept on;
        accept_mutex_delay 50ms;
    }
    
    http {
    
        ##
        # Basic Settings
        ##
    
        sendfile on;
        #tcp_nopush on;
        #tcp_nodelay on;
        keepalive_timeout 5;
        keepalive_requests 100000;
        reset_timedout_connection on;
        types_hash_max_size 2048;
        server_tokens off;
    
        # server_names_hash_bucket_size 64;
        # server_name_in_redirect off;
    
        include /etc/nginx/mime.types;
        default_type application/octet-stream;
    
        ##
        # Logging Settings
        ##
    
        access_log /var/log/nginx/access.log;
        error_log /var/log/nginx/error.log;
    
        ##
        # File Cache Settings
        ##
    
        open_file_cache          max=20000  inactive=20s;
        open_file_cache_valid    30s;
        open_file_cache_min_uses 2;
        open_file_cache_errors   on;
    
        ##
        # Gzip Settings
        ##
    
        gzip on;
        gzip_static on;
        gzip_disable "msie6";
    
        gzip_vary on;
        gzip_proxied any;
        gzip_comp_level 6;
        gzip_buffers 16 8k;
        gzip_http_version 1.1;
        gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript;
    
        index index.php index.htm index.html redirect.php;
    
        upstream php {
                server 127.0.0.1:9000;
        }
    
        ##
        # Virtual Host Configs
        ##
    
        include /etc/nginx/conf.d/*.conf;
        include /etc/nginx/sites-enabled/*;
    }
    

    my.cnf

    [client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    key_buffer = 512M
    sort_buffer_size = 8M
    read_buffer_size = 8M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 256M
    query_cache_size = 512M
     innodb_buffer_pool_size = 512M
    
    #
    # * Basic Settings
    #
    user            = mysql
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    #
    # * Fine Tuning
    #
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 256
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
     max_connections        = 400
     wait_timeout           = 250
     interactive_timeout    = 250
     join_buffer_size       = 8M
     tmp_table_size         = 512M
     max_heap_table_size    = 512M
     table_cache            = 2048
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 4M
    #query_cache_size       = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    
    log_error                = /var/log/mysql/error.log
    
    # Here you can see queries with especially long duration    
    slow_query_log          = 1
    slow_query_log_file     = /var/log/mysql/mysql-slow.log
    long_query_time = 2
    
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    

    /etc/php5/fpm/pool.d/www.conf

    pm = dynamic
    pm.max_children = 40
    pm.start_servers = 20
    pm.min_spare_servers = 10
    pm.max_spare_servers = 40
    pm.max_requests = 50000
    

    /var/log/php5-fpm.log

    [04-Jan-2013 12:08:59] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 0 idle, and 27 total children
    [04-Jan-2013 12:09:00] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 16 children, there are 0 idle, and 35 total children
    [04-Jan-2013 12:09:01] WARNING: [pool www] server reached pm.max_children setting (40), consider raising it
    

    but if I raise the max_children, it just increases the CPU load.

    free

                 total       used       free     shared    buffers     cached
    Mem:      24184356    8739828   15444528          0     239376    4526028
    -/+ buffers/cache:    3974424   20209932
    Swap:      1048572          0    1048572
    

    htop

      1  [||||||||||||||||||||||||||||||100.0%]     Tasks: 86, 279 thr; 43 running
      2  [||||||||||||||||||||||||||||||100.0%]     Load average: 36.83 37.80 40.27
      3  [||||||||||||||||||||||||||||||100.0%]     Uptime: 1 day, 13:55:16
      4  [||||||||||||||||||||||||||||||100.0%]
      5  [||||||||||||||||||||||||||||||100.0%]
      6  [||||||||||||||||||||||||||||||100.0%]
      7  [||||||||||||||||||||||||||||||100.0%]
      8  [||||||||||||||||||||||||||||||100.0%]
      Mem[||||||||||||||          3806/23617MB]
      Swp[                            0/1023MB]
    
      PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command
      635 mysql      20   0 5867M  661M  8520 S 120.  2.8 22h27:59 /usr/sbin/mysqld
    32522 www-data   20   0  342M 86872 37908 R 23.0  0.4  2:40.74 php-fpm: pool www
    32510 www-data   20   0  388M  131M 38572 R 20.0  0.6  2:49.39 php-fpm: pool www
    32508 www-data   20   0  361M  104M 38432 R 19.0  0.4  2:42.97 php-fpm: pool www
    32521 www-data   20   0  326M 69972 38784 R 18.0  0.3  2:35.50 php-fpm: pool www
    32516 www-data   20   0  386M  129M 38552 R 16.0  0.5  2:45.58 php-fpm: pool www
    32529 www-data   20   0  389M  131M 37916 R 16.0  0.6  2:42.09 php-fpm: pool www
    32504 www-data   20   0  370M  113M 38596 R 16.0  0.5  2:42.96 php-fpm: pool www
    32526 www-data   20   0  365M  108M 38552 R 15.0  0.5  2:42.65 php-fpm: pool www
    32507 www-data   20   0  360M  102M 37912 R 15.0  0.4  2:25.33 php-fpm: pool www
    32520 www-data   20   0  357M  101M 38540 R 15.0  0.4  2:43.34 php-fpm: pool www
    32523 www-data   20   0  361M  104M 38548 R 15.0  0.4  2:37.45 php-fpm: pool www
    32535 www-data   20   0  363M  106M 38552 R 15.0  0.4  2:49.62 php-fpm: pool www
    32530 www-data   20   0  342M 87856 38584 R 15.0  0.4  2:38.24 php-fpm: pool www
    32533 www-data   20   0  341M 86592 37604 R 15.0  0.4  2:52.92 php-fpm: pool www
    32511 www-data   20   0  371M  114M 38608 R 14.0  0.5  2:44.71 php-fpm: pool www
    32512 www-data   20   0  364M  106M 38416 R 14.0  0.5  2:39.31 php-fpm: pool www
    32514 www-data   20   0  349M 94528 38564 R 14.0  0.4  2:39.62 php-fpm: pool www
    32513 www-data   20   0  360M  103M 37820 R 14.0  0.4  2:43.11 php-fpm: pool www
    32528 www-data   20   0  380M  123M 38596 R 14.0  0.5  2:40.09 php-fpm: pool www
    32505 www-data   20   0  362M  105M 38540 R 14.0  0.4  2:47.40 php-fpm: pool www
    32524 www-data   20   0  344M 89840 38552 R 14.0  0.4  2:43.62 php-fpm: pool www
    32509 www-data   20   0  359M  102M 38568 R 14.0  0.4  2:48.95 php-fpm: pool www
    32537 www-data   20   0  369M  112M 38588 R 13.0  0.5  2:43.47 php-fpm: pool www
    32501 www-data   20   0  363M  105M 38436 R 13.0  0.4  2:41.65 php-fpm: pool www
    32498 www-data   20   0  342M 88104 38580 R 13.0  0.4  2:40.12 php-fpm: pool www
    32506 www-data   20   0  384M  126M 37816 R 13.0  0.5  2:43.70 php-fpm: pool www
    32536 www-data   20   0  337M 82956 38584 R 13.0  0.3  2:33.11 php-fpm: pool www
    32527 www-data   20   0  380M  122M 37536 R 13.0  0.5  2:37.99 php-fpm: pool www
    32538 www-data   20   0  364M  107M 38588 R 13.0  0.5  2:36.25 php-fpm: pool www
    32515 www-data   20   0  370M  113M 37920 R 12.0  0.5  2:38.53 php-fpm: pool www
    32496 www-data   20   0  346M 92484 38656 R 12.0  0.4  2:37.37 php-fpm: pool www
    32531 www-data   20   0  361M  103M 38460 R 12.0  0.4  2:45.09 php-fpm: pool www
    32532 www-data   20   0  356M   99M 38544 R 11.0  0.4  2:47.93 php-fpm: pool www
    17645 mysql      20   0 5867M  661M  8520 S  6.0  2.8  7:32.69 /usr/sbin/mysqld
    32525 www-data   20   0  349M 95344 38572 R  6.0  0.4  2:39.90 php-fpm: pool www
    30993 root       20   0 26096  2480  1404 R  5.0  0.0  5:26.88 htop
    12612 mysql      20   0 5867M  661M  8520 S  5.0  2.8 15:11.08 /usr/sbin/mysqld
    30744 mysql      20   0 5867M  661M  8520 R  5.0  2.8  0:07.03 /usr/sbin/mysqld
    30371 mysql      20   0 5867M  661M  8520 S  5.0  2.8  0:09.67 /usr/sbin/mysqld
    30336 mysql      20   0 5867M  661M  8520 S  5.0  2.8  0:07.02 /usr/sbin/mysqld
    27169 mysql      20   0 5867M  661M  8520 R  4.0  2.8 10:58.63 /usr/sbin/mysqld
    12121 mysql      20   0 5867M  661M  8520 S  4.0  2.8  8:13.50 /usr/sbin/mysqld
    30471 mysql      20   0 5867M  661M  8520 S  4.0  2.8  0:08.43 /usr/sbin/mysqld
    30723 mysql      20   0 5867M  661M  8520 S  4.0  2.8  0:07.12 /usr/sbin/mysqld
    30314 mysql      20   0 5867M  661M  8520 S  4.0  2.8  0:09.48 /usr/sbin/mysqld
    30462 mysql      20   0 5867M  661M  8520 R  4.0  2.8  0:08.27 /usr/sbin/mysqld
    30752 mysql      20   0 5867M  661M  8520 S  4.0  2.8  0:06.71 /usr/sbin/mysqld
     1371 mysql      20   0 5867M  661M  8520 S  4.0  2.8 17:29.79 /usr/sbin/mysql
    

    mysqltuner.pl

    >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.28-0ubuntu0.12.04.3-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 27M (Tables: 40)
    [--] Data in InnoDB tables: 55M (Tables: 61)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 61
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 13h 56m 26s (278M q [2K qps], 610K conn, TX: 643B, RX: 22B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 2.0G global + 32.2M per thread (400 max threads)
    [OK] Maximum possible memory usage: 14.6G (63% of installed RAM)
    [OK] Slow queries: 0% (92/278M)
    [OK] Highest usage of available connections: 72% (289/400)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/16.3M
    [OK] Key buffer hit rate: 100.0% (9M cached / 2K reads)
    [!!] Query cache efficiency: 0.0% (64K cached / 275M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 757K sorts)
    [OK] Temporary tables created on disk: 19% (233K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (289 created / 610K connections)
    [!!] Table cache hit rate: 18% (364 open / 1K opened)
    [OK] Open file limit used: 4% (194/4K)
    [OK] Table locks acquired immediately: 99% (276M immediate / 276M locks)
    [OK] InnoDB data size / buffer pool: 55.9M/512.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_limit (> 4M, or use smaller result sets)
        table_cache (> 2048)
    

    tuning-primer.sh

    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 2.000000 sec.
    You have 92 out of 279119812 that take longer than 2.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 256
    Current threads_cached = 196
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 400
    Current threads_connected = 62
    Historic max_used_connections = 289
    The number of used connections is 72% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 17 M
    Current InnoDB data space = 55 M
    Current InnoDB buffer pool free = 18 %
    Current innodb_buffer_pool_size = 512 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 10.59 G
    Configured Max Per-thread Buffers : 12.57 G
    Configured Max Global Buffers : 1.51 G
    Configured Max Memory Limit : 14.08 G
    Physical Memory : 23.06 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 16 M
    Current key_buffer_size = 512 M
    Key cache miss rate is 1 : 3464
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 512 M
    Current query_cache_used = 786 K
    Current query_cache_limit = 4 M
    Current Query cache Memory fill ratio = .14 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 8 M
    Current read_rnd_buffer_size = 8 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 8.00 M
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly
    join_buffer_size >= 4 M
    This is not advised
    
    OPEN FILES LIMIT
    Current open_files_limit = 4506 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 2048 tables
    Current table_definition_cache = 400 tables
    You have a total of 142 tables
    You have 364 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 512 M
    Current tmp_table_size = 512 M
    Of 952993 temp tables, 19% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 8 M
    Current table scan ratio = 9 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 5029903
    Your table locking seems to be fine
    
    • Lew Ayotte
      Lew Ayotte about 11 years
      So, this seemingly all started after I updated WordPress from 3.4.1 to 3.5. I ignored that fact, because, seriously! As if it was from that! Well, I just downgraded the site back to 3.4.2 and everything is smooth as butter again. I still do not think it was WordPress (since I have a billion sites on 3.5)... but I do think it was how the CometChat program was talking to WordPress.
  • rvs
    rvs over 10 years
    +1. OP, try looking at mysql slow query log.