NGINX + PHP-FPM + MYSQL - High CPU, Low RAM
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 )
Related videos on Youtube
Lew Ayotte
Updated on September 18, 2022Comments
-
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 about 11 yearsSo, 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 over 10 years+1. OP, try looking at mysql slow query log.