Tungsten Replicator moved to GitHub with Apache license

It had been in the making for long time. Google announced that Google Code would be closing, and since then the Continuent team has been hard at work to handle the transition. You can guess it: this operation would have been quicker if it had been done by a small company like we were one year ago, but being part of a large corporation introduces some constraints that have affected our schedule.

However, our wish has always been, and still is, to keep Tungsten Replicator as an open source product, with full functionalities and with the full benefits that the open source development model offers.

Today, Tungsten Replicator is available on GitHub as vmware/tungsten-replicator, and it is wearing new clothes. It is not GPL anymore. In an effort to facilitate contributions, its license was changed to Apache 2.0.

Feature-wise, there is little difference from the previous release of 4.0. Mainly, we have cleaned up the code and moved out the pieces that no longer fit:

1. Bristlecone was removed from the package. It is used only for testing, and it will be released separately. There is no need to duplicate it into every Tungsten tarball.
2. The cookbook recipes have been retired. These scripts were created when the installer was still in its infancy and we had little documentation. Therefore, it was convenient to have wrappers for the common installation operations. Using the manual, it is pretty easy to install master/slave, fan-in, and multi-master topologies. The biggest reason for removing the cookbook, though, is that it was only useful for MySQL replication. If you need heterogenous deployments, the cookbook was an obstacle, rather than being helpful.
3. Some files were shuffled within the deployment tree. The ./tungsten-replicator/scripts directory was merged with ./tungsten-replicator/bin, the applier templates were moved from samples to a dedicated path, and we also did some other similar cleanup.

Although it has changed location and license, this is not a "release." If you compile the code, it will come up as 4.1, but it is still work in progress. Same as what was happening in the previous repository, we tag the code with the next version, and start working on it until it is deemed ready for release. The latest release for production (4.0.1) is still available from the old directory.

The code is available on GitHub, which makes collaboration much simpler than the previous repository. Take advantage of it: fork it, and help make the best replication tool even better!

MySQL replication monitoring 101

Replication is the process that transfers data from an active master to a slave server, which reproduces the data stream to achieve, as best as possible, a faithful copy of the data in the master.

To check replication health, you may start with sampling the service, i.e. committing some Sentinel value in the master and retrieving it from the slave.

Sentinel data: Tap tap… Is this thing on?

If you want to make sure that replication is working, the easiest test is using replication itself to see if data is being copied across from the master to the slaves. The method is easy:

1. Make sure that the data you want to see is NOT in the master or in the slave. If you skip this step, you may think that replication is working, while in fact it may not.
2. Either create a table in the master or use a table that you know exists both in the master and the slave.
3. Insert several records in the master table.
4. Check that they are replicated in the slave correctly.
5. Update a record in the master.
6. Watch it changing in the slave.
7. Delete a record in the master.
8. Watch it disappear in the slave.

On the road again - FOSSAsia

On the road again - FOSSAsia

It has been a few busy months until now. I have moved from Italy to Thailand, and the move has been my first priority, keeping me from attending FOSDEM and interacting with social media. Now I start catching my breath, and looking around for new events to attend. But before I get into this, let’s make a few things clear:

• I am still working for Continuent. Actually, it’s because of my company flexibility that I could move to a different country (a different continent, 6 time zones away) without much trouble. Thanks, Continuent! (BTW: Continuent is hiring! )
• I am still involved with MySQL activities, events, and community matters. I just happen to be in a different time zone, where direct talk with people in Europe and US need to happen on a different schedule.

I am already committed to attend Percona Live MySQL Conference & Expo in Santa Clara, where I will present a tutorial on MySQL replication features and a regular session on multi-master topologies with Tungsten.

But in the meantime, Colin encouraged me to submit talk proposals at FOSSAsia, and both my submissions were accepted. So, at the end of February I will be talking about some of my favorite topics:

• Easy MySQL multi master replication with Tungsten
• Data in the cloud: mastering the ephemeral

The exact schedule will be announced shortly. I am eager to attend an open source event in Asia. It’s been a long time since I went to a similar event in Malaysia, which was much pleasant.

PerconaLive 2014 program is published

PerconaLive 2014 program is published

After a few months of submissions and reviews, the program for PerconaLive MySQL Conference and Expo 2014 is published. The conference will be held in Santa Clara, from April 1 to 4, 2014.

Registration with early bird discount is available until February 2nd. If you plan to attend, this is probably the best time to act.

I will be presenting twice at the conference:

• MySQL Replication: Advanced features in all flavors it’s a 3 hours tutorial where I compare the features produced in the last years by MySQL, MariaDB, Tungsten, and show how well they work.
• Multi-master made easy: complex topologies with Tungsten Replicator it’s a 50-minutes session that explains how to create multi-master topologies, beyond the usual paradigms. In addition to all-masters, star, fan-in, I will show how to mix cases from these topologies to make multi-master usage powerful and easy.

Notice that the Call for Participation is still open for lightning talks and BoF. You can submit a talk until the end of January.

Parallel replication: off by one

One of the most common errors in development is where a loop or a retrieval by index falls short or long by one unit, usually because of an oversight or a logic in coding.

Of the following snippets, which one will run 10 times?

/* #1 */    for (N = 0 ; N < 10; N++) printf("%d\n", N);

/* #2 */    for (N = 0 ; N <= 10; N++) printf("%d\n", N); 

/* #3 */    for (N = 1 ; N <= 10; N++) printf("%d\n", N);

/* #4 */    for (N = 1 ; N < 10; N++) printf("%d\n", N);

The question is deceptive, as there are two snippets that will run 10 times (1 and 3). But they will print different numbers. If you ware aiming for numbers from 1 to 10, only #3 is good.

After many years of programming, off-by-one errors are rare in my code, and I have been able to spot them or prevent them at first sight. That’s why I feel uneasy when I look at the way parallel replication is enabled in MySQL 5.6,5.7 and MariaDB 10.0.5. In both cases, there is a variable that sets the number of replication threads:

set global slave_parallel_workers=5 in MySQL
set global slave_parallel_threads=5 in mariadb

Yet, for both implementations, you can set the number of threads to 1, and it is not the same as disabling parallel replication.

set global slave_parallel_workers=1 in MySQL
set global slave_parallel_threads=1 in mariadb

It will run parallel replication with one thread, meaning that you will have all the overhead of parallel replication with none of the benefits. Not only that, but replication actually slows down. The extra channel reduces performance by 7% in MariaDB and 10% in MySQL.

Now for the punch line. In Tungsten-Replicator, to disable parallel replication you set the number of channels to 1 (the intuitive value). If you set it to 0, the setup fails, as it should, since there would be no replication without channels. The reason for the fit is that in Tungsten, parallel replication was designed around the core functionality, while in MySQl and MariaDB it is an added feature that struggles to be integrated.

Tungsten Replicator Filters: A trove of golden secrets unveiled

UPDATE: This post is obsolete, for most of the URLs referenced here have been removed by the contents owner.

Since I joined the company in late 2010, I have known that one of the strong points of Tungsten Replicator is its ability of setting filters. The amazing capabilities offered by Tungsten filters cannot be fully grasped unless we explain how stage replication works.

There are several default stages in the replication stream. Every stage has an extraction task and an apply task. The extraction task will get data from the previous step repository and the apply task will save the data to the next repository, which can be either a temporary storage (memory queue, THL file) or the final destination (slave database server). Consider that the architecture allows developers to add stages, and you will appreciate its full power. For every stage, we can insert one or more filter between the two tasks. This architecture gives us ample freedom to design our pipelines.

What’s more interesting, filters in Tungsten can be of two types:

• built-in filters, written in Java, which require users to compile and build the software before the filter is available;
• Javascript filters, which can do almost everything the built-in scripts can do, but don’t require recompiling. All you need is deploy the filter in the appropriate folder, and run a tpm command to install or update the replicator.

The Tungsten team has developed a large number of filters, either to achieve general purpose pipeline control (such as renaming objects, excluding or including schemas and tables from replication) or filters providing very specific tasks that were needed to meet customer requirements or to help implementing heterogeneous pipelines (such as converting ENUM and SET columns to strings, dropping UPDATE and DELETE events, normalize DDL, etc). There are 23 built-in and 18 Javascript filters available. They were largely undocumented, or sparsely documented in the code. Now, thanks to my colleague MC Brown, Continuent director of documentation, the treasure is there for the taking.

• First off, there is a general guide of filter usage, which tells you how to enable or disable filters.
• Then, we have a full list of the built-in filters with their purpose, required parameters, and some applicability notes.
• For Javascript filters there is a section that explains in detail How to create and using Javascript filters, followed by the Reference list of all available filters.

Tungsten filters are a beautiful feature to explore, but also a powerful tool for users with special needs who want to create their own customized pipeline. A word of warning, though. Filters are powerful and sometimes fun to implement and use, but they don’t come free. There are two main things to keep in mind:

• Using more than one filter for the same stage may result in performance loss. The amount of performance loss depends on the stage and on your systems resources. For example if your replication weakest point is data transfer across the network, adding two or three filters to this stage (remote-to-thl) will make things worse. If you can apply the filter to the previous or next stages, you may achieve your purpose without many side effects. As always, the advice is: “test, benchmark, repeat.”
• Filters that alter data (such as exclude schemas or table, drop events) will make the slave unsuitable for promotion. You should always ask yourself if the filter may make the slave a lousy master, and if it does, make sure you have at least another slave that is replicating without filters.

