mySQL 5.0.24 High Server Load, Need Help

ReB

Member
Aug 10, 2006
20
0
151
Ever since Cpanel updated to mySQl 5.0.24 my server is having unusual high loads. Before my server load hardly makes to 3.

Server specs.

Dual Opteron 246 / 4 GB Ram / Dual SCSI 36 GB Drive

top - 21:25:16 up 3:44, 3 users, load average: 25.13, 17.36, 14.38
Tasks: 229 total, 5 running, 221 sleeping, 1 stopped, 2 zombie
Cpu(s): 49.3% us, 40.2% sy, 6.5% ni, 3.9% id, 0.0% wa, 0.2% hi, 0.0% si
Mem: 3115256k total, 2434556k used, 680700k free, 184932k buffers
Swap: 4192924k total, 144k used, 4192780k free, 1497384k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32320 mysql 15 0 1194m 175m 2856 T 99.9 5.8 30:50.04 mysqld
2077 munin 31 10 7912 4856 1700 R 12.9 0.2 0:00.40 munin-graph
32414 nobody 16 0 12160 7132 3380 S 1.0 0.2 0:01.85 httpd
31628 nobody 15 0 12008 6984 3384 S 0.6 0.2 0:02.28 httpd
31631 nobody 15 0 12048 7020 3380 S 0.6 0.2 0:02.08 httpd
31662 nobody 15 0 11976 6948 3380 S 0.6 0.2 0:02.20 httpd
32400 nobody 16 0 12156 7128 3380 S 0.6 0.2 0:01.77 httpd
32411 nobody 16 0 12112 7084 3380 S 0.6 0.2 0:01.89 httpd
32417 nobody 15 0 12052 7020 3376 S 0.6 0.2 0:01.62 httpd
.....

Current CPU Usage

Pid Owner Priority Cpu % Mem % Command
2196 mysql 0 99.9 5.1 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cp.tipidpchosting.com.pid --skip-locking
2423 nobody 0 0.9 0.2 /usr/local/apache/bin/httpd -DSSL
2444 nobody 0 0.9 0.2 /usr/local/apache/bin/httpd -DSSL
2445 nobody 0 0.9 0.2 /usr/local/apache/bin/httpd -DSSL
2949 nobody 0 0.9 0.2 /usr/local/apache/bin/httpd -DSSL
2424 nobody 0 0.6 0.2 /usr/local/apache/bin/httpd -DSSL
2426 nobody 0 0.6 0.2 /usr/local/apache/bin/httpd -DSSL
....
PID

Process : /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cp.tipidpchosting.com.pid --skip-locking
Owner : mysql
PID : 2196
CPU % : 5
MEM % : 7

Traced Process

