List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:May 24 2011 4:01pm
Subject:Re: After long semaphore waits MySQL becomes unresponsive.
View as plain text  
On 2011-05-24 16:30:13 you wrote:
> > 2 simultaneous queries with the same thread-id? Is that possible?
> 
> No, not with the same thread-id. Deadlock occurs when two threads attempt
> to lock the same resources, but do so in an order which causes both sides
> to hold part of the resources the other needs.

OK, but that would mean that the answer to the question:

"I may be wrong here, but I tend to interpret this as '140054029002496' is 
trying to get an exclusive lock on 0x78733f8, on which it already has an 
exclusive lock, and hence is deadlocked in some manner" is  'no there is 
another query' (i.e.: it isn't locked on mistakingly acquiring a lock it 
already has) rather then 'that seems likely' :)

> Say you have tables a and b, both wanted by threads 1 and 2. At roughly the
> same time, thread 1 locks a while thread 2 locks b. The next obvious step
> for both is that 1 tries to lock b and 2 tries to lock a; but those
> resources are both in use, so at this point both threads must wait for the
> other to free up the resources. Since neither of them can complete and
> free the held locks for the other to use, deadlock occurs.
> 
> In theory, this will last until one of them times out or gives up. In
> practice, the engine will (mostly...) notice that the second thread is
> trying to initiate a deadlock and unceremoniously shoot the bastard in the
> head.

And in my case, the server became unusable (kept running into semaphore locks 
at 769 seconds before a kill & start was given). Query timeouts / crashes I 
can live with, an unresponsive server I cannot...

> > didn't get any 'too many connections' errors, but timeouts on connecting.
> > But let's say that in this case (MySQL needing a kill -9) all bets about
> > proper connecting are sort of off ;) ). Nevertheless, according to the
> > manual, max_user_connections configures "The maximum number of
> > simultaneous connections permitted to any given MySQL user account.",
> > which would
> 
> Hmm. you're right. Still, I'd say that it's unlikely that connection
> swamping happens from more than one account at once, so it should help in
> most instances (and has saved me before).

Ack, could be, however, the rest of the users quickly eat away at the safety 
margin. If the max_connections is set to 5000, and mainly 1 user eats it away 
with for instance 4500 max_user_connections, I still have 19 other users which 
can quickly gobble up the remaining 500 between them. A mere 26-27 per user 
would already achieve that, and seeing as the 'runaway user' already has 4500 
connections, it is very likely something is going on like a load / visitors 
spike that other users also may be requiring / asking for more connections. 
All in all, I don't really think the max_user_connections would/will help the 
current problem. 

> > mean (and a test shows) it is per-user name, and it would only work if we
> > have only 1 non-admin/non-super-user, while in reality, we have several
> > users with specific privileges. Not an incredible lot, but about 20
> > nonetheless,
> 
> 'super' refers to a very specific privilege, not a random combination of
> administrative stuff like create/drop, and 20 users is a whole lot to give
> that to. You should really consider if that many need it.

I know that, that's why i said NON-super users :). There are but 2 SUPER 
users, 1 solely is used by the OS for stuff like 'flush-logs'  after 
logrotating (may connect and do something maybe 5 times a day max), 1 is 
solely used by the likes of me monitoring / debugging / administrating the 
server, never by code, and hopefully rarely connects :)

> > Ack, I assume this means the 'SHOW ENGINE INNODB STATUS;' at the
> > moment of the problem, rather then just the output I get now?
> 
> Yep. The output is a combination of current data and since-start counters.

OK, let's hope I never get to show that output (i.e: that the problem doesn't 
reoccur). Since the server has been restarted since-start counters will 
probably be pretty useless.

> > Hmm, we're never using explicit (table) locks, only implicit by the
> > innodb-transaction, ans usually even then the transaction is just the
> > single statement (autocommit is usually on save for a few instances).
> > Does order in joins matter in terms of setting them alphabetically? That
> > would mean
> 
> Uhh... Unsure, but it might.
> 
> > checking & rewriting a heck of a lot of codebase... Also, taking for
> > instance the latest detected deadlock from the innodb status, I don't
> > know how to
> 
> > actually prevent it on an SQL-query level:
> Strictly speaking, the engine should detect it and kill the thread
> initiating deadlock - as seen in your output there.

Yup, right there it did, And that's the way I like it: kill the/a query, which 
issues an error somewhere else we know if and how to handle in some 
application, rather then letting a database server with a light load grind to 
a halt. 

My main problem at hand is why the server did nothing but seize up 
gracelessly, rather then either dying (a last resort, but something we have 
failovers for) or killing queries (which we can handle).
-- 
Rik Wasmus
Thread
After long semaphore waits MySQL becomes unresponsive.rik24 May
  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
    • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
      • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
        • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus24 May
          • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman24 May
            • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
              • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May
                • Re: After long semaphore waits MySQL becomes unresponsive.Rik Wasmus25 May
                  • Re: After long semaphore waits MySQL becomes unresponsive.Johan De Meersman25 May