Innotop ? or how to resolve locking issues with Galera? - codership

The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Message from discussion Innotop ? or how to resolve locking issues with Galera?
Received-SPF: pass (google.com: domain of henrik.i...@gmail.com designates 209.85.223.172 as permitted sender) client-ip=209.85.223.172;
MIME-Version: 1.0
Reply-To: henrik.i...@avoinelama.fi
Sender: henrik.i...@gmail.com
In-Reply-To: <507FBAAE.30...@mikjaer.com>
References: <5b044d77-74da-4290-bbe6-50447e4a1328@googlegroups.com>
	<a4a28c647d9909a90c12bcac3a631...@codership.com>
	<CAKL74PMwhtYyX-tUAgO1TtLHTg2vy4eCD+dfpgecK6bBA5u...@mail.gmail.com>
	<3c76eb6a7b134d82ae2fb2f04ed71...@codership.com>
	<507D52DF.1000...@mikjaer.com>
	<CAKHykevtynB=LsPaPG9QpqG1SOtZA8mX0-j+bShNg5SYEXi...@mail.gmail.com>
	<507D668F.50...@mikjaer.com>
	<CAKHykesgjG=nWsmeE4fr62ZjzqStisvNcd30m8wQxj5sYva...@mail.gmail.com>
	<507DA6E3.3030...@mikjaer.com>
	<CAKHyketrdio2HrWLjRXBOrMqDQx1+yL0RPPK9qfH-7hyeGZ...@mail.gmail.com>
	<507F11D9.1030...@mikjaer.com>
	<CAKHykesMUpio0efGcUku4acsj1AL8j4sPwB9havsXGB=hgW...@mail.gmail.com>
	<507FBAAE.30...@mikjaer.com>
Date: Thu, 18 Oct 2012 13:08:41 +0300
Message-ID: <CAKHykev+-A=3Q-2iYkA=L17acjUWJGHZLb7Fax_673k=uYs...@mail.gmail.com>
Subject: Re: [codership-team] Innotop ? or how to resolve locking issues with Galera?
From: Henrik Ingo <henrik.i...@avoinelama.fi>
To: Mikkel Christensen <mik...@mikjaer.com>
Cc: codership-team@googlegroups.com
Content-Type: text/plain; charset=ISO-8859-1

> Any input?

Thanks for the info you sent off-list. A few more questions:

Do you take daily backups? If yes, do you use mysqldump? If yes, any
chance it was running in the morning when this happened? Note, it
could have been running on another node than the one you sent the
processlist and innodb status, it will still block replication from
succeeding.

Just checking, some symptoms match exactly what I have experienced
with mysqldump. If it's not mysqldump, then there could still be some
long running transaction dangling on any of the four nodes that is
causing similar behavior.

Ie the following could cause this:

SET tx_isolation=read-committed; # this is default
BEGIN;
SELECT * FROM innodb_table;
(go and have coffee...)

It's a bit of a gray area for me wrt InnoDB locking, but DELETEs (and
I think INSERTs) take some locks which will make them block until the
above transaction is finished. In a Galera cluster they will of course
block cluster wide if there is such a transaction on any of the nodes,
because deletes and inserts have to be committed on all nodes.

Come to think of it, this could also be an issue in Galera lock
certification process rather than InnoDB itself.

henrik



On Thu, Oct 18, 2012 at 11:15 AM, Mikkel Christensen <mik...@mikjaer.com> wrote:
> Den 17/10/12 22.49, Henrik Ingo skrev:
>>
>> Thanks for the explanation. I'll top post for brevity...
>>
>>
>> When the system starts to get locked, can any user do anything, or is
>> everything just locked?
>
> Non-database driven websites works, they at almost no time.
>
>> What is the value of SHOW GLOBAL VARIABLES LIKE "autocommit"?
>> Related:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_autocommit
>
> mysql> SHOW GLOBAL VARIABLES LIKE "autocommit";
> +---------------+-------+
> | Variable_name | Value |
> +---------------+-------+
> | autocommit    | ON    |
> +---------------+-------+
> 1 row in set (0.00 sec)
>
>> When it is happening (or even slightly before, when you see apache
>> processes starting to grow toward 400), could you do SHOW ENGINE
>> INNODB STATUS and copy-paste the section "Transactions"? (If you don't
>> want to send the queries here, send to me personally.)
>
> Yea no problem, the database answers quickly enough. Iv'e send you the
> processlist as well.
>
>> Related:
>>
>> http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
>> http://mtocker.livejournal.com/38194.html
>> Do you know if any of the apps in Apache have persistent connections
>> on or some kind of connection pool on? If yes, are you able to turn it
>> off?
>
> Typo3 uses persistent connections, don't know about the rest. But i do know
> that they connect to "127.0.0.1" instead of "localhost", hence forcing the
> stream into a socket. Can this be a problem?
>
>>
>> Basically, I'm suspecting that the apps you have migrated from MyISAM
>> to InnoDB might behave poorly with transactions. They might do
>> something like BEGIN; SELECT ...; UPDATE ...; ... and then never
>> commit. For MyISAM this is not an issue because it doesn't keep any
>> transaction state, all queries will be executed alone and forgotten.
>> But poor InnoDB would now be forced to forever keep uncommitted
>> transaction state open. It could even be just one query/code path in
>> one of your applications that does this. Next time, instead of killing
>> the 10 oldest apache processes, you could try killing just the most
>> oldest one.
>
> We did that this morning (it's shown in the processlist file as well) and
> after a couple of minutes it worked swiftly. I asked the developpers to go
> over that query.
>
> Any input?
>
> / Mikkel
>



-- 
henrik.i...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
Account Options
