MySQL Spawns Many Processes and Uses 100% CPU! PART #1

[Ryan Shrout]

------=_NextPart_000_0240_01C1274E.1D4A8580
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

This message is broken into two parts because the MySQL list blocked it =
as being too big!!!
=20
Okay, here is the problem: everyone once in a while, say once or twice =
a day, my web server running RH 7.2, PHP 4.0.6 and MySQL, will seem to =
freeze (from a web users point of view). Upon SSH'ing into the server, =
and running "top" I see the system load averages in the 90s (!!!) and =
all I see for processes on the list is mysqld. The CPU usage is at =
100%, and the top mysqld process ususally is taking 14% or so, then 12% =
then a lot of 10%, and on down until it goes off the list.

Below, I have included a copy of the "top" status during one of the =
afore mentioned problem times:

3:17pm up 23 min, 1 user, load average: 87.18, 54.78, 24.56 =
=20
282 processes: 203 sleeping, 79 running, 0 zombie, 0 stopped =
=20
CPU0 states: 96.14% user, 3.0% system, 0.0% nice, 0.0% idle =
=20
CPU1 states: 95.5% user, 4.9% system, 0.0% nice, 0.0% idle =
=20
Mem: 1026964K av, 757988K used, 268976K free, 56K shrd, 8116K =
buff =20
Swap: 2048248K av, 0K used, 2048248K free 312316K =
cached=20
=
=20
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND =
=20
4784 mysql 18 0 28472 27M 2236 R 15.5 2.7 0:02 mysqld =
=20
4944 mysql 18 0 26316 25M 2236 R 7.6 2.5 0:00 mysqld =
=20
4940 mysql 19 0 26316 25M 2236 S 7.2 2.5 0:00 mysqld =
=20
4942 mysql 17 0 26316 25M 2236 S 7.2 2.5 0:00 mysqld =
=20
5049 mysql 19 0 24948 24M 2236 R 3.9 2.4 0:00 mysqld =
=20
4810 mysql 20 0 26316 25M 2236 R 3.1 2.5 0:01 mysqld =
=20
4808 mysql 20 0 26316 25M 2236 R 3.0 2.5 0:01 mysqld =
=20
4806 mysql 17 0 26316 25M 2236 R 2.8 2.5 0:01 mysqld =
=20
4727 mysql 20 0 28472 27M 2236 R 2.7 2.7 0:02 mysqld =
=20
4751 mysql 20 0 28472 27M 2236 R 2.7 2.7 0:02 mysqld =
=20
4759 mysql 20 0 28472 27M 2236 R 2.7 2.7 0:02 mysqld =
=20
4794 mysql 20 0 26468 25M 2236 R 2.7 2.5 0:01 mysqld =
=20
4799 mysql 14 0 26204 25M 2236 R 2.7 2.5 0:01 mysqld =
=20
4804 mysql 15 0 26316 25M 2236 R 2.7 2.5 0:01 mysqld =
=20
4823 mysql 20 0 26316 25M 2236 R 2.7 2.5 0:01 mysqld =
=20
4868 mysql 20 0 26316 25M 2236 R 2.7 2.5 0:01 mysqld =
=20
4798 mysql 14 0 26156 25M 2236 R 2.6 2.5 0:01 mysqld =
=20
4802 mysql 19 0 26316 25M 2236 R 2.5 2.5 0:01 mysqld =
=20
4866 mysql 20 0 26316 25M 2236 R 2.5 2.5 0:01 mysqld =
=20
4963 mysql 20 0 26316 25M 2236 R 2.5 2.5 0:01 mysqld =
=20
4762 mysql 16 0 28472 27M 2236 S 2.4 2.7 0:02 mysqld =
=20
4795 mysql 19 0 26580 25M 2236 R 2.4 2.5 0:01 mysqld =
=20
4821 mysql 18 0 26316 25M 2236 S 2.4 2.5 0:01 mysqld =
=20
4957 mysql 20 0 26316 25M 2236 R 2.4 2.5 0:01 mysqld =
=20
4997 mysql 20 0 26316 25M 2236 R 2.4 2.5 0:00 mysqld =
=20
4769 mysql 18 0 28472 27M 2236 S 2.3 2.7 0:02 mysqld=20

