From owner-freebsd-questions@FreeBSD.ORG Wed May 22 18:11:30 2013 Return-Path: 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 7D6A8E13 for ; Wed, 22 May 2013 18:11:30 +0000 (UTC) (envelope-from aimass@yabarana.com) Received: from mail-wg0-x229.google.com (mail-wg0-x229.google.com [IPv6:2a00:1450:400c:c00::229]) by mx1.freebsd.org (Postfix) with ESMTP id 19169B5F for ; Wed, 22 May 2013 18:11:29 +0000 (UTC) Received: by mail-wg0-f41.google.com with SMTP id c11so2858443wgh.2 for ; Wed, 22 May 2013 11:11:29 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:x-gm-message-state; bh=d6QUCtEESe11gR0aKAuVqjMM0ACIVDMuaAcJblqx8gw=; b=gg6PTJLUSsmxfIAUF2CYo33xNjdv4LvaWxZO3elARE26/XkZCCMJRl3qQrEhvWU/lc 3b++BN1IZRE8WlMSQCUO5qFVuoXo2zV2Jc9iIu7Qzpe+T+Co6flITbepeYa2VrVf5Hlh mwcyHt4DG9m1C39UxfL6W1i02WZo7DzqB9U28iZAtN3sX7LTmOyKR7k0CUbeWcazkSgU ++pjnZkLWoSAwI3sr2nUtjTKB4hRgUhQHiya4xw7F0ytsyPVA2qtLPAP+MAM3Bl6o90d O+zBePQ263vPE0QEZTwo1AFo6az/PRbnHVIM0AC3DaHRmZJcV70PU26ZkAM6JaM872jB EsQQ== MIME-Version: 1.0 X-Received: by 10.180.185.244 with SMTP id ff20mr36285475wic.0.1369246289126; Wed, 22 May 2013 11:11:29 -0700 (PDT) Received: by 10.216.113.202 with HTTP; Wed, 22 May 2013 11:11:29 -0700 (PDT) In-Reply-To: References: Date: Wed, 22 May 2013 14:11:29 -0400 Message-ID: Subject: Re: MySQL hangs server completely From: Alejandro Imass To: Michael Ross Content-Type: text/plain; charset=ISO-8859-1 X-Gm-Message-State: ALoCoQmw0QVJSrVLyocZFqVT2kgu3M5XULy0KF0NSv87ZuDHtmplGv7dzfFcAYUIywilGMgAUBXq Cc: FreeBSD Questions X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 22 May 2013 18:11:30 -0000 On Wed, May 22, 2013 at 2:04 PM, Michael Ross wrote: > On Wed, 22 May 2013 15:52:45 +0200, Alejandro Imass > 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. > Right on the money. It doesn't immediately hang the server but in time it drains it to the point the shell stops responding and no more ssh access and even snmp stops responding! It doesn't happen immediately, but only after a while that MySQL has one of the CPUs at 100%. What I don't understand is how it manages to crash the whole server. Thanks, -- Alejandro Imass > > Regards, > > Michael