From: Dan Nelson
Date: October 10 2001 2:33am
Subject: Re: Last try guys!  What is with the Spawning processes??
List-Archive: http://lists.mysql.com/mysql/87498
Message-Id: <20011009213310.D41739@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Oct 09), Ryan Shrout said:
> Other 'threads' that have addressed this issue include, but limited to:
> Re: constantly spawning processes
> Re: constantly spawning processes
> Re: MySQL Spawns Many Processes and Uses 100% CPU! PART #1
> Re: MySQL Spawning Tons of Processes
> Re: Mysql causing severe load problems on Dual-Athlon server?
> Re: MySQL Performance Problem
> 
> Two of these were started by me, the rest were done by other people. 
> I have read through ALL of these threads, and never has anyone come
> to our rescue with assistance.  A long time ago, I had suggestions
> like, "Your IDE throughput is low" or "upgrade your mysql version"
> but since I use SCSI and tested the throughput at over 40 MB/s and am
> using the latest mysql version, these are unfounded.

Every time I've seen this on systems I have access to, it's simply an
overload situation; You may be humming happily along at 5 queries/sec,
until someone runs a long select that takes 15 seconds, locking a
table; during those 15 seconds, ~10 update queries get backed up, plus
65 regular queries, *plus* if this is a backend for your stock
Apache/PHP server you've got 75 6MB apache processes hanging around
sucking up your RAM.  By this point, your Apache processes are causing
mysqld's cache to get swapped to disk, and you never recover.  And
that's only at 5 queries/sec.

SCSI helps here, not because of raw transfer rate, but the fact that
SCSI has tagged queuing allowing up to 256 (usually 48-64) overlapped
I/Os, SCSI drives usually run at higher RPM, have larger cache, and
smaller seek time than comparably sized IDE disks.  You still overload,
just not as easily as with IDE.

In Henning's case, he could probably solve his problem by switching to
BDB or InnoDB for his primary table, which would keep updates from
locking out queries.

-- 
	Dan Nelson
	dnelson@stripped