And here is my "my.cnf" file:

[mysqld]
datadir=3D/var/lib/mysql
socket=3D/var/lib/mysql/mysql.sock
set-variable =3D max_connections=3D300

[mysql.server]
user=3Dmysql
basedir=3D/var/lib

[safe_mysqld]
err-log=3D/var/log/mysqld.log
pid-file=3D/var/run/mysqld/mysqld.pid

When I do an "/etc/rc.d/init.d/mysqld restart" the server restarts, =
though a bit slowly, and I sometimes have to manually kill a few mysqld =
processes, but returns to normal. I have tried all kinds of things to =
figure out what is going on here. I raised the max_connections from 100 =
to 300, and edited the following httpd.conf settings:
MinSpareServers 15
MaxSpareServers 30
StartServers 15
MaxClients 150
MaxRequestsPerChild 1000

Ryan Shrout
Owner - Amdmb.com
http://www.amdmb.com/
rsh...@amdmb.com

------=_NextPart_000_0240_01C1274E.1D4A8580--

[Jeremy Zawodny]

On Fri, Aug 17, 2001 at 06:54:59PM -0400, Ryan Shrout wrote:
>
> This message is broken into two parts because the MySQL list blocked

> it as being too big!!!

>
> Okay, here is the problem: everyone once in a while, say once or

> twice a day, my web server running RH 7.2, PHP 4.0.6 and MySQL, will
> seem to freeze (from a web users point of view). Upon SSH'ing into
> the server, and running "top" I see the system load averages in the
> 90s (!!!) and all I see for processes on the list is mysqld. The
> CPU usage is at 100%, and the top mysqld process ususally is taking
> 14% or so, then 12% then a lot of 10%, and on down until it goes off

> the list.
>
> Below, I have included a copy of the "top" status during one of the

> afore mentioned problem times:

Your system has a fair amount of memory. Can we see the output of
SHOW PROCESSLIST, SHOW STATUS, and maybe SHOW VARIABLES when this
happens?

You might want to try out mytop

http://public.yahoo.com/~jzawodn/mytop/

to get an idea of what MySQL is doing at various times.

Jeremy
--
Jeremy D. Zawodny, <jza...@yahoo-inc.com>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936

MySQL 3.23.41-max: up 0 days, processed 10,728,969 queries (174/sec. avg)

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysql-th...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[Ryan Shrout]

Jeremy, it was included in PART #2 of the problem message. Did you get
that?

Ryan Shrout
Owner - Amdmb.com
http://www.amdmb.com/
rsh...@amdmb.com

[Werner Stuerenburg]

> Okay, here is the problem: everyone once in a while, say once or twice a day, my web server running RH 7.2, PHP 4.0.6 and MySQL, will seem to freeze (from a web users point of view). Upon SSH'ing
> into the server, and running "top" I see the system load averages in the 90s (!!!) and all I see for processes on the list is mysqld. The CPU usage is at 100%, and the top mysqld process ususally
> is taking 14% or so, then 12% then a lot of 10%, and on down until it goes off the list.

This is what I used to have. We invoked the scenario described in
http://linux.oreillynet.com/pub/a/linux/2000/06/29/hdparm.html
and since then things went back to normal without any other
tuning. Oh, one more thing: I changed pconnect to connect in php.

So it looks as like we had load problems at times due to poor hd
performance which gave trouble to mysql.

--
Herzlich
Werner Stuerenburg

_________________________________________________
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de

[Ryan Shrout]

Thank you for the tip, but this is not our problem. We are running SCSI
disks with over 40 MB/sec throughput. Any other ideas?

Ryan Shrout
Owner - Amdmb.com
http://www.amdmb.com/
rsh...@amdmb.com

----- Original Message -----
From: "Werner Stuerenburg" <w...@art-quarter.com>
To: "Ryan Shrout" <my...@amdmb.com>
Cc: <my...@lists.mysql.com>
Sent: Saturday, August 18, 2001 6:50 AM
Subject: Re: MySQL Spawns Many Processes and Uses 100% CPU! PART #1