mysql & HIGH server load, please help

[SoftDux]

Hi

For the past few days I have noticed the server load sky rocket, so much, that I cannot access it, either via WHM or SSH. At one stage, I was able to logon, and saw that MySQL has skyrocketed the server's load.

billing billing.softdux.com 6.93 0.04 0.2
Top Process %CPU 45.0 /usr/local/bin/php -q -f www/include/scripts/run_background_queue.inc.php www/ cron
Top Process %CPU 36.0 /usr/local/bin/php -q -f www/include/scripts/run_background_queue.inc.php www/ cron
Top Process %CPU 34.0 /usr/bin/php mbalert.php
trade tradeleads.co.za 4.84 0.01 24.4
Top Process %CPU 20.0 /usr/bin/perl default.cgi
Top Process %CPU 19.0 /usr/bin/perl default.cgi
Top Process %CPU 16.0 /usr/bin/perl default.cgi
ucan u-can.co.za 3.23 0.03 0.3
Top Process %CPU 33.0 /usr/bin/php index2.php
Top Process %CPU 30.6 /usr/bin/php index.php
Top Process %CPU 30.0 /usr/bin/php index.php
mysql 25.21 1.98 0.0
Top Process %CPU 56.3 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/gandalf.softdux.com.pid --skip-external-locking
Top Process %CPU 56.1 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/gandalf.softdux.com.pid --skip-external-locking
Top Process %CPU 55.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/gandalf.softdux.com.pid --skip-external-locking
softdux softdux.com 18.60 0.07 0.7
Top Process %CPU 48.0 /usr/bin/php cron.php
Top Process %CPU 41.0 /usr/bin/php index.php
Top Process %CPU 36.0 /usr/bin/php cron.php
root 11.92 0.87 1.9
Top Process %CPU 56.1 gzip
Top Process %CPU 28.5 gzip

At first I thought it's a "trouble" account, but then noticed the top three accounts constanly change, but MySQL is constanly at the top, when running top in SSH mode.

Can someone please assist me with this?

Here's the server specs:

Processor InformationProcessor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #1 speed: 688.804 MHz
Processor #1 cache size: 512 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #2 speed: 688.804 MHz
Processor #2 cache size: 512 KB

Processor #3 Vendor: GenuineIntel
Processor #3 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #3 speed: 688.804 MHz
Processor #3 cache size: 512 KB

Processor #4 Vendor: GenuineIntel
Processor #4 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #4 speed: 688.804 MHz
Processor #4 cache size: 512 KB



Memory Information


System InformationLinux localhost 2.6.9-023stab033.9-enterprise #1 SMP Tue Dec 5 14:40:57 MSK 2006 i686 i686 i386 GNU/Linux



Physcial Disks


Current Memory Usage total used free shared buffers cached
Mem: 8302304 7913416 388888 0 141716 1643024
-/+ buffers/cache: 6128676 2173628
Swap: 16771696 3538848 13232848
Total: 25074000 11452264 13621736



Current Disk UsageFilesystem Size Used Avail Use% Mounted on
/dev/vzfs 26G 16G 11G 60% /
ext3 9.9G 399M 9.0G 5% /tmp
/tmp 9.9G 399M 9.0G 5% /tmp

Server Load 20.08 (4 cpus)
Memory Used 73.5 %
Swap Used 21.10 %
Disk vzfs (/) 60 %

[picoleto]

have you tried logging into mysql when you notice the load is high and doing a

show full processlist;

?

[SoftDux]

This is the second "bit" of the same output....

update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 878 | trade_tradeleads | localhost | trade_b2b | Query | 417 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 889 | trade_tradeleads | localhost | trade_b2b | Query | 374 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 899 | trade_tradeleads | localhost | trade_b2b | Query | 350 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 902 | eguard_wbst1 | localhost | eguard_wbst1 | Sleep | 312 | | |
| 906 | trade_tradeleads | localhost | trade_b2b | Query | 308 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 908 | trade_tradeleads | localhost | trade_b2b | Query | 188 | Locked | select compid, company from companies where password = '73123457' or company = 'Tong Xin Engineering |
| 925 | trade_tradeleads | localhost | trade_b2b | Query | 175 | Locked | select compid, email, password, siteadminid, company, siteid from companies where email like "sales@ |
| 929 | trade_tradeleads | localhost | trade_b2b | Query | 171 | Locked | select compid, email, password, siteadminid, company, siteid from companies where email like "hnhy@h |
| 932 | trade_tradeleads | localhost | trade_b2b | Query | 167 | Locked | select compid, company from companies where password = '73123457' or company = 'Tong Xin Engineering |
| 940 | trade_tradeleads | localhost | trade_b2b | Query | 118 | Locked | select compid, email, password, siteadminid, company, siteid from companies where email like "sales@ |
| 945 | trade_tradeleads | localhost | trade_b2b | Query | 97 | Locked | select compid, email, password, siteadminid, company, siteid from companies where email like "hnhy@h |
| 947 | trade_tradeleads | localhost | trade_b2b | Query | 95 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 949 | root | localhost | | Sleep | 73 | | |
| 954 | trade_tradeleads | localhost | trade_b2b | Query | 46 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 959 | ucan_mamb1 | localhost | ucan_mamb1 | Sleep | 0 | | |
| 960 | root | localhost | | Query | 0 | | show processlist |
+-----+------------------+-----------+--------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+

