Open Stack

On 09/02/2014 07:15 AM, Duncan Thomas wrote:
> On 11 August 2014 19:26, Jay Pipes <jaypipes at gmail.com> wrote:
>
>> The above does not really make sense for MySQL Galera/PXC clusters *if only
>> Galera nodes are used in the cluster*. Since Galera is synchronously
>> replicated, there's no real point in segregating writers from readers, IMO.
>> Better to just spread the write AND read load equally among all Galera
>> cluster nodes.
>
> Unfortunately it is possible to get bitten by the difference between
> 'synchronous' and 'virtually synchronous' in practice.

Not in my experience. The thing that has bitten me in practice are 
Galera's lack of support for SELECT FOR UPDATE, which is used 
extensively in some of the OpenStack projects. Instead of taking a 
write-intent lock on one or more record gaps (which is what InnoDB does 
in the case of a SELECT FOR UPDATE on a local node), Galera happily 
replicates DML statements to all other nodes in the cluster. If two of 
those nodes attempt to modify the same row or rows in a table, then the 
working set replication will fail to certify, which results in a 
certification timeout, which is then converted to an InnoDB deadlock error.

It's the difference between hanging around waiting on a local node for 
the transaction that called SELECT FOR UPDATE to complete and release 
the write-intent locks on a set of table rows versus hanging around 
waiting for the InnoDB deadlock/lock timeout to bubble up from the 
working set replication certification (which typically is longer than 
the time taken to lock the rows in a single transaction, and therefore 
causes thundering herd issues with the conductor attempting to retry 
stuff due to the use of the @retry_on_deadlock decorator which is so 
commonly used everywhere)

FWIW, I've cc'd a real expert on the matter. Peter, feel free to 
clarify, contradict, or just ignore me :)

Best,
-jay