Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: RBR (reset)

Delayed row-based replication with large tables lacking a primary key
+0 Vote Up -0Vote Down
posted by Joshua Prunier on Mon 13 May 2013 22:41 UTC
Tags:
(edit) mysql, Replication, innodb, primary key, RBR
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

  [Read more...]
Beware of RBR and tables without indexes
Employee +2 Vote Up -0Vote Down
posted by Shane Bester on Mon 03 May 2010 15:01 UTC
Tags:
(edit) RBR
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





  [Read more...]
Replication with InnoDB and MyISAM Transactions
Employee +0 Vote Up -0Vote Down
posted by Gary Pendergast on Fri 13 Feb 2009 06:26 UTC
Tags:
(edit) mysql, MySQL 5.1, Replication, RBR, regression

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
  [Read more...]
Q&A on MySQL 5.1
+0 Vote Up -0Vote Down
posted by Giuseppe Maxia on Sun 04 Jan 2009 16:35 UTC
Tags:
(edit) mysql, Replication, Events, Presentation, partitioning, 5.1, RBR, logs, microsecond, slow, sheeri
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 Replication operations compared to Statement based

  [Read more...]
Quality of 5.1 GA release
+0 Vote Up -0Vote Down
posted by Ryan Thiessen on Sun 30 Nov 2008 01:17 UTC
Tags:
(edit) Uncategorized, bugs, partitioning, Monty, 5.0, 5.1, ga, RBR, release model

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.

Overall Quality

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,

  [Read more...]
An odd spot in relay slaves
+0 Vote Up -0Vote Down
posted by Giuseppe Maxia on Sun 19 Oct 2008 20:14 UTC
Tags:
(edit) mysql, Replication, RBR, relay



A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate.
This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events.
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






  [Read more...]
Decoding binlog entries with row-based replication
+0 Vote Up -0Vote Down
posted by Giuseppe Maxia on Fri 12 Sep 2008 16:07 UTC
Tags:
(edit) mysql, Replication, binary log, decoder, RBR



If you have tried using row based replication, you may have noticed two things: (1) it fixes many inconsistencies of statement-based replication, but (2) the binlog is unfit for humans. Inspecting it after a problem occurs won't provide any useful information.
The instructions look like line noise, and when you don't see the result you were expecting you wonder if that's the case.

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());









  [Read more...]
Showing entries 1 to 7

Planet MySQL © 1995, 2013, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.