As a final parting thought, be aware that yours truly and the above-mentioned MC Brown will be speaking at Percona Live London 2013, with a full, take-no-prisoners, 6 hours long complete Tungsten Replicator tutorial, where we will cover filters and give some explosive and sensational examples.

Speaking at the MySQL NoSQL & Cloud Conference & Expo in Buenos Aires

I am on my way to Argentina, where I will be speaking at the MySQL NoSQL & Cloud Conference & Expo.

I have two talks: one on my pet project MySQL Sandbox and one on replication between MySQL and MongoDB (using another project dear to me, Tungsten Replicator.

It’s my first visit to Argentina and I will try to look around a bit before the conference. And I look forward to see many ex colleagues and well known speakers at the conference. The lineup includes speakers from Percona, EffectiveMySQL, PalominoDB, MariaDB, SkySQL, Tokutek, and OpenStack.

I am looking forward to this trip. My presentation on MongoDB replication is a first for me, and I am always pleased when I can break new ground. I have the feeling that the networking is going to be more exciting than the sessions.

If you are in Buenos Aires, come to the conference and say hi!

Tungsten-Replicator 2.1.1 with better installation and built-in security

UPDATE 2013-08-30: Tungsten 2.1.2 was released.

UPDATE 2013-08-23: We have found a few problems that happen when replicating with RBR and temporal columns. We will have to publish an updated bugfix release quite soon.

Tungsten Replicator 2.1.1 is out. Key features in this release are:

• A better installer, of which we have already given a preview in tpm, the multi-master composer. The new installer allows faster and more powerful deployments of both single and multiple masters topologies. And it also allows the next feature:
• Secured communication layer. Now the replicator data and administrative messages can be encrypted with SSL across nodes. The security layer, once installed, is transparent. All replication features will keep working as before, and the encryption is independent from the database. In fact, heterogeneous replication (e.g. MySQL to MongoDB, Oracle to MySQL, etc) can use it just as easily as MySQL to MySQL replication.
• Full support for MySQL 5.6 binary log improvements. Now you can have the best of two worlds, running MySQL 5.6 enhanced performance, and Tungsten advanced replication features, without compromises. Due to this improvement, we also have the first change in our transport layer (the Transaction History Logs, or THL) since we released parallel replication. This means that a full cluster upgrade is needed (first slaves, and then masters) if you want to use the new release.

For more information on Tungsten Replicator 2.1.1, see the Release notes.

What does this mean for the common user? Let’s see what you can experience, when installing Tungsten Replicator 2.1.1

$ tar -xzf tungsten-replicator-2.1.1-230.tar.gz
$ cd tungsten-replicator-2.1.1-230
$ export VERBOSE=1
$ ./cookbook/install_master_slave
## -------------------------------------------------------------------------------------
## Installation with deprecated method will resume in 30 seconds - Hit CTRL+C now to abort
## -------------------------------------------------------------------------------------
## WARNING: INSTALLATION WITH tungsten-installer and configure-service IS DEPRECATED
## Future versions of Tungsten Cookbook will only support tpm-based installations
## To install with tpm, please set the variable 'USE_TPM' and start again
## -------------------------------------------------------------------------------------
....5....^C

Installation with tungsten-installer, which has been used until now, is still available, but it is deprecated. We want to encourage everyone to use tpm, as we will stop supporting tungsten-installer from the next release (2.1.2).

The main reason for using tpm instead of tungsten-installer, is that you can now install with security. The Tungsten manual has an extensive section on how to create security certificates. If you are not used to this kind of tasks, you may get discouraged from the very beginning, as you will need to create two key stores, one encrypted password store, and one file with JMX access rules. Tungsten Cookbook to the rescue! It will be enough to state our intention to install using tpm, with security enabled, and the cookbook script will generate the needed files for you.

$ export USE_TPM=1
$ export WITH_SECURITY=1
$ ./cookbook/install_master_slave
Certificate stored in file </home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/client.cer>
Certificate was added to keystore
[Storing /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts]
Using parameters:
-----------------
password_file.location   = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
encrypted.password   = true
truststore.location      = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts
truststore.password      = cookbookpass
-----------------
Creating non existing file: /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
User created successfuly: cookbook
Using parameters:
-----------------
password_file.location   = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
encrypted.password   = true
truststore.location      = /home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts
truststore.password      = cookbookpass
-----------------
User created successfuly: cookbook
# ---------------------------------------------------------------------
# Options for tpm
\
--thl-ssl=true \
--rmi-ssl=true \
--rmi-authentication=true \
--rmi-user=cookbook \
--java-keystore-password=cookbookpass \
--java-truststore-password=cookbookpass \
--java-truststore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/truststore.ts \
--java-keystore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/keystore.jks \
--java-jmxremote-access-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/jmxremote.access \
--java-passwordstore-path=/home/tungsten/tinstall/tungsten-replicator-2.1.1-230/cookbook/passwords.store
# ---------------------------------------------------------------------

Next, you will see the complete installation command using tpm, and the cluster will be built as smoothly as it would be without the security additions.

Notice that the paths that you see on the screen are created dynamically. Once installed, the security files will be deployed in a standard location, which will be easily picked up when you need to upgrade.

The difference that you will notice about the secure deployment is only in a few small differences. When using the cookbook tools, you will see a ssl label next to each secured node:

$ ./cookbook/show_cluster
--------------------------------------------------------------------------------------
Topology: 'MASTER_SLAVE'
--------------------------------------------------------------------------------------
# node host1 (ssl)
cookbook  [master]  seqno:          0  - latency:   0.681 - ONLINE
# node host2 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.397 - ONLINE
# node host3 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.683 - ONLINE
# node host4 (ssl)
cookbook  [slave]   seqno:          0  - latency:   1.684 - ONLINE

When using the traditional tools, you will notice one tiny difference in the master URI:

Processing status command...
NAME                     VALUE
----                     -----
appliedLastEventId     : mysql-bin.000008:0000000000000427;0
appliedLastSeqno       : 0
appliedLatency         : 0.681
channels               : 1
clusterName            : cookbook
currentEventId         : mysql-bin.000008:0000000000000427
currentTimeMillis      : 1377091602039
dataServerHost         : host1
extensions             :
latestEpochNumber      : 0
masterConnectUri       : thls://localhost:/    
masterListenUri        : thls://host1:2112/    
maximumStoredSeqNo     : 0
minimumStoredSeqNo     : 0
offlineRequests        : NONE
pendingError           : NONE
pendingErrorCode       : NONE
pendingErrorEventId    : NONE
pendingErrorSeqno      : -1
pendingExceptionMessage: NONE
pipelineSource         : /var/lib/mysql
relativeLatency        : 656.039
resourcePrecedence     : 99
rmiPort                : 10000
role                   : master
seqnoType              : java.lang.Long
serviceName            : cookbook
serviceType            : local
simpleServiceName      : cookbook
siteName               : default
sourceId               : host1
state                  : ONLINE
timeInStateSeconds     : 655.552
transitioningTo        :
uptimeSeconds          : 656.431
version                : Tungsten Replicator 2.1.1 build 230
Finished status command...

Instead of thl:// you see thls://. That’s the indication that the replicators are communicating using a SSL channel.

The same procedure works for multi-master and heterogeneous topologies. In fact, the very same mechanism is used in our commercial product, Continuent Tungsten, where it is installed using the same tools and the same tpm options.

For existing deployments we have a manual page dedicated to Upgrading from tungsten-installer to tpm-based installation. If you are a cookbook user, try

./cookbook/upgrade

There is a live webinar covering many Tungsten-Replicator 2.1.1 features. It is free, on Thursday, August 22nd, at 10am PT.

.

tpm, the multi-master composer

Multi master topologies blues

Tungsten Replicator is a powerful replication engine that, in addition to providing the same features as MySQL Replication, can also create several topologies, such as

• all-masters: every master in the deployment is a master, and all nodes are connected point-to-point, so that there is no single point of failure (SPOF).
• fan-in: Several masters can replicate into a single slave;
• star: It’s an all-masters topology, where one node acts as hub which simplifies the deployment at the price of creating a SPOF.

The real weakness of these topologies is that they don’t come together easily. Installation requires several commands, and running them unassisted is a daunting task. Some time ago, we introduced a set of scripts (the Tungsten Cookbook) that allow you to install multi-master topologies with a single command. Of course, the single command is just a shell script that creates and runs all the commands needed for the deployment. The real downer is the installation time. For an all-masters topology with 4 nodes, you need 17 operations, which require a total of about 8 minutes. Until today, we have complex operations, and quite slow.

Meet The TPM

Notice: these examples require a recent night build of Tungsten Replicator (e.g. 2.1.1-120), which you can download from http://bit.ly/tr_21_builds

But technology advances. The current tungsten-installer, the tool that installs Tungsten-Replicator instances, has evolved into a tool that has been used for long time to install our flagship product, Continuent Tungsten (formerly known as ‘Tungsten Enterprise’). The ‘tpm’ (Tungsten Package Manager) has outsmarted its name, as it does way more than managing packages, and actually provides a first class installation experience. Among other things, it provides hundreds of validation checks, to make sure that the operating system, the network, and the database servers are fit for the installation. Not only that, but it installs all components, in all servers in parallel.

So users of our commercial solution have been enjoying this more advanced installation method for quite a long time, and the tpm itself has improved its features, becoming able to install single Tungsten Replicator instances, in addition to the more complex HA clusters. Looking at the tool a few weeks ago, we realized that tpm is so advanced that it could easily support Tungsten Replicator topologies with minimal additions. And eventually, we have it!

The latest nightly builds of Tungsten Replicator include the ability of installing multi-master topologies using tpm. Now, not only you can perform these installation tasks using the cookbook recipes, but the commands are so easy that you can actually run them without help from shell scripts.

Let’s start with the plain master/slave installation (Listing 1). The command looks similar to the one using tungsten-installer. The syntax has been simplified a bit. We say members instead of cluster-hosts, master instead of master-host, replication-user and replication-password instead of datasource-user and datasource-password. And looking at this command, it does not seem worth the effort to use a new syntax just to save a few keystrokes.

./tools/tpm install alpha \
    --topology=master-slave \
    --home-directory=/opt/continuent/replicator \
    --replication-user=tungsten \
    --replication-password=secret \
    --master=host1 \
    --slaves=host2,host3,host4 \
    --start

Listing 1: master/slave installation.

However, the real bargain starts appearing when we compare the installation time. Even for this fairly simple installation, which ran in less than 2 minutes with tungsten-installer, we get a significant gain. The installation now runs in about 30 seconds.

Image 1 - Master/slave deployment

Where we see the most important advantages, though, is when we want to run multiple masters deployments. The all-masters installation command, lasting 8 minutes, which I mentioned a few paragraphs above? Using tpm, now runs in 45 seconds, and it is one command only. Let’s have a look

./tools/tpm install four_musketeers \
    --topology=all-masters \
    --home-directory=/opt/continuent/replicator \
    --replication-user=tungsten \
    --replication-password=secret \
    --masters=host1,host2,host3,host4 \
    --master-services=alpha,bravo,charlie,delta \
    --start

Listing 2: all-masters installation.

It’s worth observing this new compact command line by line:

• ./tools/tpm install four_musketeers: This command calls tpm with the ‘install’ mode, to the entity ‘four_musketeers’. This thing is a data service, which users of other Tungsten products and readers of Robert Hodges blog will recognize as a more precise definition of what we commonly refer to as ‘a cluster.’ Anyway, this data service appears in the installation and, so far, does not have much to say within the replicator usage. So just acknowledge that you can name this entity as you wish, and it does not affect much of the following tasks.
• –topology=all-masters: Some of the inner working of the installer depend on this directive, which tells the tpm what kind of topology to expect. If you remember what we needed to do with tungsten-installer + configure-service, you will have some ideas of what this directive tells tpm to do and what you are spared now.
• –home-directory=/opt/continuent/replicator: Nothing fancy here. This is the place where we want to install Tungsten.
• –replication-user=tungsten: It’s the database user that will take care of the replication.
• –replication-password=secret: The password for the above user;
• –masters=host1,host2,host3,host4: This is the list of nodes where a master is deployed. In the case of an all-masters topology, there is no need of listing the slaves: by definition, every host will have a slave service for the remaining masters.
• –master-services=alpha,bravo,charlie,delta: This is the list of service names that we will use for our topology. We can use any names we want, including the host names or the names of your favorite superheroes.
• –start: with this, the replicator will start running immediately after the deployment.

This command produces, in 45 seconds, the same deployment that you get with tungsten-installer in about 8 minutes.

Image 2 - all-masters deployment

The command is so simple that you could use it without assistance. However, if you like the idea of Tungsten Cookbook assembling your commands and running them, giving you access to several commodity utilities in the process, you can do it right now. Besides, if you need to customize your installation with ports, custom paths and management tools, you will appreciate the help provided by Tungsten Cookbook.

# (edit ./cookbook/USER_VALUES.sh)
export USE_TPM=1
./cookbook/install_all_masters

Listing 3: invoking tpm installation for all-masters using a cookbook recipe.

When you define USE_TPM, the installation recipe will use tpm instead of tungsten-installer. Regardless of the verbosity that you have chosen, you realize that you are using the tpm because the installation is over very soon.

The above command (either the one done manually or the built-in recipe) will produce a data service with four nodes, all of which are masters, and you can visualize them as:

./cookbook/show_cluster
--------------------------------------------------------------------------------------
Topology: 'ALL_MASTERS'
--------------------------------------------------------------------------------------
# node host1
alpha    [master]   seqno:         15  - latency:   0.058 - ONLINE
bravo    [slave]    seqno:         15  - latency:   0.219 - ONLINE
charlie  [slave]    seqno:         15  - latency:   0.166 - ONLINE
delta    [slave]    seqno:         15  - latency:   1.161 - ONLINE

# node host2
alpha    [slave]    seqno:         15  - latency:   0.100 - ONLINE
bravo    [master]   seqno:         15  - latency:   0.179 - ONLINE
charlie  [slave]    seqno:         15  - latency:   0.179 - ONLINE
delta    [slave]    seqno:         15  - latency:   1.275 - ONLINE

# node host3
alpha    [slave]    seqno:         15  - latency:   0.093 - ONLINE
bravo    [slave]    seqno:         15  - latency:   0.245 - ONLINE
charlie  [master]   seqno:         15  - latency:   0.099 - ONLINE
delta    [slave]    seqno:         15  - latency:   1.198 - ONLINE

# node host4
alpha    [slave]    seqno:         15  - latency:   0.145 - ONLINE
bravo    [slave]    seqno:         15  - latency:   0.256 - ONLINE
charlie  [slave]    seqno:         15  - latency:   0.208 - ONLINE
delta    [master]   seqno:         15  - latency:   0.371 - ONLINE

Listing 4: The cluster overview after an all-masters installation.

More topologies: fan-in

Here is the command that installs three masters in host1,host2, and host3, all fanning in to host4, which will only have 3 slave services, and no master.

./tools/tpm install many_towns \
    --replication-user=tungsten \
    --replication-password=secret \
    --home-directory=/opt/continuent/replication \
    --masters=host1,host2,host3 \
    --slaves=host4 \
    --master-services=alpha,bravo,charlie \
    --topology=fan-in \
    --start

Listing 5: Installing a fan-in topology.

Image 3 - Fan-in deployment

You will notice that it’s quite similar to the installation of all-masters. The most notable difference is that, in addition to the list of msters, the list of masters, there is also a list of slaves.

    --masters=host1,host2,host3 \
    --slaves=host4 \

Listing 6: How a fan-in topology is defined.

We have three masters, and one slave listed. We could modify the installation command this way, and we would have two fan-in slaves getting data from two masters.

    --masters=host1,host2 \
    --slaves=host3,host4 \
    #
    # The same as:
    #
    --masters=host1,host2 \
    --members=host1,host2,host3,host4 \

Listing 7: Reducing the number of masters increases the slaves in a fan-in topology.

Now we will have two masters in host1 and host2, and two fan-in slaves in host3 and host4.

Image 4 - Fan-in deployment with two slaves

If we remove another master from the list, we will end up with a simple master/slave topology.

And a star

The most difficult topology is the star, where all nodes are masters and a node acts as a hub between each endpoint and the others.

./tools/tpm install constellation \
    --replication-user=tungsten \
    --replication-password=secret \
    --home-directory=/opt/continuent/replication \
    --masters=host1,host2,host4 \
    --hub=host3 \
    --hub-service=charlie \
    --master-services=alpha,bravo,delta \
    --topology=star \
    --start

Listing 8: Installing a star topology.

Image 5 - star deployment

Now the only complication about this topology is that it requires two more parameters than all-masters or fan-in. We need to define which node is the hub, and how to name the hub service. But this topology has the same features of the one that you could get by running 11 commands with tungsten-installer + configure-service.

More TPM: building complex clusters

The one-command installation is just one of tpm many features. Its real power resides in its ability of composing more complex topologies. The ones shown above are complex, and since they are common there are one-command recipes that simplify their deployment. But there are cases when we want to deploy beyond these well known topologies, and compose our own cluster. For example, we want an all-masters topology with two additional simple slaves attached to two of the masters. To compose a custom topology, we can use tpm in stages. We configure the options that are common to the whole deployment, and then we shape up each component of the cluster.

#1
./tools/tpm configure defaults  \
    --reset \
    --replication-user=tungsten \
    --replication-password=secret \
    --home-directory=/home/tungsten/installs/cookbook \
    --start

#2
./tools/tpm configure four_musketeers  \
    --masters=host1,host2,host3,host4 \
    --master-services=alpha,bravo,charlie,delta \
    --topology=all-masters

#3
./tools/tpm configure charlie \
    --hosts=host3,host5 \
    --slaves=host5 \
    --master=host3

#4
./tools/tpm configure delta \
    --hosts=host4,host6 \
    --slaves=host6 \
    --master=host4

#5
./tools/tpm install

Listing 9: A composite tpm command.

In Listing 9, we have 5 tpm commands, all of which constitute a composite deployment order. In segment #1, we tell tpm the options that apply to all the next commands, so we won’t have to repeat them. In segment #2, we define the same 4 masters topology that we did in Listing 2. Segments #3 and #4 will create a slave service each on hosts host5 and host6, with the respective masters being in host3 and host4. The final segment #5 tells tpm to take all the information created with the previous command, and finally run the installation. You may be wondering how the tpm will keep track of all the commands, and recognize that they belong to the same deployment. What happens after every command is that the tpm adds information to a file named deploy.cfg, containing a JSON record of the configuration we are building. Since we may have previous attempts at deploying from the same place, we add the option –reset to our first command, thus making sure that we start a new topology, rather than adding to a previous one (which indeed we do when we want to update an existing data service).

The result is what you get in the following image:

Image 6 - all-masters deployment with additional slaves

A word of caution about the above topology. The slaves in host5 and host6 will only get the changes originated in their respective masters. Therefore, host5 will only get changes that were originated in host4, while host6 will only get changes from host4. If a change comes from host1 or host2, they will be propagated to host1 to host4, because each host has a dedicated communication link to each of the other masters, but the data does not pass through to the single slaves.

Different is the case when we add slave nodes to a star topology, as in the following example.

./tools/tpm configure defaults  \
    --reset \
    --replication-user=tungsten \
    --replication-password=secret \
    --home-directory=/home/tungsten/installs/cookbook \
    --start

./tools/tpm configure constellation  \
    --masters=host1,host2,host3,host4 \
    --master-services=alpha,bravo,delta \
    --hub=host3 \
    --hub-service=charlie \
    --topology=star

./tools/tpm configure charlie \
    --hosts=host3,host5 \
    --slaves=host5 \
    --master=host3

./tools/tpm configure delta \
    --hosts=host4,host6 \
    --slaves=host6 \
    --master=host4

./tools/tpm install

Image 7 - star deployment with additional slaves

In a star topology, the hub is a pass-through master. Everything that is applied to this node is saved to binary logs, and put back in circulation. In this extended topology, the slave service in host5 is attached to a spoke of the star. Thus, it will get only changes that were created in its master. Instead, the node in host6, which is attached to the hub master, will get all the changes coming from any node.

Extending clusters

So far, the biggest challenge when working with multi-master topologies has been extending an existing cluster. Starting with two nodes and then expanding it to three is quite a challenging task. (Figure 8)

Using tpm, though, the gask becomes quite easy. Let's revisit the all-masters installation command, similar to what we saw at the start of this article

./tools/tpm install musketeers \
    --reset \
    --topology=all-masters \
    --home-directory=/opt/continuent/replicator \
    --replication-user=tungsten \
    --replication-password=secret \
    --masters=host1,host2,host3 \
    --master-services=athos,porthos,aramis \
    --start

If we want to add a host 'host4', running a service called 'dartagnan', we just have to modify the above command slightly:

./tools/tpm configure musketeers \
    --reset \
    --topology=all-masters \
    --home-directory=/opt/continuent/replicator \
    --replication-user=tungsten \
    --replication-password=secret \
    --masters=host1,host2,host3,host4 \
    --master-services=athos,porthos,aramis,dartagnan \
    --start

./tools/tpm update

That's all it takes. The update command is almost a repetition of the install command, with the additional components. The same command also restarts the replicators, to get the configuration online.

Image 8 - Extending an all-masters topology

More is coming

The tpm is such a complex tool that exploring it all in one session may be daunting. In addition to installing, you can update the data service, and thanks to its precise syntax, you can deploy the change exactly in the spot where you want it, without moving from the staging directory. We will look at it with more examples soon.

Related posts

• Installing and Administering Tungsten Replicator - Part 1 - basics
• Installing and administering Tungsten Replicator - Part 2 : advanced

Welcome Tungsten Replicator 2.1.0!

Overview

First off, the important news. Tungsten Replicator 2.1.0 was released today.
You can download it and give it a try right now.

Second, I would say that I am quite surprised at how much we have done in this release. The previous release (2.0.7) was in February, which is just a few months ago, and yet it looks like ages when I see the list of improvements, new features and bug fixes in the Release Notes. I did not realized it until I ran my last batch of checks to test the upgrade from the previous release, which I hadn’t run for quite a long time. It’s like when you see a son growing in front of your eyes day by day, and you don’t realize he’s grown a full foot until a distant relative comes visit you. The same happened to me here. I looked at the ./cookbook directory in 2.0.7, and I saw just a handful of commands (most of them now deprecated), and then at 2.1.0, which has about 30 new commands, all nicely categorized and advertised in the embedded documentation. If you are starting today with Tungsten Replicator 2.1.0, you can run

./cookbook/readme

and

./cookbook/help

Upgrade

If you were using Tungsten Replicator before, you need to know how to upgrade. If, by any unfortunate chance, you were not using the Cookbook recipes to run the installation, the method for installing is the following:

• unpack the tarball in a staging directory
• For each node in your deployment:
  
  • stop the replicator
  • run
    

    ./tools/update –release-directory=$PATH_TO_DEPLOYED_TUNGSTEN –host=$NODE

• If your node has more than one service, restart the replicator

If you are using the cookbook, you can run an upgrade using

./cookbook/upgrade

This command will ask for your current topology and then show all the commands that you should run to perform the upgrade, including adapt the cookbook scripts to use the new deployment.

So, What’s New:

The list of goodies is long. All the gory details are in the Release Notes. Here I would like to mention the ones that have impressed me more.

Oracle Extractor Is Open Source

Up to the previous release, you could extract from MySQL and appley to Oracle, all using open source tools. If you wanted to extract from Oracle, you needed a commercial license. Now all the replication layer is completely open source. You can replicate from and to Oracle using Tungsten Replicator 2.1.0 under the terms of the GPL v2. However, you will still have to buy database licenses from Oracle!

Installation and Administration

There is a long list of utilities released inside the ./cookbook directory, which will help you install and maintain the cluster with a few strokes. See References #2 and #3 below. The thing that you should try right away is:

 # edit ./cookbook/COMMON_NODES.sh
 # edit ./cookbook/USER_VALUES.sh
 ./cookbook/validate_cluster

This will tell you if your servers are ready for deployment, without actually deploying anything.

Documentation!

We have hired a stellar professional writer (my former colleague at MySQL AB, well known book writer MC Brown) and the result is that our well intentional but rather unfocused documentation is now shaping up nicely. Among all the things that got explained, Tungsten Replicator has its own getting started section.

Metadata!

Tungsten replication tools now give information using JSON. Here’s a list of commands to try:

trepctl status -json
trepctl services -json -full
trepctl properties | less
thl list -headers -high 100 [-json]

For example:

$ trepctl services -json
[
{
"appliedLatency": "0.81",
"state": "ONLINE",
"role": "slave",
"appliedLastSeqno": "1",
"started": "true",
"serviceType": "local",
"serviceName": "cookbook"
} 
]

$ trepctl properties -filter replicator.service.comments
{
"replicator.service.comments": "false"
}

More Tools

My colleague Linas Virbalas has made the team (and several customers) happy when he created two new tools:

• ddlscan, a Utility to Help Analyze and Migrate Database Schemas
• the rename filter A supercharged filter that can rename mostly any object in a relational database, from schema down to columns.

Linas coded also the above mentioned JSON-based improvements.

MongoDB Installation

It was improved and tested better. It’s a pleasure top see how data from a relational database flow into a rival NoSQL repository as if they belong there! See reference #4 below.

More to Come

What’s listed here is what we have tested and documented. But software development is not a linear process. There is much more boiling in the cauldron, ready to be mixed into the soup of release 2.1.1.

We’re working hard at making filters better. You will see soon the long awaited documentation for them, and a simplified interface.

Another thing that I have tested and worked surprisingly well is the creation of Change Data Capture for MySQL. This is a feature that is usually asked for by Oracle users, but I tried it for MySQL and it allowed me to create shadow tables with the audit trace of their changes. I will write about that as soon as we smooth a few rough edges.

Scripting! This going to be huge. Much of it is already available in the source, but not fully documented or integrated yet. The thing that you will see soon in the open is a series of Ruby libraries (the same used by the very sophisticated Tungsten installation tools) that is exposed for general usage by testers and tool creators. While the main focus of this library is aimed at the commercial tools, there is a significant portion of work that needs to end up in the replicator, and as a result its usability will increase.

What else? I may have forgot something important amid all the excitement. If so, I will amend in my next articles. Happy hacking!

References

1. Tungsten Replicator documentation
2. Installing and Administering Tungsten Replicator - Part 1 - basics
3. Installing and administering Tungsten Replicator - Part 2 : advanced
4. Getting started with replication from MySQL to MongoDB

Getting started with replication from MySQL to MongoDB

As you probably know, Tungsten Replicator can replicate data from MySQL to MongoDB. The installation is relatively simple and, once done, replication works very well. There was a bug in the installation procedure recently, and as I was testing that the breakage has been fixed, I wanted to share the experience of getting started with this replication.

Step 1: install a MySQL server

For this exercise, we will use a MySQL sandbox running MySQL 5.5.31.

We download the binaries from dev.mysql.com and install a sandbox, making sure that it is configured as master, and that it is used row-based-replication.

$ mkdir -p $HOME/opt/mysql
$ cd ~/downloads
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.31-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
$ make_sandbox --export_binaries ~/downloads/mysql-5.5.31-linux2.6-x86_64.tar.gz -- --master
$ echo 'binlog-format=row' >> ~/sandboxes/msb_5_5_31/my.sandbox.cnf

$ ~/sandboxes/msb_5_5_31/use -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {root} ((none)) > grant all on *.* to tungsten identified by 'secret' with grant option;
Query OK, 0 rows affected (0.00 sec)


mysql [localhost] {root} ((none)) > set global binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

The above command will install an instance of MySQL 5.5.31 in the directory $HOME/sandboxes/msb_5_5_31. You can use any other MySQL version. In that case, you should change the DEPLOY.sh file below with the coordinates of your server.

Step2: install a MongoDB server

Get the binaries from MongoDB downloads, and unpack them.

$ cd ~/downloads
$ wget http://fastdl.mongodb.org/linux/mongodb-linux-x86_64-2.4.3.tgz
$ cd
$ tar -xzf ~/downloads/mongodb-linux-x86_64-2.4.3.tgz
$ mv mongodb-linux-x86_64-2.4.3 mongodb

Once you have unpacked the binaries, you can run the server, with a command such as this:

#!/bin/bash
MONGODB_HOME=$HOME/mongodb

if [ ! -d $MONGODB_HOME ]
then
    echo "$MONGODB_HOME not found"
    exit 1
fi

cd $MONGODB_HOME
if [ ! -d $MONGODB_HOME/data ]
then
    mkdir $MONGODB_HOME/data
fi

./bin/mongod \
   --logpath=$MONGODB_HOME/mongodb.log \
   --dbpath=$MONGODB_HOME/data \
   --fork \
   --rest

Now MongoDB should be ready to receive commands. If needed, you can see a tutorial on basic MongoDB operations. For now, it's enough to check that your system is running:

$ cd ~/mongodb
$ ./bin/mongo
MongoDB shell version: 2.4.3
connecting to: test
> show dbs
local 0.078125GB
>

Step 3: install a master replicator using MySQL

As I mentioned above, there was a bug in the installation. The bug was fixed yesterday, and thus you should use a recent build from http://bit.ly/tr20_builds. For this exercise, I am using tungsten-replicator-2.1.0-269.tar.gz.

Of course, you should also configure your host according to the system requirements for Tungsten.

We start by creating a few directories that we need for our deployment.

$ cd 
$ mkdir deploy
$ cd deploy
$ mkdir mysql     # here we install the master replicator
$ mkdir mongodb   # here we install the slave replicator
$ wget https://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.0-snapshots/tungsten-replicator-2.1.0-269.tar.gz
$ tar -xzf tungsten-replicator-2.1.0-269.tar.gz

We now create a defaults script that will be used by both master and slave installer.

$ cat DEPLOY.sh
MYSQL_PORT=5531
MYSQL_SANDBOX_BASE=$HOME/sandboxes/msb_5_5_31
MYSQL_BASEDIR=$HOME/opt/mysql/5.5.31
MYSQL_CONF=$MYSQL_SANDBOX_BASE/my.sandbox.cnf
MYSQL_BINLOG_DIRECTORY=$MYSQL_SANDBOX_BASE/DATA/
DEPLOY_HOME=$HOME/deploy
MYSQL_DEPLOY=$DEPLOY_HOME/mysql
MONGODB_DEPLOY=$DEPLOY_HOME/mongodb
MONGODB_PORT=27017
TUNGSTEN_BINARIES=$DEPLOY_HOME/tungsten-replicator-2.1.0-269
MASTER_THL_PORT=12500
SLAVE_THL_PORT=12600
MASTER_RMI_PORT=11500
SLAVE_RMI_PORT=11600

And finally the master installation script:

$ cat install_master.sh
. ./DEPLOY.sh

cd $TUNGSTEN_BINARIES

export PATH=$MYSQL_BASEDIR/bin:$PATH

./tools/tungsten-installer --master-slave -a \
  --datasource-type=mysql \
  --master-host=127.0.0.1  \
  --datasource-user=tungsten  \
  --datasource-password=secret  \
  --datasource-mysql-conf=$MYSQL_CONF \
  --datasource-log-directory=$MYSQL_BINLOG_DIRECTORY \
  --datasource-port=$MYSQL_PORT \
  --service-name=mongodb \
  --home-directory=$MYSQL_DEPLOY \
  --cluster-hosts=127.0.0.1 \
  --thl-port=$MASTER_THL_PORT \
  --rmi-port=$MASTER_RMI_PORT \
  --java-file-encoding=UTF8 \
  --mysql-use-bytes-for-string=false \
  --mysql-enable-enumtostring=true \
  --mysql-enable-settostring=true \
  --svc-extractor-filters=colnames,pkey \
  --svc-parallelization-type=none --start-and-report

We can run the installation command for the master.

$ ./install_master.sh
INFO  >> 127_0_0_1 >> Getting services list
INFO  >> 127_0_0_1 >> ..
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 1.218
role            : master
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

Step 4: install a slave replicator using MongoDB

Now we have a master database server with a replicator that can send data across for any slave to pick it up. We can install one or more MySQL slaves, but this is not the purpose of this exercise. So we skip any regular slave installation and will install a MongoDB slave. To do so, we create a third script, which will invoke the installer to run a replicator slave service that applies data to MongoDB.

$ cat install_slave.sh
. ./DEPLOY.sh

cd $TUNGSTEN_BINARIES

export PATH=$MYSQL_BASEDIR/bin:$PATH

tools/tungsten-installer --master-slave -a \
  --datasource-type=mongodb \
  --master-host=127.0.0.1  \
  --service-name=mongodb \
  --home-directory=$MONGODB_DEPLOY \
  --cluster-hosts=127.0.0.1 \
  --datasource-port=$MONGODB_PORT \
  --master-thl-port=$MASTER_THL_PORT \
  --thl-port=$SLAVE_THL_PORT \
  --rmi-port=$SLAVE_RMI_PORT \
  --java-file-encoding=UTF8 \
  --skip-validation-check=InstallerMasterSlaveCheck \
  --svc-parallelization-type=none --start-and-report

Compared to MySQL installation, we see that the 'datasource-type' is 'mongodb', and as such it does not need username and password. Let's run it.

$ ./install_slave.sh
WARN  >> 127.0.0.1 >> Currently unable to check for the THL schema in mongodb
INFO  >> 127_0_0_1 >> Getting services list
INFO  >> 127_0_0_1 >> Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 0
appliedLatency  : 49.444
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

We get a warning, which should not worry us, as it is expected. Notice that the high latency reported by the replicator is due to the delay in installing the slave. The only event in the replication pipeline is the 'master online' broadcast, which was replicated after the installation was completed.

If you got to this point, with both the master and slave replicator in the 'ONLINE' state, then you can continue with checking that replication works.

Step 5: check replication

Replication from MySQL (a relational DBMS) to MongoDB (a key-value store) requires some understanding of what we are replicating and what limitations we can face.

In MySQL, we have tables, which are translated to MongoDB collections. What is very important to understand is that Tungsten Replicator DOES NOT REPLICATE DDL EVENTS. Any CREATE/DROP/ALTER events are ignored by the replicator.

However, the replicator is smart enough to convert every table record into a document within the relevant collection. Every insert will generate a document inside MongoDB. Let's see an example:

mysql [localhost] {msandbox} (test) > create table myfirst (id int not null primary key, name char(30), d date);
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into myfirst values (1, 'Harry Potter', '1997-06-30');
Query OK, 1 row affected (0.00 sec)

On the MongoDB side, we will see the corresponding object:

> show dbs
local 0.078125GB
test 0.203125GB
tungsten_mongodb 0.203125GB
> use test
switched to db test
> show collections
myfirst
system.indexes
> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter", "d" : "1997-06-30" }

Not only inserts, but also updates are recognised:

mysql [localhost] {msandbox} (test) > insert into myfirst values (2, 'Harry Potter 2', '1999-06-02');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > update myfirst set name = 'Harry Potter 1' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Which will result in:

> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter 1", "d" : "1997-06-30" }
{ "_id" : ObjectId("51a287633004fd7959a5f5ab"), "id" : "2", "name" : "Harry Potter 2", "d" : "1999-06-02" }

And delete statements:

mysql [localhost] {msandbox} (test) > delete from myfirst  where id =2;
Query OK, 1 row affected (0.00 sec)

#####
> db.myfirst.find()
{ "_id" : ObjectId("51a2867d3004fd7959a5f5aa"), "id" : "1", "name" : "Harry Potter 1", "d" : "1997-06-30" }

This is all fine. But what happens when we insert quite a lot of data? Let's find out. We're going to use the test employees database to see if the system complains.

$ cd ~/data/employees/
$  ~/sandboxes/msb_5_5_31/use < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries

To see if replication is OK, we check both master and slave for their status, and then we count the records in MySQL and MongoDB.

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $MASTER_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 182
appliedLatency  : 1.152
role            : master
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 106
appliedLatency  : 93.469
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

So, we see that the slave is lagging, as the data is hard to digest (about 4 million records). Let's try again after a few seconds:

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 182
appliedLatency  : 89.99
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

Both the master and the slave have reached the same transaction number. The slave has some delay, due to the size of the transactions (which become even bigger because of the RBR). When the size of the transaction is small, the latency becomes quite acceptable:

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $MASTER_RMI_PORT heartbeat

$ ./mongodb/tungsten/tungsten-replicator/bin/trepctl -port $SLAVE_RMI_PORT services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 183
appliedLatency  : 0.423
role            : slave
serviceName     : mongodb
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

As a last operation, let's check the numbers of the biggest table in the employees database:

$ ~/sandboxes/msb_5_5_31/use  employees
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (employees) > show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.22 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.28 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.25 sec)

