CPU load locks up box. Apache or MYSQL related.

[crypted]

top - 14:53:01 up 2 days,  1:09,  1 user,  load average: 52.22, 68.57, 37.83
Tasks: 346 total,   1 running, 343 sleeping,   0 stopped,   2 zombie
Cpu(s): 26.4%us, 11.2%sy,  0.2%ni,  0.0%id, 61.7%wa,  0.0%hi,  0.4%si,  0.0%st
Mem:   2063384k total,  1940664k used,   122720k free,    12108k buffers
Swap:  1951856k total,   932880k used,  1018976k free,   142528k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                
29613 web5      20   0  202m  49m 7656 S   18  2.5   0:02.42 php-cgi                                                                
 6500 mysql     20   0  502m  27m 2940 S   12  1.4  24:26.55 mysqld                                                                 
29458 web5      20   0  202m  49m 7656 S   11  2.5   0:00.96 php-cgi                                                                
28987 web8      20   0  195m  31m 7660 D    1  1.6   0:03.24 php-cgi                                                                
29201 www-data  20   0     0    0    0 Z    1  0.0   0:00.06 apache2 <defunct>                                                      
29335 web29     20   0  190m  33m 7636 S    1  1.7   0:00.48 php-cgi                                                                
29408 web8      20   0  203m  47m 7808 D    1  2.4   0:00.64 php-cgi                                                                
29515 web5      20   0  202m  50m 7684 S    1  2.5   0:00.78 php-cgi                                                                
   46 root      15  -5     0    0    0 S    1  0.0   0:07.94 kblockd/0                                                              
 3630 root      20   0 57384 4100  960 S    1  0.2  13:12.69 collectl                                                               
29269 web5      20   0  203m  45m 7640 D    1  2.3   0:00.96 php-cgi                                                                
29273 web5      20   0  204m  49m 7636 D    1  2.4   0:01.94 php-cgi                                                                
29294 web29     20   0  190m  34m 7684 S    1  1.7   0:00.52 php-cgi                                                                
29306 web5      20   0  204m  48m 7584 S    1  2.4   0:01.02 php-cgi                                                                
29326 web29     20   0  190m  33m 7636 S    1  1.7   0:00.48 php-cgi                                                                
29409 web8      20   0  201m  46m 7628 S    1  2.3   0:00.58 php-cgi                                                                
29412 web8      20   0  201m  46m 7624 D    1  2.3   0:00.62 php-cgi                                                                
29474 web8      20   0  201m  48m 7592 D    1  2.4   0:00.68 php-cgi                                                                
29514 web1      20   0  187m  34m 7788 D    1  1.7   0:01.00 php-cgi                                                                
29734 root      20   0 19216 1532  940 R    1  0.1   0:00.32 top

Debian Lenny x64, dual 3ghz P4, 2Gb ram, 500gb hdd.

I've noticed that the websites will get hit like crazy and cause massive load spikes. (inter5.org and areyouliberal.com, namely). I put a robots.txt limitation of 8 seconds for cycles to stop the massive Googlebot floods. I've also added caching to all Wordpress sites.

I've been trying to tweak my.cnf to allow better response times because I think it's going to end up being Mysql related. The tuning-primer.sh recommendations have been implemented almost entirely.

Still locks up. It will come back to responding after about 15 minutes. However, it will keep a CPU load of 50+ for about half an hour before things seem to settle causing bad lag.

Error logs don't really help too much.

mydns log

mydns[24690]: mydns: error finding NS type resource records for name `ns1' in zone 12: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: mydns: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): error during query: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: last message repeated 2 times
mydns[24690]: mydns: error finding NS type resource records for name `' in zone 12: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: mydns: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): error during query: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: last message repeated 2 times
mydns[24690]: mydns: error finding NS type resource records for name `ns2' in zone 12: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: mydns: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): error during query: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: last message repeated 2 times
mydns[24690]: mydns: error finding NS type resource records for name `' in zone 12: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: mydns: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): error during query: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: last message repeated 2 times
mydns[24690]: mydns: ns3.derekgordon.com.: error loading SOA: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: mydns: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): error during query: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)
mydns[24690]: last message repeated 2 times
mydns[24690]: mydns: ns3.derekgordon.com.: error loading SOA: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) (errno=2)

mail.err

Oct 12 14:48:24 my imapd-ssl: authentication error: Input/output error
Oct 12 14:49:45 my authdaemond: failed to connect to mysql server (server=localhost, userid=ispconfig): Lost connection to MySQL server at 'reading authorization packet', system error: 104
Oct 12 14:50:58 my imapd: authentication error: Input/output error
Oct 12 14:51:28 my imapd: authentication error: Input/output error
Oct 12 15:00:46 my imapd: authentication error: Input/output error
Oct 12 15:00:51 my authdaemond: failed to connect to mysql server (server=localhost, userid=ispconfig): Lost connection to MySQL server at 'sending authentication information', system error: 32

kern.log

Oct 12 10:15:45 my kernel: [164931.197729] php-cgi[24767]: segfault at 411347f0 ip 676429 sp 7fff02289320 error 4 in php5-cgi[400000+506000]
Oct 12 12:42:34 my kernel: [173901.308359] php-cgi[11310]: segfault at 44afc280 ip 676429 sp 7fffca89e5f0 error 4 in php5-cgi[400000+506000]

error.log web8 / inter5.org

[Tue Oct 12 15:04:16 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:04:43 2010] [warn] (104)Connection reset by peer: mod_fcgid: read data from fastcgi server error.
[Tue Oct 12 15:04:43 2010] [warn] (104)Connection reset by peer: mod_fcgid: ap_pass_brigade failed in handle_request function       
[Tue Oct 12 15:04:43 2010] [warn] (104)Connection reset by peer: mod_fcgid: read data from fastcgi server error.
[Tue Oct 12 15:04:44 2010] [error] [client 66.249.71.105] Premature end of script headers: index.php
[Tue Oct 12 15:05:19 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:05:36 2010] [warn] mod_fcgid: read data timeout in 360 seconds
[Tue Oct 12 15:05:37 2010] [error] [client 98.158.20.230] Premature end of script headers: index.php
[Tue Oct 12 15:06:33 2010] [warn] (104)Connection reset by peer: mod_fcgid: read data from fastcgi server error.
[Tue Oct 12 15:06:34 2010] [warn] (104)Connection reset by peer: mod_fcgid: ap_pass_brigade failed in handle_request function       
[Tue Oct 12 15:07:50 2010] [warn] mod_fcgid: read data timeout in 360 seconds
[Tue Oct 12 15:07:50 2010] [error] [client 72.14.199.155] Premature end of script headers: index.php
[Tue Oct 12 15:08:04 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:08:06 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:08:12 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:08:12 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Tue Oct 12 15:08:21 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$

access.log web8 / inter5.org has at least 10 queries a second. These use Mysql (wordpress installs).

No logs are being added for Mysql /var/log/ files, damnit.

Any thoughts on tuning this thing? Errors indicate an inability to access Mysql causing the daemons to not be able to produce information and lock up....

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[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

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
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
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
max_connections        = 150
table_cache            = 300
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 2M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
#long_query_time = 2
log-queries-not-using-indexes
#
# 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
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

join_buffer_size = 2M
max_heap_table_size = 80M
tmp_table_size = 80M

low_priority_updates = 1

concurrent_insert=2

[till]

Your server uses a lot of swap, thats normally a indication that it does not has enough RAM. Mysql and apache need a lot of ram to run fast as mysql caches the tables and queries in ram and when it has to use swap then the performance drops rapidly. Currently you have 2 GB ram installed, can you increase it to 4, 6 or 8 GB?

[Turbanator]

Memory is probably your main issue, but may also want to increase your mysql max_connections to much higher than 150 [500] (and ignore tuning-primer.sh stating that you have it set too high or allocating too much memory to mysql). I say that because mydns lookup and email/spam fighting also hits mysql (I think)....it solved many of my problems at least.


The segfaults you get are an ongoing issue that many of us are still trying to fix. I think falko pointed us to some articles just recently but I haven't had a chance to research them.

[crypted]

Till, I don't know on memory. The standard price at the datacenter is $180 onetime fee for an additional 2GB or an extra $18/monthly. If they will let me submit my own memory and pay an installation fee of like $20, then I'd be more apt to doing it.

Right now, there is 512MB free on the memory and almost all SWAP is free. It just goes crazy for some reason at random points and they all spike.

I'm 99.99999% it's SQL related. I've tweaked it a bit more and will give it 24 hrs before I run those tests again to determine if I need to tweak further.

[MarkSeger]

The good news is I can see from your TOP display that you have collectl running. Download/install collectl-utils and plot everything with colplot. Maybe something will jump out at you as to which resource is being starved.
-mark

[e100]

When I see high load averages the first thing I always check is disk IO.
Your load average should be less than or equal to the number of CPU cores you have. If the load is higher than the number of cores then you have a bottleneck somewhere and it is usually disk IO.

When this issue is happening run: vmstat 1
Ctrl+C will exit
This will print lots of data including blocks read/written each second.
You will see that IO in/out will be rather high.

Till and Turbanator are right, you need more RAM.
You were using nearly 1GB of SWAP and your CPU's were spending 61% of their time waiting on disk IO:

Cpu(s): 26.4%us, 11.2%sy,  0.2%ni,  0.0%id, 61.7%wa,  0.0%hi,  0.4%si,  0.0%st
Mem:   2063384k total,  1940664k used,   122720k free,    12108k buffers
Swap:  1951856k total,   932880k used,  1018976k free,   142528k cached