Synchronous data replication over long distances has the sort of
seductive appeal that often characterizes bad ideas. Why
wouldn't you want every local credit card transaction
simultaneously stored on the other side of the planet far away
from earthquake, storms and human foolishness? The answer
is simple: conventional SQL applications interact poorly with
synchronous replication over wide area networks (WANs).
I spent a couple of years down the synchronous replication rabbit
hole in an earlier Continuent product. It was one of those
experiences that make you a sadder but wiser person. This
article digs into some of the problems with synchronous
replication and shows why another approach, asynchronous
multi-master replication, is currently a better way to manage
databases connected by long-haul networks.
Synchronous Replication between …
I have seen a few posts on DBA.SE (where I answer a lot of questions) recommending the use of semi-synchronous replication in MySQL 5.5 over a WAN as a way to improve the reliability of replication. My gut reaction was that this is a very bad idea with even a tiny write load, but I wanted to test it out to confirm. Please note that I do not mean to disparage the author of those posts, a user whom I have great respect for.
What is semi-synchronous replication?
The short version is that one slave has to acknowledge receipt of the binary log event before the query returns. The slave doesn’t have to execute it before returning control so it’s still an asynchronous commit. …
[Read more]Monitoring MySQL’s replication has always been a bit hit and miss, especially when trying to detect whether a slave is becoming overloaded or not. There’s been a few ways to do this in the past:
- Monitor the Seconds_behind_master variable from SHOW SLAVE STATUS, which is the difference between the timestamp passed down from the master that the event was executed, to the time that the SQL thread started executing the event.
- Use something like mk-heartbeat, which operates in the same way as Seconds_Behind_Master (trying to show you the actual time difference between the master and slave), and is a little more robust in complex replication chains, and other situations where Seconds_Behind_Master falls down (such as when the IO thread is lagging). …
We found a simple XA transaction that crashes MySQL 5.5 replication. This simple transaction inserts a row into an InnoDB table and a TokuDB table. The bug was caused by a flaw in the logging code exposed by the transaction’s use of two XA storage engines (TokuDB and InnoDB). This bug was fixed in the TokuDB 6.0.1 release.
Here are some details. Suppose that a database contains the following tables.
create table t1 (a int) engine=InnoDB
create table t2 (a int) engine=TokuDB
The following transaction
begin
insert into t1 values (1)
insert into t2 values (2)
commit
causes the replication slave to crash.
The crash occurs when mysqld tries to dereference a NULL pointer.
#4 0x000000000088e203 in MYSQL_BIN_LOG::log_and_order (this=0x14b8640, thd=0x7f7758000af0, xid=161, all=true, need_prepare_ordered=false, need_commit_ordered=true) at …
[Read more]Read the original article at Accidental DBA’s Guide to MySQL Management
So you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start. Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right. See that subscribe button? Grab our newsletter!
1. Installation
The “yum” tool is your friend. If you’re using debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL. It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweak and fixes. Also if …
[Read more]
Intro
Oracle is widely use to support back-end systems. On the
other hand, MySQL is the "go-to" data management solution for the
web-facing part of many businesses. If you have both Oracle
and MySQL in-house, you may already also have the need to share
data between them. In this article I'll describe software
that my colleagues and I have been working on to move data from
Oracle to MySQL in real-time without costing an arm and a leg.
Tungsten to the Rescue!
Latest Tungsten Replicator has many features, most of which are open-source,
but the recent one for me is particularly exciting - thanks to
the development done by my colleague Stephane Giron in the …
In my previous post I covered the shard-disk paradigm's pros
and cons, but the conclusion that is that it cannot really
qualify as a scale-out solution, when it comes to massive OLTP,
big-data, big-sessions-count and mixture of reads and
writes.
Read/Write splitting is achieved when numerous
replicated database servers are used for reads. This way the
system can scale to cope with increase in concurrent load. This
solution qualifies as a scale-out solution as it
allow expansion beyond the boundaries of one DB, DB
machines are shared-nothing, can be added as a slave to the
replication "group" when required.
And, as a fact, read/write …
From a Tumblr engineering blog post:
Tumblr is one of the largest users of MySQL on the web. At present, our data set consists of over 60 billion relational rows, adding up to 21 terabytes of unique relational data. Managing over 200 dedicated database servers can be a bit of a handful, so naturally we engineered some creative solutions to help automate our common processes.
Today, we’re happy to announce the open source release of Jetpants, Tumblr’s in-house toolchain for managing huge MySQL database topologies. Jetpants offers a command suite for easily cloning replicas, rebalancing shards, and performing master …
[Read more]Once MySQL is deployed inside a datacenter environment (i.e. forms a cloud ;-), major feature in it becomes replication. It is used to maintain hot copies, standby copies, read-only copies, invalidate external systems, replicate to external systems, etc. If this functionality is broken, datacenter is broken – components are not synchronized anymore, invalidations not done, data not consistent.
From performance perspective, replication not working properly results in unusable slaves so load cannot be spread. This results in higher load on other machines, including master (especially on master, if environment needs stronger consistency guarantees).
Judging on replication importance in MySQL deployments, it should attract performance engineering as much as InnoDB and other critical pieces. Though slave replication performance is being increased in 5.6, master side is not (well, group commit may help a bit, but not as much).
…
[Read more]MySQL and Continuent Tungsten at Constant Contact - How We Architected Our Replication StrategyThursday, June 14th10:00 am PDT/1:00 pm EDT19:00 CEST/18:00 BSTReserve your seat!Constant Contact is a provider of marketing services for over 500,000 small businesses and organizations worldwide, helping them to drive engagement and build relationships with current and prospective customers.As the