mysql [localhost] {msandbox} (employees) > select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.83 sec)

####

> use employees
switched to db employees
> show collections
departments
dept_emp
dept_manager
employees
salaries
system.indexes
titles
> db.employees.count()
300024
> db.dept_emp.count()
331603
> db.titles.count()
443308
> db.salaries.count()
2844047
>

This proves that replication works, and even large amount of data are replicated reasonably fast.

Here's the official documentation about MySQL to MongoDB replication: https://docs.continuent.com/wiki/display/TEDOC/Replicating+from+MySQL+to+MongoDB. There you will find more details on how to install and what to expect. Happy hacking!

Installing and administering Tungsten Replicator - Part 2 : advanced

Switching roles

To get a taste of the power of Tungsten Replicator, we will show how to switch roles. This is a controlled operation (as opposed to fail-over), where we can decide when to switch and which nodes are involved.

In our topology, host1 is the master, and we have three slaves. We can either ask for a switch and let the script select the first available slave, or tell the script which slave should be promoted. The script will show us the steps needed to perform the operation.

IMPORTANT! Please note that this operation is not risk free. Tungsten replicator is a simple replication system, not a complete management tool like Continuent Tungsten. WIth the replicator, you must make sure that the applications have stopped writing to the master before starting the switch, and then you should address the application to the new master when the operation is done.

