> Best regards,
> Alex

> So does this release support an IST after crash ?

> On Sat, Oct 13, 2012 at 2:06 AM, Alex Yurchenko <
> alexey.yurche...@codership.com> wrote:

>> On 2012-09-29 23:03, Alexey Yurchenko wrote:

>>> Since this release contains two new features, we decided to make a
>>> release
>>> candidate first to gain some feedback. Provided there are no
>>> complains
>>> this
>>> code will be promoted to final release in a couple of weeks.

>> So I lied! So what? Sue me?

>> While we were waiting for complaints about 2.2 RC1 (got none
>> actually) we
>> managed to fix another bug and achieve serious improvements in
>> performance
>> and memory footprint for some workloads. We didn't want it go to
>> waste, so
>> here's RC2, the last one, honest... pinky swear!

>>  Downloads: https://launchpad.net/galera

>>> Best regards,
>>> Alex

>> --
>> Alexey Yurchenko,
>> Codership Oy, www.codership.com
>> Skype: alexey.yurchenko, Phone: +358-400-516-011

>> --

> We have 50 concurrent users, and four server each with 32GB of memory and
> 6x146GB 10.000 RPM i HP Proliant DL380 G5 with 2xQuadCore 2.5 Ghz Xeon ...
> and we run aprox. 8 Typo3 sites on this setup.

> 50 users and a mysql drop table shouldnt be a problem, _at all_ for this
> setup, or am i wrong?

>> We have 50 concurrent users, and four server each with 32GB of memory and
>> 6x146GB 10.000 RPM i HP Proliant DL380 G5 with 2xQuadCore 2.5 Ghz Xeon ...
>> and we run aprox. 8 Typo3 sites on this setup.

>> 50 users and a mysql drop table shouldnt be a problem, _at all_ for this
>> setup, or am i wrong?
> I agree.

>> What to look for and where?
> Check SHOW VARIABLES LIKE "wsrep_OSU_method"

> If it is 'TOI' then what you see is by design: During any DDL the
> cluster is locked. Even if for DROP TABLE of an unused table this is
> not necessary, Galera cannot know that the table is unused.

> To do DDL in a non-blocking way, you need to set wsrep_OSU_method="RSU"

> For more info:
> http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade

> henrik

> Is it posible to get it working ? Or do you know another way of resolving
> locking issues?

>> Is it posible to get it working ? Or do you know another way of resolving
>> locking issues?

> Perhaps first, could you explain a bit more what symptoms you see as
> "locking issues"? Do you get some errors (rollbacks, timeouts...) in
> your application? Do you see queries blocked (waiting for a lock) a
> long time?

> What are the specific symptoms you are trying to fix? Please
> copy-paste if you can.

> henrik

> --
> 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

> --

>> Is it posible to get it working ? Or do you know another way of resolving
>> locking issues?
> Perhaps first, could you explain a bit more what symptoms you see as
> "locking issues"? Do you get some errors (rollbacks, timeouts...) in
> your application? Do you see queries blocked (waiting for a lock) a
> long time?

> What are the specific symptoms you are trying to fix? Please
> copy-paste if you can.

> henrik

>> On Tue, Oct 16, 2012 at 9:26 PM, Mikkel Christensen <mik...@mikjaer.com>
>> wrote:

>>> It's me again, it seems as we are having periodic locking issues, and it
>>> seems as "innotop" does'nt work with galera.

>>> Is it posible to get it working ? Or do you know another way of resolving
>>> locking issues?

>> Perhaps first, could you explain a bit more what symptoms you see as
>> "locking issues"? Do you get some errors (rollbacks, timeouts...) in
>> your application? Do you see queries blocked (waiting for a lock) a
>> long time?

>> What are the specific symptoms you are trying to fix? Please
>> copy-paste if you can.

>> henrik

> It's hard to explain, but i'll try. We have  four node full stack cluster
> running lots of customize typo3, sugarcrm, third party authentification
> providers (like openid) and with  a singlesign on system spanning all of the
> portals. The code is made by a lot of different more or less skilled
> developers, so mildly speaking ... this is spagetti code.

> To make things worse we took the datebase from a mixed myisam and innodb
> environment to a pureley innodb environment to accomodate galera.

> Most of the times, right now for an instance 22:05 i the evening, the system
> is running smoothly with no problems what so ever. We tried emulating 2000
> concurrent users and the servers would just take the beating and serve its
> content. But tommorow morning around 9 o' clock (when the customers starts
> to use the site, and the editors starts to upload new content) i expect it
> to happend again.

> The first thing i se is that the apache process starts to grow in numbers,
> but the users on the site (according to google analytics) stays the same,
> short after the number of slow-queries starts to grow, and when i check my
> apache status i will se the same 400 apache processes idleing in the top of
> the list as if they are waiting for something.

> Our MySQL profiler claims that no tables are locked, and we don't know how
> to determine excatly what is going on at that excact problem when it locks
> up. I have tried stracing the top-most apache process, but it gave me no
> clues as to what was wrong.

> If i do nothing the systems stays locked up like this forever (15 minuttes,
> the boss would't let me go longer because of complaints from the customers)
> and after restarting apache on the four nodes everything goes back to normal
> within 30 seconds. If i kill the top 10 processes (which has lived the
> longest) on the apache status list everything goes back to normal within
> 60-120 seconds.

> I also tried using mytop to se the processlist, but the top most query is to
> my knowledge the next query in line to be excecuted, not the current one ...
> and besides killing the top 10 queries in there did'nt make any difference.

> - i really hope you can help?

> - And while im at it, im really gratefull for the help i have gotten so far
> and the help im hoping to receive, i have learnt so much during this process
> and i have made a lot of experience and small tools which i am looking
> forward to contribute back to the community ... once i have proven it's
> worth with this project :-)

> --
> De bedste hilsener / Best Regards
> Mikkel Christensen

> --

> When the system starts to get locked, can any user do anything, or is
> everything just locked?

> 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.

>> 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#s...

> 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-wal...
>> 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

> 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#s...
>> 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-wal...
>>> 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

> On 18/10/12 12.08, Henrik Ingo wrote:

>>> 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#s...

>>> 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-wal...
>>>> 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

> --
> --
> Haris Zukanovic

> --

> 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#s...

>> 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-wal...
>>> 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

> It turns out galera replication would block on deletes to a table
> that

> This is a well known issue (I don't know if it is a bug, I know
> Codership guys have made some fixes in this area though). But in any
> case it is best practice to alway define explicit primary keys when
> using Galera (it's a good idea anyway).

> henrik