Multi-master database systems that span sites are an increasingly
common requirement in business applications. Yet the way
such applications work in practice is not quite what you would
think from accounts of NoSQL systems like Cassandra or SQL-based systems like Oracle RAC. In this article I would like
to introduce a versatile design pattern
for multi-master SQL applications in which individual
schemas are updated in a single location only but may have many
copies elsewhere both locally as well as on other sites.
This pattern is known as a system of record architecture. You can build
it with off-the-shelf MySQL and master/slave replication.
Let's …
Why This Post
While testing Yoshinori Matsunobo's MHA agent I found that
although the wiki has a very complete documentation, it was
missing a some details. This article intends to close that gap
and bring up some issues to keep in mind when you do your own
installation. At the end of the article I added a
Conclusions section, if you're not interested in the
implementation details, but to read my take on the project, feel
free to jump straight to the end from here.
My Test Case
Most of our production environments can be simplified to match
the MHA's agent most simple use case: 1 master w/ 2 or more
slaves and at least one more slave in an additional tier:
Master A --> …[Read more]
A previous article on this blog described Tungsten parallel replication using on-disk
queues. On-disk queues are now more or less finished,
and I just closed the covering issue for the feature. The work
is bug fixing and performance testing from here on out.
Speaking of performance, that looks fairly good. A
recent on-site test using production workloads showed 3.3X
improvement over native MySQL replication while holding resources
like memory down to much more reasonable levels than in-memory
queues. We have further optimizations on the way, so this
should improve.
Now that parallel replication is working a lot better, what is it
good for? Here is a good start: assuming your …
If you love a software product, you should try to improve it, and not be afraid of criticizing it. This principle has guided me with MySQL (where I have submitted many usability bugs, and discussed interface with developers for years), and it proves true for Tungsten Replicator as well. When I started working at Continuent, while I was impressed by the technology, I found the installation procedure and the product logs quite discouraging. I would almost say disturbing. Fortunately, my colleagues have agreed on my usability focus, and we can enjoy some tangible improvements. I have already mentioned the new installation procedure, which requires just one command to install a full master/slave cluster. I would like to show how you can use the new installer to deploy a multiple source …
[Read more]About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.
Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.
It was subtle code changes in function:
get_range_statistics adding
my $cxn_string_dc =
“DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;
my $pass = ‘password’;
my $dbh_slave = DBI->connect($cxn_string_dc, $user, $pass); …
Last week was a banner week for MySQL at OSCON. We had many MySQL
developers meeting with the MySQL community, conducting technical
sessions, leading BOF sessions, working the exhibit hall, and
confirming Oracle's leadership in the technical evolution of
MySQL. The highlight of the week was the unveiling of even
more 5.6 early access InnoDB and Replication features that are
now available for early adopters to download, evaluate and shape
via labs.mysql.com.
InnoDB is one of MySQL's "crown jewels" and beginning in 5.5 is
now the default storage engine. The following 5.6 feature
improvements are in direct response to community and customer
feedback and requests. The new 5.6 early access features
include:
- Full-text search
- REDO log files max size extended to 2 TB
- UNDO logs on their own tablespace …
At OSCON 2011 last week, Oracle delivered more early access (labs) features for MySQL 5.6 replication. These features are focused on better integration, performance and data integrity:
- The Binlog API: empowering the community to seamlessly integrate MySQL with other applications and data stores;
- Binlog Group Commit and Enhanced Multi-Threaded Slaves: continuing to deliver major improvements to replication performance;
- Durable Slave Reads: further enhancing data integrity.
These new features build on the significant replication enhancements announced as part of the MySQL 5.6.2 Development Milestone Release back in April.
We are always listening to our customers and community. And, based on their needs and input, the MySQL engineering team continues to take …
[Read more]Multi-master replication between sites is the holy grail of applications ranging from credit card processing to large-scale software-as-a-service (SaaS) operations. Tungsten Replicator is award-winning open source software that helps you solve a wide range of multi-master problems that you can only dream of tackling with MySQL native replication. Learn the nuts and bolts of multi-master
Binlog Group Commit Experiments
It was a while ago since I talked about binary log group commit. I had to spend time on a few other things.
Since then, Kristian has released a version of binary log group commit that seems to …
[Read more]
Back in February I wrote an article titled A Small Fix For mysql-agent. Since then we did
a few more fixes to the agent and included a Bytes Behind
Master (or BBM) chart. For those who can't wait to get their
hands on the code, here's the current version: MySQL SNMP agent RPM. For those who'd like to
learn about it's capabilities and issues, keep reading.
What to Expect From this Version
The article I quoted above pretty much describes the main
differences with the original project, but we went further with
the changes while still relying on Masterzen's code for the data
collection piece.
The first big change is that we transformed Masterzen's code into
a Perl module, …