From owner-freebsd-questions@FreeBSD.ORG  Wed May 22 18:29:17 2013
Return-Path: <owner-freebsd-questions@FreeBSD.ORG>
Delivered-To: freebsd-questions@freebsd.org
Received: from mx1.freebsd.org (mx1.freebsd.org
 [IPv6:2001:1900:2254:206a::19:1])
 by hub.freebsd.org (Postfix) with ESMTP id A93EB4AD
 for <freebsd-questions@freebsd.org>; Wed, 22 May 2013 18:29:17 +0000 (UTC)
 (envelope-from gmx@ross.cx)
Received: from www81.your-server.de (www81.your-server.de [213.133.104.81])
 by mx1.freebsd.org (Postfix) with ESMTP id 6DCD1D18
 for <freebsd-questions@freebsd.org>; Wed, 22 May 2013 18:29:17 +0000 (UTC)
Received: from [178.3.49.140] (helo=michael-think)
 by www81.your-server.de with esmtpsa (TLSv1:AES256-SHA:256)
 (Exim 4.74) (envelope-from <gmx@ross.cx>)
 id 1UfDOZ-0006Ik-EL; Wed, 22 May 2013 20:04:15 +0200
Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes
To: "FreeBSD Questions" <freebsd-questions@freebsd.org>, "Alejandro Imass"
 <aimass@yabarana.com>
Subject: Re: MySQL hangs server completely
References: <CAHieY7R6Xz=p-6RHN0rKyJaOLBufRv4tNR_XTW3T3Q=bShFGow@mail.gmail.com>
Date: Wed, 22 May 2013 20:04:07 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: "Michael Ross" <gmx@ross.cx>
Message-ID: <op.wxhy45zgg7njmm@michael-think>
In-Reply-To: <CAHieY7R6Xz=p-6RHN0rKyJaOLBufRv4tNR_XTW3T3Q=bShFGow@mail.gmail.com>
User-Agent: Opera Mail/12.15 (Win32)
X-Authenticated-Sender: gmx@ross.cx
X-Virus-Scanned: Clear (ClamAV 0.97.6/17261/Wed May 22 18:40:37 2013)
X-BeenThere: freebsd-questions@freebsd.org
X-Mailman-Version: 2.1.14
Precedence: list
List-Id: User questions <freebsd-questions.freebsd.org>
List-Unsubscribe: <http://lists.freebsd.org/mailman/options/freebsd-questions>, 
 <mailto:freebsd-questions-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-questions>
List-Post: <mailto:freebsd-questions@freebsd.org>
List-Help: <mailto:freebsd-questions-request@freebsd.org?subject=help>
List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-questions>, 
 <mailto:freebsd-questions-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Wed, 22 May 2013 18:29:17 -0000

On Wed, 22 May 2013 15:52:45 +0200, Alejandro Imass <aimass@yabarana.com>  
wrote:

> Hi,
>
> We've been having this problem with a customer for a while and it
> seems that some funky query makes MySQL use 100% of CPU. Nevertheless,
> even though you can see in top that it's only 1 CPU in 100% (out of 8)
> the server eventually becomes useless and stops responding completely.
>
> So my question is, how does a user process hang the whole server? What
> system resources could MySQL be draining to make the server stop
> responding completely?
>

In laymans terms - can't do better - MySQL racing itself to obtain a (  
table | memory | file ) lock?

I know I can death-stall the MySQL server at a customer's site if I give  
it a big enough query ( like, DROPping a table, recreating it and pushing  
backup data inside ) while cron's hourly backup-dump is running on the  
database. Just the MySQL server, the machine itself hasn't stalled yet -  
but I'm sitting at the console while doing this, so I don't know what  
would eventually happen if I'd let it sit for a while.


Regards,

Michael