mysql over loaded

[rfygjlkhj]

hello everyone,
i have a problem with mysql it causes a very high load on the server and crashes it down,

server Information
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(TM) CPU 2.66GHz
Processor #1 speed: 2666.845 MHz
Processor #1 cache size: 512 KB

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

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

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


mysql 0 60.4 70.6 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.domain.com.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
thanks alot

[RavenSoul_]

You can install mytop (http://jeremy.zawodny.com/mysql/mytop/) and see, in real time, which user(s) is/are eating too much sources...

Installation of mytop:

  wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.4.tar.gz
  tar -zxvf mytop-1.4.tar.gz
  cd mytop-1.4
  perl Makefile.PL
  make
  make test
  make install

Type "mytop" as superuser (root) to run the program. Press "q" to exit it.

--

Good luck

[Blue|Fusion]

Sounds like you need it optimized and tweaked. I sent you a PM.

[rfygjlkhj]

hello,

i still have the same problem still have high load on the server,

ps -aux

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0  1552  548 ?        S    Jul08   0:05 init
root         2  0.0  0.0     0    0 ?        SW   Jul08   0:00 [migration/0]
root         3  0.0  0.0     0    0 ?        SW   Jul08   0:00 [migration/1]
root         4  0.0  0.0     0    0 ?        SW   Jul08   0:00 [migration/2]
root         5  0.0  0.0     0    0 ?        SW   Jul08   0:00 [migration/3]
root         6  0.0  0.0     0    0 ?        SW   Jul08   0:00 [keventd]
root         7  0.0  0.0     0    0 ?        SWN  Jul08   0:00 [ksoftirqd/0]
root         8  0.0  0.0     0    0 ?        SWN  Jul08   0:00 [ksoftirqd/1]
root         9  0.0  0.0     0    0 ?        SWN  Jul08   0:00 [ksoftirqd/2]
root        10  0.0  0.0     0    0 ?        SWN  Jul08   0:00 [ksoftirqd/3]
root        13  0.0  0.0     0    0 ?        SW   Jul08   0:00 [bdflush]
root        11  0.0  0.0     0    0 ?        SW   Jul08   0:08 [kswapd]
root        12  0.0  0.0     0    0 ?        SW   Jul08   0:21 [kscand]
root        14  0.0  0.0     0    0 ?        SW   Jul08   0:06 [kupdated]
root        15  0.0  0.0     0    0 ?        SW   Jul08   0:00 [mdrecoveryd]
root        23  0.0  0.0     0    0 ?        SW   Jul08   0:00 [scsi_eh_0]
root        24  0.0  0.0     0    0 ?        SW   Jul08   0:00 [scsi_eh_1]
root        27  0.0  0.0     0    0 ?        SW   Jul08   0:04 [kjournald]
root       547  0.0  0.0     0    0 ?        SW   Jul08   0:07 [kjournald]
root       548  0.0  0.0     0    0 ?        SW   Jul08   0:00 [kjournald]
root       549  0.0  0.0     0    0 ?        SW   Jul08   0:07 [kjournald]
root       550  0.0  0.0     0    0 ?        SW   Jul08   0:01 [kjournald]
root       551  0.0  0.0     0    0 ?        SW   Jul08   0:08 [kjournald]
root       552  0.0  0.0     0    0 ?        SW   Jul08   0:11 [kjournald]
root       980  0.0  0.0  2132  944 ?        S    Jul08   0:00 /sbin/dhclient -1
root      1154  0.0  0.0  1616  608 ?        S    Jul08   0:01 syslogd -m 0
root      1158  0.0  0.0  1560  500 ?        S    Jul08   0:00 klogd -x
root      1168  0.0  0.0  1552  484 ?        S    Jul08   0:02 irqbalance
named     1230  0.0  0.1 69424 3336 ?        S    Jul08   0:02 /usr/sbin/named -
root      1246  0.0  0.0  3672 1384 ?        S    Jul08   0:00 /usr/sbin/sshd
root      1260  0.0  0.0  2164  812 ?        S    Jul08   0:00 xinetd -stayalive
clamav    1269  0.0  0.7 26912 14916 ?       S    Jul08   0:02 clamd
mailnull  1354  0.0  0.0  6652 1860 ?        S    Jul08   0:00 /usr/sbin/exim -b
mailnull  1359  0.0  0.0  6640 1864 ?        S    Jul08   0:04 /usr/sbin/exim -b
mailnull  1366  0.0  0.0  6632 1820 ?        S    Jul08   0:00 /usr/sbin/exim -C
mailnull  1372  0.0  0.0  6636 1840 ?        S    Jul08   0:00 /usr/sbin/exim -t
root      1383  0.0  0.0  2940 1444 ?        S    Jul08   0:04 antirelayd
root      1417  0.0  0.3 13692 6748 ?        S    Jul08   0:16 /usr/local/apache
root      1485  0.0  0.0  1644  684 ?        S    Jul08   0:00 crond
root      1877  0.0  0.0  6944 1704 ?        S    Jul08   0:00 pure-ftpd (SERVER
root      1881  0.0  0.0  6692  988 ?        S    Jul08   0:00 /usr/sbin/pure-au
root      1921  0.0  0.3  8124 6696 ?        S    Jul08   0:15 /usr/bin/perl -w
root      1926  0.0  0.0  1556  460 tty1     S    Jul08   0:00 /sbin/mingetty tt
root      1927  0.0  0.0  1552  456 tty2     S    Jul08   0:00 /sbin/mingetty tt
root      1928  0.0  0.0  1560  460 tty3     S    Jul08   0:00 /sbin/mingetty tt
root      1929  0.0  0.0  1548  456 tty4     S    Jul08   0:00 /sbin/mingetty tt
root      1930  0.0  0.0  1556  456 tty5     S    Jul08   0:00 /sbin/mingetty tt
root      1931  0.0  0.0  1560  456 tty6     S    Jul08   0:00 /sbin/mingetty tt
root      2598  0.0  0.1  7292 2756 ?        S    Jul08   0:01 chkservd
root     31384  0.0  0.1  8896 3648 ?        S    13:47   0:00 /usr/bin/perl /us
nobody   31385  0.1  0.7 21056 14588 ?       S    13:47   0:42 /usr/local/apache
nobody   31386  0.1  0.7 21020 14536 ?       S    13:47   0:45 /usr/local/apache
nobody   31387  0.1  0.7 21020 14540 ?       S    13:47   0:47 /usr/local/apache
nobody   31388  0.1  0.7 21348 15056 ?       S    13:47   0:41 /usr/local/apache
nobody   31389  0.1  0.6 20336 13880 ?       S    13:47   0:42 /usr/local/apache
nobody   31413  0.1  0.7 21104 14648 ?       S    13:47   0:44 /usr/local/apache
nobody   31444  0.1  0.7 20964 14496 ?       S    13:47   0:43 /usr/local/apache
nobody   31471  0.1  0.7 21332 14848 ?       S    13:47   0:39 /usr/local/apache
nobody   31501  0.1  0.6 20588 14132 ?       S    13:47   0:43 /usr/local/apache
nobody   31527  0.1  0.7 21148 14832 ?       S    13:47   0:42 /usr/local/apache
root      4241  0.0  0.4 11284 8988 ?        SN   13:49   0:06 cpanellogd - slee
root      4245  0.0  0.2  8320 4788 ?        S    13:49   0:00 cppop - accepting
mailnull  4254  0.0  0.2  6512 4232 ?        S    13:49   0:01 /usr/bin/perl /us
mailman   4260  0.0  0.2  7300 4796 ?        S    13:49   0:00 /usr/bin/python2
mailman   4286  0.0  0.2  7216 4728 ?        S    13:49   0:06 /usr/bin/python2
mailman   4287  0.0  0.2  7172 4732 ?        S    13:49   0:05 /usr/bin/python2
mailman   4288  0.0  0.2  7164 4728 ?        S    13:49   0:05 /usr/bin/python2
mailman   4289  0.0  0.2  7168 4724 ?        S    13:49   0:06 /usr/bin/python2
mailman   4290  0.0  0.2  7308 4764 ?        S    13:49   0:05 /usr/bin/python2
mailman   4291  0.0  0.2  7312 4808 ?        S    13:49   0:04 /usr/bin/python2
mailman   4294  0.0  0.2  7200 4720 ?        S    13:49   0:05 /usr/bin/python2
mailman   4295  0.0  0.2  7212 4724 ?        S    13:49   0:00 /usr/bin/python2
nobody    4318  0.0  0.0  1776  672 ?        S    13:49   0:00 /usr/local/cpanel
nobody    4320  0.0  0.1  3396 2060 ?        S    13:49   0:00 entropychat
mailnull  4514  0.0  0.7 21204 15944 ?       S    13:49   0:00 /usr/bin/perl -I/
mailnull  4515  0.0  2.5 60880 53236 ?       S    13:49   0:13 /usr/bin/perl -I/
root      4563  0.0  0.3  9664 7404 ?        S    13:49   0:01 cpsrvd - waiting
cpanel    4569  0.0  0.0  3700 1644 ?        S    13:49   0:00 /usr/bin/stunnel-
mailnull  4570  0.0  2.5 60876 53244 ?       S    13:49   0:15 /usr/bin/perl -I/
mailnull  4582  0.0  2.5 60880 53244 ?       S    13:49   0:13 /usr/bin/perl -I/
mailnull  4869  0.0  2.5 60880 53236 ?       S    13:50   0:13 /usr/bin/perl -I/
mailnull  4889  0.0  2.5 60752 53236 ?       S    13:50   0:14 /usr/bin/perl -I/
root     11374  0.0  0.0  2204 1068 ?        S    14:41   0:00 /bin/sh /usr/bin/
mysql    11401  0.0  3.9 319040 80408 ?      S    14:41   0:01 /usr/sbin/mysqld
mysql    11402  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11403  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11404  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11405  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11406  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11407  0.0  3.9 319040 80408 ?      S    14:41   0:06 /usr/sbin/mysqld
mysql    11408  0.0  3.9 319040 80408 ?      S    14:41   0:04 /usr/sbin/mysqld
mysql    11409  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11411  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11412  0.0  3.9 319040 80408 ?      S    14:41   0:00 /usr/sbin/mysqld
mysql    11420  0.0  3.9 319040 80408 ?      S    14:41   0:17 /usr/sbin/mysqld
mysql    11459  0.0  3.9 319040 80408 ?      S    14:42   0:17 /usr/sbin/mysqld
mysql    11461  0.0  3.9 319040 80408 ?      S    14:42   0:17 /usr/sbin/mysqld
mysql    11467  0.0  3.9 319040 80408 ?      S    14:43   0:16 /usr/sbin/mysqld
mysql    17373  0.0  3.9 319040 80408 ?      S    15:38   0:08 /usr/sbin/mysqld
mysql    17374  0.0  3.9 319040 80408 ?      S    15:38   0:10 /usr/sbin/mysqld
mysql    17383  0.0  3.9 319040 80408 ?      S    15:39   0:12 /usr/sbin/mysqld
mysql    18160  0.0  3.9 319040 80408 ?      S    15:46   0:08 /usr/sbin/mysqld
admin    17585  0.0  0.0  7044 1976 ?        S    21:00   0:00 pure-ftpd (IDLE)
world    17789  0.0  0.0  7044 1984 ?        S    21:02   0:00 pure-ftpd (IDLE)
root     18047  0.0  0.1  6896 2144 ?        S    21:05   0:00 sshd: root@pts/0
root     18063  0.0  0.0  5356 1380 pts/0    S    21:06   0:00 -bash
root     18329  0.0  0.0  2900  844 pts/0    R    21:09   0:00 ps -aux 

top

11401 mysql 0  0.0  9.3 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/host.mtclibya.com.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 

thank you so much