Process 2196 attached - interrupt to quit
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_FILE, path="ò¿"}, [2]) = 144
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(144, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
fcntl64(144, F_SETFL, O_RDONLY) = 0
fcntl64(144, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(144, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(144, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0x859ada4, FUTEX_WAKE, 1) = 1
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_FILE, path="ò¿"}, [2]) = 144
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(144, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
fcntl64(144, F_SETFL, O_RDONLY) = 0
fcntl64(144, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(144, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(144, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0x859ada4, FUTEX_WAKE, 1) = 1
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_FILE, path="ò¿"}, [2]) = 144
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(144, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
fcntl64(144, F_SETFL, O_RDONLY) = 0
fcntl64(144, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(144, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(144, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0x859ada4, FUTEX_WAKE, 1) = 1
futex(0x859ada0, FUTEX_WAKE, 1) = 1
futex(0x8599500, FUTEX_WAKE, 1) = 1
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_FILE, path="ò¿"}, [2]) = 301
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(301, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
fcntl64(301, F_SETFL, O_RDONLY) = 0
fcntl64(301, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(301, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(301, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
futex(0x859ada4, FUTEX_WAKE, 1) = 1
select(5, [3 4], NULL, NULL, NULL) = 1 (in [4])
fcntl64(4, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(4, {sa_family=AF_FILE, path="ò¿"}, [2]) = 343
fcntl64(4, F_SETFL, O_RDWR) = 0
getsockname(343, {sa_family=AF_FILE, path="/var/lib/mysql"}, [28]) = 0
fcntl64(343, F_SETFL, O_RDONLY) = 0
fcntl64(343, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(343, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(343, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)
.....

Can somebody help me diagnose the problem and tell me what is wrong and what to do?


Thanks.
 

carluk

Well-Known Member
Sep 2, 2003
162
0
166
what does the following return:

Kernel:
Code:
uname -a
Usual suspects:
Code:
ps aux | grep httpd; ps aux | grep exim; ps aux | grep mysql
Then what's mysql doing?
Code:
mysqladmin processlist
Cpanel version:
Code:
/usr/local/cpanel/cpanel -V
Also out of interest whats your php and apache versions?
 

ReB

Member
Aug 10, 2006
20
0
151
Kernel Version : Linux cp.xxx.com 2.6.9-34.0.1.ELsmp

ps aux | grep httpd; ps aux | grep exim; ps aux | grep mysql

root 7166 0.0 0.1 11868 5160 ? Ss 22:07 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7247 0.1 0.2 13392 7148 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7248 0.2 0.2 13372 7132 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7249 0.1 0.2 13404 7160 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7250 0.1 0.2 13472 7240 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7251 0.2 0.2 13356 7116 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7252 0.1 0.2 13444 7200 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7253 0.1 0.2 13432 7192 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7254 0.1 0.2 13360 7120 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7255 0.2 0.2 13376 7148 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7256 0.1 0.2 13480 7240 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7257 0.1 0.2 13460 7220 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7258 0.1 0.2 13452 7212 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7259 0.1 0.2 13428 7188 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7260 0.1 0.2 13568 7324 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7261 0.1 0.2 13448 7208 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7262 0.1 0.2 13424 7184 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7263 0.1 0.2 13544 7300 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7264 0.2 0.2 13428 7188 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7265 0.2 0.2 13372 7132 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7266 0.1 0.2 13516 7272 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7267 0.2 0.2 13508 7268 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7268 0.2 0.2 13452 7212 ? S 22:07 0:02 /usr/local/apache/bin/httpd -DSSL
nobody 7269 0.2 0.2 13472 7124 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7270 0.1 0.2 13416 7176 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7271 0.1 0.2 13392 7152 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7272 0.1 0.2 13388 7144 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7273 0.1 0.2 13440 7200 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7274 0.1 0.2 13428 7188 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7275 0.1 0.2 13800 7464 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7276 0.1 0.2 13556 7328 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7277 0.1 0.2 13536 7296 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7278 0.1 0.2 13412 7172 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7279 0.1 0.2 13380 7060 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7280 0.2 0.2 13520 7280 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7281 0.1 0.2 13368 7124 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7282 0.1 0.2 13584 7344 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7283 0.1 0.2 13400 7160 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7284 0.2 0.2 13288 7048 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7285 0.1 0.2 13528 7288 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7286 0.2 0.2 13432 7192 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7287 0.1 0.2 13452 7208 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7288 0.1 0.2 13424 7180 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7289 0.1 0.2 13376 7148 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7290 0.1 0.2 13460 7232 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7291 0.2 0.2 13436 7196 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7292 0.2 0.2 13568 7328 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7293 0.2 0.2 13604 7364 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7294 0.1 0.2 13380 7140 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7295 0.1 0.2 13508 7264 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7297 0.1 0.2 13592 7352 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7298 0.1 0.2 13368 7124 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7299 0.1 0.2 13516 7276 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7303 0.1 0.2 13428 7200 ? S 22:07 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7852 0.1 0.2 13320 7076 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7854 0.1 0.2 13528 7288 ? S 22:11 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7855 0.1 0.2 13464 7224 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7856 0.1 0.2 13504 7260 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7859 0.1 0.2 13384 7152 ? S 22:11 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7860 0.2 0.2 13456 7216 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7861 0.1 0.2 13436 7196 ? S 22:11 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7866 0.2 0.2 13500 7256 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7868 0.1 0.2 13468 7228 ? S 22:11 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7869 0.1 0.2 13452 7212 ? S 22:11 0:01 /usr/local/apache/bin/httpd -DSSL
nobody 7885 0.1 0.2 13432 7192 ? S 22:12 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7910 0.2 0.2 13372 7132 ? S 22:12 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7914 0.1 0.2 13580 7352 ? S 22:12 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7927 0.1 0.2 13384 7156 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7929 0.1 0.2 13556 7316 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7931 0.1 0.2 13420 7176 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7934 0.2 0.2 13420 7188 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7936 0.1 0.2 13440 7200 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7945 0.1 0.2 13596 7356 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7947 0.1 0.2 13340 7100 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7948 0.1 0.2 13620 7380 ? S 22:13 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 7971 0.2 0.2 13464 7224 ? S 22:14 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8477 0.1 0.2 13400 7156 ? S 22:17 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8499 0.1 0.2 13380 7132 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8504 0.2 0.2 13448 7204 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8505 0.1 0.2 13544 7296 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8506 0.1 0.2 13380 7128 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8507 0.1 0.2 13432 7188 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8508 0.1 0.2 13460 7208 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8509 0.1 0.2 13588 7344 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
.......
nobody 8518 0.1 0.2 13496 7236 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8520 0.2 0.2 13380 7120 ? S 22:19 0:00 /usr/local/apache/bin/httpd -DSSL
nobody 8939 0.0 0.0 0 0 ? Z 22:20 0:00 [httpd] <defunct>
nobody 8940 0.2 0.2 13656 7284 ? S 22:20 0:00 /usr/local/apache/bin/httpd
...
root 8952 0.0 0.0 5692 672 pts/3 R+ 22:20 0:00 grep httpd
mailnull 2362 0.0 0.0 8344 1720 ? Ss 17:42 0:00 /usr/sbin/exim -bd -q30m
mailnull 2367 0.0 0.0 8624 1684 ? Ss 17:42 0:00 /usr/sbin/exim -tls-on-connect -bd -oX 465
mailnull 2907 0.0 0.1 7764 5352 ? S 17:42 0:00 eximstats
root 8954 0.0 0.0 5580 676 pts/3 R+ 22:20 0:00 grep exim
root 4106 0.0 0.0 4600 1092 pts/3 S 21:42 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/cp.tipidpchosting.com.pid
mysql 4127 99.9 6.3 1181704 199180 pts/3 Sl 21:42 55:52 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cp.tipidpchosting.com.pid --skip-locking
root 8956 0.0 0.0 4824 652 pts/3 R+ 22:20 0:00 grep mysql

Cpanel Version : 10.8.2-STABLE_120

PHP Version : 4.4.3

Apache Version : 1.3.37 (Unix)
 

ReB

Member
Aug 10, 2006
20
0
151
MySQL Process List

+-------+------------------+-----------+-----------------+---------+------+----------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+-----------------+---------+------+----------------+ |
| 9111 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 37 | | |
| 9112 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 51 | | |
......
|
| 9131 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 15 | | |
| 9132 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 1 | Locked | UPDATE users SET lastip='203.87.183.230', activedate=NOW() WHERE uid=251272 |
| 9133 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 2 | | |
| 9138 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 7 | | |
| 9141 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 11 | | |
| 9148 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 26 | | |
| 9149 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 1 | Locked | SELECT status,uid,username,password FROM users WHERE uid=7678 |
| 9150 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 4 | | |
| 9151 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 36 | | |
.....

| 9249 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 30 | | |
| 9277 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 1 | Locked | SELECT status,uid,username,password FROM users WHERE uid=5308 |
| 9278 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 15 | | |
| 9279 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 7 | | |
| 9287 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 0 | Locked | SELECT name, iid, posts, price, primeflag, username FROM items INNER JOIN users USING (uid) WHERE se |
| 9289 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 16 | | |
| 9288 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 19 | | ........
|
| 10620 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 5 | | |
| 10619 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 0 | Locked | SELECT COUNT(*) fcnt FROM oldfeedback,users WHERE userid=14503 AND users.uid=oldfeedback.userid |
| 10623 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 22 | | |
.......
|
| 11226 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 0 | Locked | SELECT name, iid, posts, price, primeflag, username FROM items INNER JOIN users USING (uid) WHERE se |
| 11340 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 3 | | |
| 11617 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 0 | Locked | SELECT name, iid, posts, price, primeflag, username FROM items INNER JOIN users USING (uid) WHERE se |
| 11975 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 6 | | |
| 11980 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 9 | | |
| 11997 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 8 | | |
| 12000 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Query | 1 | Sorting result | SELECT name, iid, posts, price, primeflag, username FROM items INNER JOIN users USING (uid) WHERE se |
| 12858 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 16 | | |
........

| 14176 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 22 | | |
| 14177 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 4 | | |
| 14206 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 12 | | |
| 14240 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 34 | | |
| 14242 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 16 | | |
| 14253 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 78 | | |
| 14296 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 8 | | |
| 14327 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 51 | | |
| 14331 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 36 | | |
| 14382 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 5 | | |
| 14411 | tipidpc_tipidpc | localhost | tipidpc_tipidpc | Sleep | 12 | | |
| 14425 | tipidpc_adserver | localhost | tipidpc_adsnew | Sleep | 78 | | |
|........... |
| 14977 | tipidpc_adserver | localhost | tipidpc_adsnew | Sleep | 6 | | |
| 15011 | tipidpc_adserver | localhost | tipidpc_adsnew | Sleep | 3 | | |
| 15035 | root | localhost | | Query | 0 | | show processlist |
+-------+------------------+-----------+-----------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
[/qoute]
 

carluk

Well-Known Member
Sep 2, 2003
162
0
166
Under server status in WHM click "Apache Status" and see what apache it doing.

I would suggest upgrading to php 4.4.4 which you can do via /scripts/easyapache or probably easier via the WHM interface. Make sure 4.4.3 is de-selected before selecting 4.4.4

Also do:
Code:
/scripts/restartsrv_mysql
Has that resolved your load problems (the mysql restart)?

If you could post the following also I think you might have a mysql misconfiguration.

Code:
cat /etc/my.cnf
Also some information about your system:

Memory usage:
Code:
free -m
edit: my mistake for forgetting about the system information you gave up the first post.
 

ReB

Member
Aug 10, 2006
20
0
151
probably easier via the WHM interface. Make sure 4.4.3 is de-selected before selecting 4.4.4
I don't see it the panel? How do you do this?

Thanks in advance.
 

carluk

Well-Known Member
Sep 2, 2003
162
0
166
It's under "Software" in WHM called "Apache Update".
 

ReB

Member
Aug 10, 2006
20
0
151
/scripts/restartsrv_mysql : didn't solve the problem.

my.cnf

[mysqld]
max_connections = 1000
key_buffer = 1024M
myisam_sort_buffer_size = 128M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 6M
table_cache = 768M
thread_cache_size = 286M
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 96M

skip-innodb
tmpdir = /home/tmp
thread_concurrency=4

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

free -m

[email protected] [~]# free -m

...........................total used free shared buffers cached
Mem:...................3042 2358 683 0 222 1514
-/+ buffers/cache:.621 2421
Swap:..................4094 0 4094
carluk : It's under "Software" in WHM called "Apache Update".
Thanks.


nickp666 did you do a kernel upgrade recently? there have been multiple threads here about the 2.6.9-34.0.x.ELsmp kernel and load, increases with mysql, the answer was and is to upgrade to a newer kernel
I'll check it out.
 

AndyReed

Well-Known Member
PartnerNOC
May 29, 2004
2,221
4
193
Minneapolis, MN
nickp666 said:
did you do a kernel upgrade recently? there have been multiple threads here about the 2.6.9-34.0.x.ELsmp kernel and load, increases with mysql, the answer was and is to upgrade to a newer kernel
Not necessarily. Upgrading the Kernel is just one thing to solve high server load issue. It's perfectly normal to have 'spikes' in your Server Load. Peak visitor times, log processing, database backup scripts, automated cron jobs - all can cause 'spikes' in your server load. It's when the server load is constantly above 5.00 - that's the time to start asking questions. To read about the causes of high server load and how to solve it, go to:

http://www.servertune.com/kbase/security/server_load.html
http://www.servertune.com/kbase/security/concept.html
 

nickp666

Well-Known Member
Jan 28, 2005
769
2
168
/dev/null
AndyReed said:
Not necessarily. Upgrading the Kernel is just one thing to solve high server load issue. It's perfectly normal to have 'spikes' in your Server Load. Peak visitor times, log processing, database backup scripts, automated cron jobs - all can cause 'spikes' in your server load. It's when the server load is constantly above 5.00 - that's the time to start asking questions. To read about the causes of high server load and how to solve it, go to:

http://www.servertune.com/kbase/security/server_load.html
http://www.servertune.com/kbase/security/concept.html
given recent problems it most likely is the cause (Had the exact same scenario on a server of mine), hence my suggestion to upgrade
 

ReB

Member
Aug 10, 2006
20
0
151
kernel update did the trick...it's now 2.6.9-34.0.1.ELsmp

it's now stable at 2-3...it used to be 0.5-2.0 but that's just fine with me...probably just need a little tweak.

Thanks for the help guys.
 

carluk

Well-Known Member
Sep 2, 2003
162
0
166
Suggestions (not perfect, might need a bit tweaking for 4GB memory and what else you have on the server) based upon your current my.cnf

Code:
[mysqld]

max_connect_errors = 10
max_connections = 250
max_user_connections = 50

wait_timeout = 15
connect_timeout = 15
interactive_timeout = 15

myisam_sort_buffer_size = 128M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 6M

key_buffer = 512M
table_cache = 1024M
thread_cache_size = 286M
max_allowed_packet = 16M

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 96M

skip-locking
skip-innodb
safe-show-database
tmpdir = /home/tmp
thread_concurrency=4

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
 

ReB

Member
Aug 10, 2006
20
0
151
Additional info.

The server runs only one site...it's a pretty heavy traffic site...average users at peak hours (Normally business hours) would be around 1,000 - 1,200..

though like i mentioned awhile ago that load was 0.5-2...hardly made 3.0 then suddenly rocketed..

carluk, i'll experiment with your input. though i must say that i can not use the number for the max_connections. but everything else i taken a note of. thanks.
 

ReB

Member
Aug 10, 2006
20
0
151
I guess I spoke too soon.

top - 09:14:15 up 8:37, 1 user, load average: 25.76, 16.23, 12.53
Tasks: 235 total, 4 running, 231 sleeping, 0 stopped, 0 zombie
Cpu(s): 63.9% us, 35.4% sy, 0.0% ni, 0.8% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 3115256k total, 1923964k used, 1191292k free, 133884k buffers
Swap: 4192924k total, 80k used, 4192844k free, 1126516k cached
This is at peak hours....before load never get past 3 at peak hours. we get same number of visits.
 

robocap

Well-Known Member
Nov 6, 2006
67
0
156
Hello
[email protected] [/]# free -m
total used free shared buffers cached
Mem: 1514 1468 45 0 53 396
-/+ buffers/cache: 1019 494
Swap: 2047 365 1681
[email protected] [/]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database
[email protected] [/]#

i can config max_connections=500 of where?
php.ini?

please Help me

Thanks
 

brendanrtg

Well-Known Member
Oct 4, 2006
311
0
166