$ cookbook/switch host2
# Determining current roles
host1 master
host2 slave
host3 slave
host4 slave
# Will promote host2 to be the new master server
# Waiting for slaves to catch up and pausing replication
trepctl -host host2 wait -applied 5382
trepctl -host host2 offline
trepctl -host host3 wait -applied 5382
trepctl -host host3 offline
trepctl -host host4 wait -applied 5382
trepctl -host host4 offline
trepctl -host host1 offline
# Reconfiguring server roles and restarting replication
trepctl -host host2 setrole -role master
trepctl -host host2 online
trepctl -host host1 setrole -role slave -uri thl://host2:2112
trepctl -host host1 online
trepctl -host host3 setrole -role slave -uri thl://host2:2112
trepctl -host host3 online
trepctl -host host4 setrole -role slave -uri thl://host2:2112
trepctl -host host4 online
--------------------------------------------------------------------------------------
Topology: 'MASTER_SLAVE'
--------------------------------------------------------------------------------------
# node host1
cookbook  [slave]   seqno:      5,384  - latency:   2.530 - ONLINE

# node host2
cookbook  [master]  seqno:      5,384  - latency:   2.446 - ONLINE

# node host3
cookbook  [slave]   seqno:      5,384  - latency:   2.595 - ONLINE

# node host4
cookbook  [slave]   seqno:      5,384  - latency:   2.537 - ONLINE

