Performing schema changes in a Tungsten environment

Posted in: MySQL, Open Source, Technical Track

Schema changes in a Tungsten environment

This post will discuss the alternatives for performing schema changes in a Tungsten environment, which uses Tungsten replicator/cluster instead of traditional MySQL replication.

Option 1: MySQL native DDL

If you have MySQL >= 5.6 you can use the built-in online DDL feature on the master and let it propagate to the slaves.

The problem with this alternative is the same as that of traditional mysql replication: it will serialize on the slaves, so if you have a 6h long alter, your slave will not process any updates for ~6h until the alter is done there.

An example would be:

ALTER TABLE test_table ADD COLUMN foo VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

If you have MySQL < 5.6 you are stuck with normal blocking DDL.

If your application can deal with it, you could also run the DDL on a slave first, then promote it as master, finally alter the old master (and any other slaves) doing kind of a shell game.

Option 2: pt-online-schema-change

pt-online-schema-change works by creating triggers and copying the rows of the original table in chunks. With tungsten this approach works, but you need to use either ROW or STATEMENT based binlogs (MIXED doesn’t work).

There is a caveat with ROW, as tungsten converts the row events to SQL statements, it will cause triggers to be fired twice on the slaves. The way to overcome this is to use the tungsten plugin for percona toolkit.

The plugin will recreate the pt-osc triggers in a tungsten-safe manner, and also as a bonus provide you with a way to throttle the process. pt-osc relies on native MySQL replication features for throttling so that won’t work here.

Here are some instructions to install the tungsten plugin:

1. get the needed packages

sudo yum -y install gcc perl-YAML perl-JSON-XS
sudo cpan Test::More
sudo cpan JSON::XS

2. get the plugin

wget https://raw.githubusercontent.com/grypyrg/percona-toolkit-plugin-tungsten-replicator/master/pt-plugin-tungsten_replicator.pl --no-check-certificate

3. complete the plugin config by editing the pt-plugin-tungsten_replicator.pl file:

# trepctl command to run
my $trepctl="/opt/tungsten/installs/cookbook/tungsten/tungsten-replicator/bin/trepctl";

# what tungsten replicator service to check
my $service="east";

# what user does tungsten replicator use to perform the writes?
my $tungstenusername = 'tungsten';

4. Create the table with slave lookup info:

CREATE DATABASE percona;
CREATE TABLE `percona`.`dsn` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`));

5. Populate the table with current slaves:

At this point you are ready to test your schema change as follows:

time pt-online-schema-change --recursion-method=dsn=D=percona,t=dsns \
--plugin=/usr/local/bin/pt-plugin-tungsten_replicator.pl \
--check-interval=5 \
--max-lag=180 \
--alter="ADD COLUMN foo INT" \
D=test_db,t=test_table

This will check every 5 sec and ensure tungsten replicator max lag is 180 secs.

When you are ready, just add the –execute flag and rerun.

Option 3: gh-ost

The new and shiny gh-ost tool also has Tungsten support (huge thanks to Shlomi for this).

For me, it is quickly becoming the tool of choice for online schema changes.

There are several ways to run it, and I encourage you to read the official doc and also this post by Valerie Parham-Thompson.

To install gh-ost, simply download latest binary from here

As an example, the following will run the tool with the less possible load on the master, by reading the changes from the slave_host as follows:

gh-ost \
--assume-master-host=tungsten_master \
--tungsten \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--max-lag-millis=180000 \
--user="your_user" \
--password="your_pass" \
--host=slave_host \
--database="database" \
--table="test_table" \
--alter="your_alter_here" \
--cut-over=default \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--initially-drop-ghost-table \
--initially-drop-old-table \
--verbose

Same as before, when you are satisfied, just add the –execute flag and rerun. Just make sure you have tungsten configured with –log-slave-updates parameter, otherwise gh-ost will hang forever waiting to find its tables on the slave’s binlog.

email

Interested in working with Ivan? Schedule a tech call.

Lead Database Consultant

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *