MySQL high load, which prgress is ......

[digi]

MySQL give a high load. Running MySQL5 // PHP5
See attachment.

Wich appliclicationns are running, how to track them??

Thanks.

[root@xxx ~]# top -ci
top - 13:08:39 up 18:30, 1 user, load average: 98.62, 98.82, 97.02
Tasks: 377 total, 102 running, 275 sleeping, 0 stopped, 0 zombie
Cpu(s): 89.1% us, 10.9% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1034572k total, 1016116k used, 18456k free, 31676k buffers
Swap: 2096440k total, 146652k used, 1949788k free, 281248k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12976 mysql 18 0 99.6m 77m 2428 R 6.6 7.6 0:03.86 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysq
4865 mysql 25 0 99.6m 77m 2428 R 3.3 7.6 7:22.33 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysq
12084 mysql 25 0 99.6m 77m 2428 R 3.3 7.6 6:22.43 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysq

[patrik]

Turn on
log-slow-queries = /path/to/logfile
in your my.cnf and check if there are any bad queries running. You might also install and use mytop to get some information out of your mysql server.

[digi]

Turn on
log-slow-queries = /path/to/logfile
in your my.cnf and check if there are any bad queries running. You might also install and use mytop to get some information out of your mysql server.

Your advice is posted in every forum, and we have already done that.

[patrik]

Your advice is posted in every forum, and we have already dome that.

Then write what you have done in order to figure out the problem. How could I know what troubleshooting already has been done?

[ejai]

i have the same problem about the high load .. after running trough my system i see 1 of the user in my server running WPMU ( http://mu.wordpress.org ) ... i`ve suspend the account and my server is all normal again. i hope this can help because it sure did help me. :D

[seachen]

may i know how to check which user using high load?

checking from where?

[floyd]

Try using mytop

[h2d]

what exactly are u wanting to track

[selfwebhosting]

I tried to use mytop this way:


/usr/bin/mytop -s /tmp -u da_admin -p pass

but got this error:


Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.6/i386-linux-thread-multi /usr/lib/perl5/5.8.6 .) at /usr/bin/mytop line 22.
BEGIN failed--compilation aborted at /usr/bin/mytop line 22.


My server is running Fedora 4 and apache version is 2.0 built with customapache

[smtalk]

Use:


cpan
install DBI

[selfwebhosting]

Thank you very much! I got in cpan and installed DBI but when I run mytop I get this error:


install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.6/i386-linux-thread-multi /usr/lib/perl5/5.8.6 .) at (eval 11) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.
at /usr/bin/mytop line 235

I tried to install DBD::mysql but got this error and the install stopped:


Failed 25/28 test scripts. 421/426 subtests failed.
Files=28, Tests=426, 14 wallclock secs ( 9.80 cusr + 1.46 csys = 11.26 CPU)
Failed 25/28 test programs. 421/426 subtests failed.
make: *** [test_dynamic] Error 255
CAPTTOFU/DBD-mysql-4.005.tar.gz
/usr/bin/make test -- NOT OK
Running make install
make test had returned bad status, won't install without force
Failed during this command:
CAPTTOFU/DBD-mysql-4.005.tar.gz : make_test NO


What should I do now?

[smtalk]

You can try:


yum -y install perl-DBD-MySQL

[selfwebhosting]

You can try:


yum -y install perl-DBD-MySQL


I get this message running the above:


No Match for argument: perl-DBD-MySQL
Nothing to do

[selfwebhosting]

Thank for smtalk's help, now mytop is running OK and I now see all the mysql databases that are active. Thank you so much, smtalk !!!

[selfwebhosting]

Yes, mytop helped me nail down the problem! Just a few minutes after running mytop from root, the server high load occurred and there it was!!!

It turned out to be a database for a guestbook script that the site owner installed and then abandoned using it. That was an advanced guestbook script that used a MySQL database to store the guestbook entries. However, the owner of the website did not remove the script and the database that was associated with it. This out-of-date script left the door open for hacking.

I have made some suggestions for my customers here:

http://selfwebhosting.com/update/34/cause-of-server-cpu-high-load-nailed-down/

Anything more should I add to the suggestions?

[Eduard]

Hello,

I have same problem with installing mytop :

I try fron cpan to intall :
intall DBI


Failed 11/125 test scripts, 91.20% okay. 552/5517 subtests failed, 89.99% okay.
make: *** [test_dynamic] Error 255
/usr/bin/make test -- NOT OK
Running make install
make test had returned bad status, won't install without force

cpan>

and [root@s1 ~]# yum -y install perl-DBD-MySQL


No Match for argument: perl-DBD-MySQL

Have any sugestion?

Eduard