MySQL hangs server completely

[Alejandro Imass]

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?

The MySQL database is running inside a Jail and perhaps that could
help limit the damage it can cause. Has anyone else run into this
problem?

Thanks,

--
Alejandro Imass
_______________________________________________
freebsd-...@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to "freebsd-questi...@freebsd.org"

[Alejandro Imass]

On Wed, May 22, 2013 at 2:04 PM, Michael Ross <g...@ross.cx> wrote:

> On Wed, 22 May 2013 15:52:45 +0200, Alejandro Imass <aim...@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.
>

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

[Michael Ross]

On Wed, 22 May 2013 15:52:45 +0200, Alejandro Imass <aim...@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.