As you can see from the listing above, The script displays the steps for the switch, using trepctl as a centralized tool.

Under load

After the simple installation in Part 1, we saw that we can test the flow of replication using 'cookbook/test_cluster'. That's a very simple set of operations that merely checks if replication is working. If we want to perform more serious tests, we should apply a demanding load to the replication system.

If you don't have applications that can exercise the servers to your liking, you should be pleased to know that Tungsten Replicator ships with a built-in application for data loading and benchmarking. Inside the expanded tarball, there is a directory named bristlecone, containing the software for such testing tools. There is a detailed set of instructions under './bristlecone/doc'. For the impatient, there is a cookbook recipe that starts a reasonable load with a single command:

$ cookbook/load_data start
# Determining current roles
# Evaluator started with pid 28370
# Evaluator details are available at /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.job
# Evaluator output can be monitored at /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.log

$ cat /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.job
Task started at   : Sun Apr  7 18:20:00 2013
Task started from : /home/tungsten/tinstall/current
Executable        : /home/tungsten/installs/cookbook/tungsten/bristlecone/bin/evaluator.sh
Process ID        : 28370
Using             : /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.xml
Process id        : /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.pid
Log               : /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.log
Database          : host1
Table prefix      : tbl
Host              : host1
Port              : 3306
User              : tungsten
Test duration     : 3600

