This post is a lab experiment learning from migration to the Percona Xtradb Cluster (Galera) and a very unexpected DEADLOCK scenario which took me back to basics. (root@localhost) [test]>insert into app values (1); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Little background: We needed to work on migrating the […]
Statement-based or row-based, or mixed? We’ve all seen this discussed at length so I’m not trying to rehash tired arguments. At a high level, the difference is simple:
- Statement based replication (SBR) replicates the SQL statements to the slave to be replayed
- Row based replication (RBR) replicates the actual rows changed to the slave to be replayed
- Mixed mode uses RBR in the event of a non-deterministic statement, otherwise uses SBR
Recently, I worked with a client to optimize their use of pt-online-schema-change and keep replication delay to a minimum. We found that using RBR in conjunction with a smaller chunk-time was the best result in their environment due to reduced IO on the slave, but I wanted to recreate the test locally as well to see how it looked in the generic sense (sysbench for data/load).
Here was my local setup: