3

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
1
  • 1
    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.
    – Lew Ayotte
    Jan 4, 2013 at 19:01

3 Answers 3

1

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 )

1
  • +1. OP, try looking at mysql slow query log.
    – rvs
    Jul 21, 2013 at 9:45
0

Seems, that you have too many requests. Cache anything you can.

0

It could be a DDoS attak. See the connection stats on port 80 and if there is some pattern block the offensive IP.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .