Lossless RBR TL/DR: There’s been talk of moving the next release of MySQL to minimal RBR: I’d like to suggest an alternative: lossless RBR For MySQL 5.8 there was talk / suggestions about moving to minimal RBR as the default configuration (http://mysqlserverteam.com/planning-the-defaults-for-mysql-5-8/). I’m not comfortable with this because it means that by default you do not have … Continue reading Lossless RBR for MySQL 8.0?
The other day, I was discussing high availability (HA)
with other people. The discussion was going in the
direction of, as the application was only using INSERT ... ON DUPLICATE KEY
UPDATE (ODKU) and traffic could be replayed,
promoting a slave in case of the failure of the master was easy:
replaying traffic would make all the slaves converge
back to a common state. It looks as this could work but it
might not be as simple. Before going into the details,
let's take a step back and look into HA in general.
tl;dr: be careful when using row-based replication when replicating ODKU as slaves will stop replicating if a row is present on the master and missing on slave (or vice-versa).
High Availability by Promoting a Slave as the new Master
Let's suppose we are working …
Recently, I have faced one interesting issue with Master(Active)-Master(Passive) replication (RBR + Triggers). Passive master was stopped due to “Duplicate Entry” error with update statement. It was something like this.
Last_Error: Error ‘Duplicate entry ‘29014131’ for key ‘PRIMARY” on query. Default database: ‘db’. Query: ‘UPDATE `db`.`tab1` SET `empid`=’103′, `name`=’Nilnandan’, `address`=’India ‘, `postcode`=’D100′, `phone`=’878 515 7788’;
Interesting thing was , id (primary key column) was not updated in above update statement. Initially I was confused but when I check further, found that both servers has binglog_format = row and there are some triggers in “tab1” table which is inserting records into “tab2” (Another table).
After some investigation, found that “If under row-based replication the slave applied the triggers as …[Read more]
I configure all our master databases to use row-based binary
logging where I work. In my opinion it is a much safer option
than statement-based replication. The advantages and
disadvantages of both types of MySQL replication are detailed in
the online documentation here. You can't view the events a slave is
applying directly with 'show processlist' but by
issuing 'show open tables where in use' you can
detect what table is receiving the attention of the SQL thread.
If you need more information the mysqlbinlog command must be used
to decode the slaves relay logs or masters binary logs.
Our developers often change a lot of rows with a single update statement. This usually results in some reasonable replication lag on downstream slaves. Occasionally the lag continues to grow and eventually nagios …
I always knew RBR and unindexed tables didn't play along very
well, but never realized just how much you can distress a slave
can in some cases.
Consider this statement (yeah yeah, i know :)
mysql> delete from t1 order by rand(); Query OK, 78130 rows affected (2.61 sec)
t1 has no indexes and is an int field with numbers from 1 to 78130. However, this will cause the slave to re-read entire table for each row deleted! Here it's still running, causing 100% cpu usage:
---TRANSACTION 0 1799, ACTIVE 2390 sec, OS thread id 3672 fetching rows mysql tables in use 1, locked 1 153 lock struct(s), heap size 30704, 78281 row lock(s), undo log entries 35423
Number of rows inserted 78130, updated 0, deleted 35423, read 1076560253 0.00 inserts/s, 0.00 updates/s, 17.58 deletes/s, 367099.91 reads/s
Over a billion row reads 40 minutes later and it's not even half done yet.For a large table this could take weeks or years to …
There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:
Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0); Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1); Transaction 1: START TRANSACTION; Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1; Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1; Transaction 2: START TRANSACTION; Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1; Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2; Transaction 2: COMMIT; Transaction 1: COMMIT;
After this, the Master innodb_tbl would look like this:
Listening to Sheeri's presentation on MySQL 5.1, I saw that
there are a few questions left unanswered. I am listing here some
of the questions that I found interesting, plus a few from an
early webinar on the same topic.
- Q: does Partitioning physically split data?
- A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.
- Q: Can you set partitions to different servers?
- A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.
- Q: How efficient are Row-Based …
With all due respect to Monty (and I mean that — much respect is due), I have some serious issues with his portrayal of the 5.1 release. I hate to make my first entry on Planet MySQL about a controversy, but he encouraged people to blog about their experience with 5.1, so that’s what I’ll do here.
As a long time user, I am very confident that the quality of 5.1 GA far exceeds that of the initial 5.0 GA release (5.0.15). In fact, I would go further and suggest that the MySQL organization has if anything been too conservative about declaring 5.1 GA.
It’s obviously true that there are still many bugs open. However no software is bug free, especially not those with codebase as large as MySQL. So the question is not if they are bug free, but are the …[Read more]
A typical scenario is when a master has a few dozen slaves, and
instead of dealing with all of them directly, uses four relay
slaves, each one dealing with 6 slaves.
So, where's the trick? The trouble comes if you change replication format after you start the slave.
Example. One master (M), two relay slaves (R1, R2), with four slaves each …
For example, after executing this code:
create table t1 (id int, c char(10), d date);
insert into t1 values (1, 'abc', '2008-01-01');
insert into t1 values (2, 'def', '2008-08-19');
insert into t1 values (3, 'ghi', current_date());
select * from t1; …