[SoftDux]

I have, but don't know what to look for.....

Here's the current output (at this moment)

+-----+------------------+-----------+--------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------------------+-----------+--------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| 525 | eximstats | localhost | eximstats | Sleep | 130 | | |
| 557 | trade_tradeleads | localhost | trade_b2b | Query | 905 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 571 | trade_tradeleads | localhost | trade_b2b | Query | 904 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 577 | trade_tradeleads | localhost | trade_b2b | Query | 897 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 597 | trade_tradeleads | localhost | trade_b2b | Query | 898 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 603 | trade_tradeleads | localhost | trade_b2b | Query | 186 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 605 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 624 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 664 | trade_tradeleads | localhost | trade_b2b | Query | 173 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 686 | trade_tradeleads | localhost | trade_b2b | Query | 187 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 693 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 698 | trade_tradeleads | localhost | trade_b2b | Query | 187 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 706 | trade_tradeleads | localhost | trade_b2b | Query | 188 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 724 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 726 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 728 | trade_tradeleads | localhost | trade_b2b | Query | 190 | Locked | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 735 | trade_tradeleads | localhost | trade_b2b | Query | 907 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 754 | eguard_wbst1 | localhost | eguard_wbst1 | Sleep | 862 | | |
| 756 | trade_tradeleads | localhost | trade_b2b | Query | 869 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 760 | trade_tradeleads | localhost | trade_b2b | Query | 188 | Locked | update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 770 | trade_tradeleads | localhost | trade_b2b | Query | 187 | Locked | update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 777 | trade_tradeleads | localhost | trade_b2b | Query | 828 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 787 | trade_tradeleads | localhost | trade_b2b | Query | 802 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 794 | trade_tradeleads | localhost | trade_b2b | Query | 190 | Locked | update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 796 | trade_tradeleads | localhost | trade_b2b | Query | 187 | Locked | update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 800 | trade_tradeleads | localhost | trade_b2b | Query | 762 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 817 | trade_tradeleads | localhost | trade_b2b | Query | 707 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 821 | trade_tradeleads | localhost | trade_b2b | Query | 697 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 832 | trade_tradeleads | localhost | trade_b2b | Query | 645 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 834 | trade_tradeleads | localhost | trade_b2b | Query | 643 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 837 | trade_tradeleads | localhost | trade_b2b | Query | 631 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 844 | trade_tradeleads | localhost | trade_b2b | Query | 579 | converting HEAP to MyISAM | select distinct tradeleads.tradeleadid, tradeleads.type, tradeleads.subject, tradeleads.bid, tradele |
| 851 | trade_tradeleads | localhost | trade_b2b | Query | 191 | Locked | update companies set lastlogindate = current_date, logincounter = logincounter + 1 where compid = 12 |
| 862 | trade_tradeleads | localhost | trade_b2b | Query | 172 | Locked | select distinct companies.compid, companies.company, companies.date, companies.city, locations.locat |
| 866 | trade_tradeleads | localhost | trade_b2b | Query | 191 | Locked |

Running "top" at the same time (pressing shift - m)

top - 06:37:09 up 53 min, 1 user, load average: 15.94, 14.92, 12.35
Tasks: 180 total, 1 running, 179 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.0% us, 1.0% sy, 0.0% ni, 33.3% id, 64.7% wa, 0.0% hi, 0.0% si
Mem: 8302304k total, 8282960k used, 19344k free, 84688k buffers
Swap: 16771696k total, 3351328k used, 13420368k free, 2115256k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16136 mysql 15 0 796m 772m 2612 S 4 9.5 25:20.39 mysqld
20169 root 15 0 29536 26m 2104 S 0 0.3 0:13.61 spamd
20178 root 16 0 28088 25m 2068 S 0 0.3 0:01.23 spamd
17703 root 16 0 38704 24m 1000 S 0 0.3 0:06.31 clamd
19556 root 16 0 25896 23m 2012 S 0 0.3 0:01.84 spamd
19878 ucan 16 0 31304 18m 4776 S 0 0.2 0:00.86 php
21611 eguard 25 0 27640 17m 4512 S 0 0.2 0:30.18 php
13829 eguard 25 0 27480 17m 4516 S 0 0.2 0:30.18 php

What do you suggest?

[brendanrtg]

ANy chance you are on a VPS? Maybe reducing the number for mysql connections might help but of course you would still be better off finding the culprit.

[SoftDux]

This is on a VPS, and I have set the max_connections in my.cf to 300.

[kernow]

Am no expert but i think you should investigate this;

converting HEAP to MyISAM

[SoftDux]

I'm not 100% sure what that is, and why, nor what impact it could have, so if anyone could tell me more, it would be nice