$  tail /home/tungsten/installs/cookbook/tungsten/load/host1/evaluator.log
18:22:18,672 INFO  1365351738672 10/10 5035.0 ops/sec 0 ms/op 28380 rows/select 41 updates 54 deletes 166 inserts
18:22:20,693 INFO  1365351740693 10/10 4890.0 ops/sec 0 ms/op 26746 rows/select 57 updates 37 deletes 144 inserts
18:22:22,697 INFO  1365351742697 10/10 4986.0 ops/sec 0 ms/op 28183 rows/select 59 updates 46 deletes 162 inserts
18:22:24,716 INFO  1365351744716 10/10 5208.0 ops/sec 0 ms/op 29067 rows/select 51 updates 51 deletes 171 inserts
18:22:26,736 INFO  1365351746736 10/10 4856.0 ops/sec 0 ms/op 27695 rows/select 46 updates 68 deletes 141 inserts
18:22:28,739 INFO  1365351748739 10/10 5022.0 ops/sec 0 ms/op 28269 rows/select 51 updates 58 deletes 145 inserts
18:22:30,758 INFO  1365351750758 10/10 4893.0 ops/sec 0 ms/op 28484 rows/select 47 updates 50 deletes 165 inserts
18:22:32,777 INFO  1365351752777 10/10 4501.0 ops/sec 0 ms/op 26481 rows/select 42 updates 52 deletes 130 inserts
18:22:34,781 INFO  1365351754781 10/10 5057.0 ops/sec 0 ms/op 30450 rows/select 58 updates 53 deletes 157 inserts
18:22:36,801 INFO  1365351756801 10/10 5087.0 ops/sec 0 ms/op 30845 rows/select 55 updates 56 deletes 156 inserts

What happens here?

The evaluator process is started using a file named 'evaluator.xml,' which is generated dynamically. The cookbook recipe detects which is the current master in the replication system and directs the operations there (in our case, it's 'host1'). The same task takes note of the process ID, which will be used to stop the evaluator when done, and the output is sent to a file, where you can look at it if needed.

Looking at evaluator.log, you can see that there are quite a lot of operations going on. Most of them are read queries, as the application was designed to solicit a database server as much as possible. Nonetheless, there are quite a lot of update operations, as a call to 'show_cluster' can confirm.

$ cookbook/show_cluster
--------------------------------------------------------------------------------------
Topology: 'MASTER_SLAVE'
--------------------------------------------------------------------------------------
# node host1
cookbook  [master]  seqno:     30,292  - latency:   0.566 - ONLINE

# node host2
cookbook  [slave]   seqno:     30,277  - latency:   0.531 - ONLINE

# node host3
cookbook  [slave]   seqno:     30,269  - latency:   0.511 - ONLINE

# node host4
cookbook  [slave]   seqno:     30,287  - latency:   0.550 - ONLINE

The load will continue for one hour (unless you defined a different duration). SHould you want to stop it before that period, you can run:

$ cookbook/load_data stop
# Determining current roles
# Stopping Evaluator at pid 28370

One important piece of information about this load application is that it looks for the masters in your cluster, and starts a load in every master. This is useful if you want to test a multi-master topology, as the ones we will see in another article.

If the default behavior of load_data is not what you expect, you can further customize the load by fine tuning the application launcher. First, you run 'load_data' with the print option:

$ cookbook/load_data print
# Determining current roles
$HOME/installs/cookbook/tungsten/bristlecone/bin/concurrent_evaluator.pl \
    --deletes=1 \
    --updates=1 \
    --inserts=3 \
    --test-duration=3600 \
    --host=host1 \
    --port=3306 \
    -u tungsten \
    -p secret  \
    --continuent-root=/home/tungsten/installs/cookbook \
    -d host1 \
    -s /home/tungsten/installs/cookbook/tungsten/load/host1  start

Then, you can copy and paste the resulting command, and eventually run the concurrent_evaluator script with your additions.

There are many options available. The manual is embedded in the application itself:

$ ./bristlecone/bin/concurrent_evaluator.pl --manual

An important option that we can use is --instances=N. This option will launch concurrently the evaluator N times, each time using a different schema. We will use this option to test parallel replication.

Backup

I am not going to stress here how important backups are. I assume (perhaps foolishly) that everyone reading this article knows why. Instead, I want to show how Tungsten Replicator supports backup and restore as integrated methods.

When you install Tungsten, you can add options to select a backup method and fine tune its behavior.

$ ./tools/tungsten-installer --help-master-slave -a |grep backup
--backup-directory            Permanent backup storage directory [$TUNGSTEN_HOME/backups]
                              This directory should be accessible by every replicator to ensure simple operation of backup and restore.
--backup-method               Database backup method (none|mysqldump|xtrabackup|script) [xtrabackup-full]
                              Tungsten integrates with a variety of backup mechanisms. We strongly recommend you configure one of these to help with provisioning servers. Please consult the Tungsten
                              Replicator Guide for more information on backup configuration.
--backup-dump-directory       Backup temporary dump directory [/tmp]
--backup-retention            Number of backups to retain [3]
--backup-script               What is the path to the backup script
--backup-command-prefix       Use sudo when running the backup script? [false]
--backup-online               Does the backup script support backing up a datasource while it is ONLINE [false]

First off, the default directory for backups is under your installation directory ($TUNGSTEN_HOME/backups). If you want to take backups through Tungsten, you must make sure that there is enough storage in that path to hold at least one backup. Tungsten will keep up to three backups in that directory, but you can define this action differently.

Second, the default backup method is 'mysqldump,' not because it is recommended, but because it is widely available. As you probably know, though, if your database is more than a few dozen GB, mysqldump is not an adequate method.

Tungsten Replicator provides support for xtrabackup. If xtrabackup is installed in your servers, you can define it as your default backup method. When you are installing a new cluster, you can do this:

$ export MORE_OPTIONS='-a --backupmethod=xtrabackup --backup-command-prefix=true'
$ cookbook/install_master_slave

If you have just installed and need to reconfigure, you can call 'configure_service' to accomplish the task:

$ cookbook/configure_service -U -a --backup-method=xtrabackup --backup-command-prefix=true cookbook

(Where 'cookbook' is the service name). VERY IMPORTANT: configure_service acts on a single host, and by default it acts on the current host, unless you say otherwise. For example:

$ cookbook/configure_service -U --host=host2 -a --backup-method=xtrabackup --backup-command-prefix=true cookbook

You will have to restart the replicator in node 'host2' for the changes to take effect.

$ ssh host2 "cd $TUNGSTEN_BASE/tungsten/ ; ./cookbook/replicator restart"

Using the backup is quite easy. You only need to call 'trepctl', indicate in which host you want to take a backup, and Tungsten will do the rest.

$ cookbook/trepctl -host host3 backup
Backup completed successfully; URI=storage://file-system/store-0000000001.properties

$ cookbook/trepctl -host host2 backup
Backup completed successfully; URI=storage://file-system/store-0000000001.properties

Apparently, we have two backups with the same contents, taken from two different nodes. However, since we have changed the backup method for host2, we will have a mysqldump small file for host3, and a rather larger xtrabackup file for host2. Again, the cookbook has a method that shows the backups that are available in all the nodes:

$ ./cookbook/backups
   backup-agent : (service: cookbook) mysqldump
     backup-dir : (service: cookbook) /home/tungsten/installs/cookbook/backups/cookbook
# [node: host1] 0 files found
# [node: host2] 3 files found
++ /home/tungsten/installs/cookbook/backups/cookbook
total 2.4G
-rw-r--r-- 1 tungsten tungsten   72 Apr  7 21:52 storage.index
-rw-r--r-- 1 tungsten tungsten 2.4G Apr  7 21:52 store-0000000001-full_xtrabackup_2013-04-07_21-50_59.tar
-rw-r--r-- 1 tungsten tungsten  323 Apr  7 21:52 store-0000000001.properties
drwxr-xr-x 2 tungsten tungsten 4.0K Apr  7 21:52 xtrabackup

# [node: host3] 3 files found
++ /home/tungsten/installs/cookbook/backups/cookbook
total 6.3M
-rw-r--r-- 1 tungsten tungsten   72 Apr  7 21:50 storage.index
-rw-r--r-- 1 tungsten tungsten 6.3M Apr  7 21:50 store-0000000001-mysqldump_2013-04-07_21-50_28.sql.gz
-rw-r--r-- 1 tungsten tungsten  315 Apr  7 21:50 store-0000000001.properties

# [node: host4] 0 files found

WARNING: This example was here only to show how to change the backup method. It is NOT recommended to have mixed methods for backups in different nodes. Unless you have a specific need, and understand the consequence of this choice, you should have the same backup method everywhere.

Restore

A backup is only good if you can use to restore your data. Using the same method shown to take a backup, you can restore your data. For this example, let's use mysqldump in all nodes (just because it's quicker), and show the operations for a backup and restore.

First, we take a backup in node 'host3', and then we will restore the data in 'host2'.

$ cookbook/trepctl -host host3 backup
Backup completed successfully; URI=storage://file-system/store-0000000001.properties

$ cookbook/backups
   backup-agent : (service: cookbook) mysqldump
     backup-dir : (service: cookbook) /home/tungsten/installs/cookbook/backups/cookbook
# [node: host1] 0 files found
# [node: host2] 0 files found
# [node: host3] 3 files found
++ /home/tungsten/installs/cookbook/backups/cookbook
total 6.2M
-rw-r--r-- 1 tungsten tungsten   72 Apr  7 22:05 storage.index
-rw-r--r-- 1 tungsten tungsten 6.1M Apr  7 22:05 store-0000000001-mysqldump_2013-04-07_22-05_43.sql.gz
-rw-r--r-- 1 tungsten tungsten  315 Apr  7 22:05 store-0000000001.properties

# [node: host4] 0 files found

Now, we have the backup files in host3, but we have an issue in host2, and we need to take a restore there. Assuming that the database server is unusable (this is usually the case when we must take a restore), we have the unpleasant situation where the backups are in one node, and we need to use in another. In a well organized environment, we would have a shared storage for the backup directory, and thus we could just move ahead and perform our restore. In this case, though, we have no such luxury. Then, we use yet another feature of the cookbook:

$ cookbook/copy_backup
syntax: copy_backup SERVICE SOURCE_NODE DESTINATION_NODE

$ cookbook/copy_backup cookbook host3 host2
# No message = success

$ cookbook/backups
   backup-agent : (service: cookbook) mysqldump
     backup-dir : (service: cookbook) /home/tungsten/installs/cookbook/backups/cookbook
# [node: host1] 0 files found
# [node: host2] 3 files found
++ /home/tungsten/installs/cookbook/backups/cookbook
total 6.2M
-rw-r--r-- 1 tungsten tungsten   72 Apr  7 22:05 storage.index
-rw-r--r-- 1 tungsten tungsten 6.1M Apr  7 22:05 store-0000000001-mysqldump_2013-04-07_22-05_43.sql.gz
-rw-r--r-- 1 tungsten tungsten  315 Apr  7 22:05 store-0000000001.properties

# [node: host3] 3 files found
++ /home/tungsten/installs/cookbook/backups/cookbook
total 6.2M
-rw-r--r-- 1 tungsten tungsten   72 Apr  7 22:05 storage.index
-rw-r--r-- 1 tungsten tungsten 6.1M Apr  7 22:05 store-0000000001-mysqldump_2013-04-07_22-05_43.sql.gz
-rw-r--r-- 1 tungsten tungsten  315 Apr  7 22:05 store-0000000001.properties

# [node: host4] 0 files found

The 'copy_backup' command has copied the files from one host to another, and now we are ready to perform a restore in host2.

$ cookbook/trepctl -host host2 restore
Operation failed: Restore operation failed: Operation irrelevant in current state

Hmm. Probably not the friendliest of error messages. What this scoundrel means is that it can't perform a restore when the replicator is online.

$ cookbook/trepctl -host host2 offline
$ cookbook/trepctl -host host2 restore
Restore completed successfully

$ cookbook/trepctl -host host2 services
Processing services command...
NAME              VALUE
----              -----
appliedLastSeqno: 17955
appliedLatency  : 0.407
role            : slave
serviceName     : cookbook
serviceType     : local
started         : true
state           : ONLINE
Finished services command...

The restore operation was successful. We could have used xtrabackup just as well. THe only difference is that the operation takes way longer.

Parallel replication

Slave lagging is a common occurrence in MySQL replication. Most of the time, the reason for this problem is that while the master updates data using many threads concurrently, the slave applies the replication stream using a single thread. In Tungsten there is a built-in feature that applies changes in parallel, when the updates are happening in separate schemas. For database servers that are sharded by database or for the ones the serve multi-tenancy application, this is an ideal case. It is likely that the action happens in several schemas at once, and thus Tungsten can parallelize the changes successfully. Notice, however, that if you are running operations using a single schema, parallel replication won't give you any relief. Also, the operations must be really independent from each other. If a schema has foreign keys that reference to another schema, or if a transaction mixes data from two or more schemas, Tungsten will stop parallelizing and resume working in single thread until the end of the unclean operation, resulting in an overall decrease of performance, instead of increase.

To activate parallel replication, you need to enable two options:

• --channels=N where you indicate how many parallel threads you want to establish. You should indicate as many channels as the number of schemas where you are operating. Some benchmarks will help you find the limits. Defining too many schemas will eventually exhaust the system resources. If the number of schemas is larger than the channels, Tungsten will use the channels in a round-robin fashion.
• --svc-parallelization-type=disk: This option will activate a fast queue-creation algorithm that acts directly to the THL files. Contrary to common perception, where one would believe that in-memory queues are faster, this method is very efficient and less likely to exhaust system resources.

If you want to install all the servers with parallel replication, you can do this:

$ export MORE_OPTIONS='-a --channels=5 --svc-parallelization-type=disk'
$ cookbook/install_master_slave

If you need parallel replication only on one particular slave service, you can enable parallel replication there, using 'configure_service', same as we have seen before for the backup-method.

In this example, we're going to use the second method

$ cookbook/configure_service -U -a --host=host4 --channels=5 --svc-parallelization-type=disk cookbook
WARN  >> host4 >> THL schema tungsten_cookbook already exists at tungsten@host4:3306 (WITH PASSWORD)
NOTE >> host4 >> Deployment finished

$ cookbook/replicator restart
Stopping Tungsten Replicator Service...
Stopped Tungsten Replicator Service.
Starting Tungsten Replicator Service...

Now parallel replication is enabled. But how do we make sure that the service has indeed been enhanced?

The quickest method is to check the Tungsten service schema. Every replicator service creates a database schema named 'tungsten_$SERVICE_NAME', where it stores the current replication status. For example, in our default system, where the only replication service is called 'cookbook', we will find a schema named 'tungsten_cookbook'. The table that we want to inspect is one named 'trep_commit_seqno', where we store the global transaction ID, the schema where the transaction was applied, the data origin, and the time stamps at extraction and apply time. What is relevant in this table is that there will be one record for each channel that we have enabled. Thus, in host2 and host3 there will be only one line, while in host4 we should find 5 lines.

There is one useful recipe to get this result at once:

$ cookbook/query_all_nodes 'select count(*) from tungsten_cookbook.trep_commit_seqno'
+----------+
| count(*) |
+----------+
|        1 |
+----------+
+----------+
| count(*) |
+----------+
|        1 |
+----------+
+----------+
| count(*) |
+----------+
|        1 |
+----------+
+----------+
| count(*) |
+----------+
|        5 |
+----------+

Right! So we have 5 channels. Before inspecting what is going on in these channels, let's apply some load. You may recall that our load_data script can show you a command that we can customize for our purpose.

$ cookbook/load_data print
/home/tungsten/installs/cookbook/tungsten/bristlecone/bin/concurrent_evaluator.pl \
    --deletes=1 \
    --updates=1 \
    --inserts=3 \
    --test-duration=3600 \
    --host=host1 \
    --port=3306 \
    -u tungsten \
    -p secret  \
    --continuent-root=/home/tungsten/installs/cookbook \
    -d host1 \
    -s /home/tungsten/installs/cookbook/tungsten/load/host1 start

We just copy-and-paste this command, adding --instances=5 at the end, and we get 5 messages indicating that an evaluator was started. Let's see:

$ cookbook/query_node host4 'show schemas'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| host11             |
| host12             |
| host13             |
| host14             |
| host15             |
| mysql              |
| test               |
| tungsten_cookbook  |
+--------------------+

Since we indicated that the database was to be named 'host1' and we have asked for 5 instances, the evaluator has created host11. host12, and so on.

Now that there is some action, we can have a look at our replication. Rather than querying the database directly, asking for the contents of trep_commit_seqno, we use another cookbook recipe:

$ cookbook/tungsten_service all
# node: host1 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 324246 | host1     |               1 | host12   | 2013-04-07 23:02:16 | 2013-04-07 23:02:15 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host2 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 324383 | host1     |               0 | host13   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host3 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 324549 | host1     |               0 | host13   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host4 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 324740 | host1     |               0 | host11   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
| 324736 | host1     |               0 | host12   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
| 324739 | host1     |               0 | host13   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
| 324737 | host1     |               0 | host14   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
| 324735 | host1     |               0 | host15   | 2013-04-07 23:02:16 | 2013-04-07 23:02:16 |
+--------+-----------+-----------------+----------+---------------------+---------------------+

Here you see that host1 has only one channel: it is the master, and it must serialize according to the binary log. Slaves host2 and host3 have only one channel, because we have enabled parallel replication only in host4. And finally we see that in host4 there are 5 channels, each showing a different shard_id (= database schema), with its own transaction ID being applied. This shows that replication is working.

Tungsten Replicator has, however, several tools that help monitoring parallel replication:

$ cookbook/trepctl -host host4 status -name stores
Processing status command (stores)...
NAME                      VALUE
----                      -----
activeSeqno             : 475395
doChecksum              : false
flushIntervalMillis     : 0
fsyncOnFlush            : false
logConnectionTimeout    : 28800
logDir                  : /home/tungsten/installs/cookbook/thl/cookbook
logFileRetainMillis     : 604800000
logFileSize             : 100000000
maximumStoredSeqNo      : 475449
minimumStoredSeqNo      : 0
name                    : thl
readOnly                : false
storeClass              : com.continuent.tungsten.replicator.thl.THL
timeoutMillis           : 2147483647
NAME                      VALUE
----                      -----
criticalPartition       : -1
discardCount            : 0
estimatedOfflineInterval: 0.0
eventCount              : 457459
headSeqno               : 475415
intervalGuard           : AtomicIntervalGuard (array is empty)
maxDelayInterval        : 60
maxOfflineInterval      : 5
maxSize                 : 10
name                    : parallel-queue
queues                  : 5
serializationCount      : 0
serialized              : false
stopRequested           : false
store.0                 : THLParallelReadTask task_id=0 thread_name=store-thl-0 hi_seqno=475415 lo_seqno=17957 read=457459 accepted=93357 discarded=364102 events=0
store.1                 : THLParallelReadTask task_id=1 thread_name=store-thl-1 hi_seqno=475415 lo_seqno=17957 read=457459 accepted=92567 discarded=364892 events=0
store.2                 : THLParallelReadTask task_id=2 thread_name=store-thl-2 hi_seqno=475415 lo_seqno=17957 read=457459 accepted=91197 discarded=366262 events=0
store.3                 : THLParallelReadTask task_id=3 thread_name=store-thl-3 hi_seqno=475415 lo_seqno=17957 read=457459 accepted=90492 discarded=366967 events=0
store.4                 : THLParallelReadTask task_id=4 thread_name=store-thl-4 hi_seqno=475415 lo_seqno=17957 read=457459 accepted=89846 discarded=367613 events=0
storeClass              : com.continuent.tungsten.replicator.thl.THLParallelQueue
syncInterval            : 10000
Finished status command (stores)...

This command shows the status of parallel replication in each channels. Notable information in this screen:

• eventCount is the number of transaction being processed
• serializationCount:0 means that all events have been parallelized, and there was no need to serialize any.
• 'read' ... 'accepted' ... 'discarded' are the operation in the disk queue. Each channel parses all the events, and queues only the ones that belong in its shard.

$ cookbook/trepctl -host host3 status -name shards
Processing status command (shards)...
 ...
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000006:0000000169567337;0
appliedLastSeqno  : 660707
appliedLatency    : 1.314
eventCount        : 130325
shardId           : host11
stage             : q-to-dbms
NAME                VALUE
----                -----
appliedLastEventId: mysql-bin.000006:0000000169566006;0
appliedLastSeqno  : 660702
appliedLatency    : 1.312
eventCount        : 129747
shardId           : host12
stage             : q-to-dbms
 ...

This command (only a portion reported here) displays the status of each shard, showing for each one which event, transaction ID and event count were recorded.

There should be much more to mention about the monitoring tools, but for now I want just to mention a last important point. When the replicator goes offline, parallel replication stops, and the replication operations are consolidated into a single thread. This makes sure that replication can later resume using a single thread, or it can be safely handed over to native MySQL replication. This behavior also makes sure that a slave can be safely promoted to master. A switch operation requires that the slave service be offline before being reconfigured to become a master. When the replicator goes offline, the N channels become 1.

$ ./cookbook/trepctl offline
$ cookbook/tungsten_service all
# node: host1 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 769652 | host1     |               0 | host12   | 2013-04-07 23:18:07 | 2013-04-07 23:18:07 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host2 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 769699 | host1     |               0 | host13   | 2013-04-07 23:18:08 | 2013-04-07 23:18:08 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host3 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 769866 | host1     |               0 | host15   | 2013-04-07 23:18:08 | 2013-04-07 23:18:08 |
+--------+-----------+-----------------+----------+---------------------+---------------------+
# node: host4 - service: cookbook
+--------+-----------+-----------------+----------+---------------------+---------------------+
| seqno  | source_id | applied_latency | shard_id | update_timestamp    | extract_timestamp   |
+--------+-----------+-----------------+----------+---------------------+---------------------+
| 767064 | host1     |               0 | host15   | 2013-04-07 23:18:01 | 2013-04-07 23:18:01 |
+--------+-----------+-----------------+----------+---------------------+---------------------+

If we put it back online, we see again the channels expanding.

Further info:

• Project home: http://tungsten-replicator.org
• Discussion group: (a Google Group discussion on Tungsten Replicator)