Speed up your backups by compressing and posting in parallel

Its pretty typical to have data stores that are several hundreds of GB’s in size and need to be posted offsite.

At weheartit our database is ~1/2 TB uncompressed and the old method of compressing and posting to S3 took 9 hours and rarely completed.

I was able to speed up this process and now it completes in < 1 hour.
53 minutes in fact.

For compression I used pbzip2 instead of gzip.

This is how I am using it along with percona’s xtrabackup.

innobackupex --user root --password $PASSWORD --slave-info --safe-slave-backup --stream=tar ./ | pbzip2 -f -p40 -c  > $BACKUPDIR/$FILENAME

The backup and compression only takes 32 minutes and compresses it from 432GB to 180GB

Next comes speeding up the transfer to S3.

In November of 2010 amazon added this feature to S3 but for some reason this functionality hasn’t been added to s3cmd.
Instead I am using s3 multipart upload
Thanks David Arther!

This is how I am using it.

/usr/local/bin/s3-mp-upload.py --num-processes 40 -s 250 $BACKUPDIR/$FILENAME $BUCKET/$(hostname)/$TIME/$FILENAME

It only takes 20 minutes to copy 180GB over the internet!
That is crazy fast.
In both cases you can play around with the number of threads for both pbzip2 and s3 multi part upload, the threads I use work for me but that depends on the size of your system.

mysql multi threaded slaves (mts) slower than single threaded

I work @ weheartit.com where we rely on MySQL.
I’ve seen very little published about mts and nothing from outside a lab so I decided to test it out.
The results weren’t good.

Our main database group has 4 active schema, is running 5.6.12 and when a slave gets our of sync its takes a while to catch back up to the master.

One of the most interesting features for MySQL 5.6 is multi threaded slaves.

Without this feature the sync speed is limited to a single thread running on a single core.

Before I start let me clear up this point about mts which is that this feature will only help if you are running more than 1 schema per host as each thread can only process one schema at a time.

That being said I went and upgraded one of my slaves to 5.6.12, restored an xtrabackup to it.

Then I added the following lines to the my.cnf and ran start slave.

binlog-format=STATEMENT
 slave_parallel_workers = 4
 master_info_repository = TABLE
 relay_log_info_repository = TABLE

Now I can just run show slave status\G and watch it catching up.

However once it was caught up I stopped replication on the mts host and single thread slave for 20 minutes.
Then I started the slaves and it turns out that the single threaded slave caught up faster.
What?
to eliminate disk and RAID configs( they were the same as I could tell) this next time I only stopped the sql_thread for 20 minutes.
Same results, the slave running mts is actually slower.

Looks like there is reason when you search for this topic the only posts are from people using it in a lab is because although it appears to function it doesn’t delivery what ultimately need to which is faster replication syncs.

I’ll keep watching the mysql releases and hope this gets fixed soon.

lessons learn while devops@posterous

I’m a proud former employee of Posterous and worked there for the 10 months prior to the twitter acquisition.
Although it was a short time  we experienced a lot of growth and it was very intense with a lot of changes in user behavior, product and infrastructure.
Like everything in life when you look back you can always find better ways to do things.
Its been about six months since I left and below are a few of the lessons that I’ve learned and will take with me to my next gig.

MySQL:

– Install a gui tool to help manage the DB’s. Yeah gui’s sound lame but they put a lot of valuable information together in one place and don’t cost you anything.

– Always have a spare slave that you can use to test out new configs, place in service for a while, iterate.

– Go to every MySQL meet up possible, they are a huge source of information and you will walk away from everyone with one or more new trick up your sleeve.

FIGHT SPRAWL:

– Every time you introduce a new technology make it a requirement to have it replace an existing technology in addition to supporting a new feature.
In most cases this isn’t a stretch as a lot of technologies are similar to each other (redis/memcached), (riak/mongodb) etc……

ADMIT YOUR SHORT COMINGS, FOCUS ON THE POSITIVE:

Posterous was rails shop with an abundance of really skilled developers which meant we moved at a rapid pace, multiple deployments a day….we were constantly in a state of change.
Working in an environment like that has advantages and disadvantages.
The disadvantages are that change equals risk.
Advantages are that we were able to push changes to the site soon after the code change, which means if something when wrong the developer didn’t have to context switch to fix the problem.
We spent a lot of time addressing the negative side of rapid development by attempting to introduce a QA process meant to slow things down and prevent mistakes.
Now that I look back I think we should have focused on the positive side of rapid change by speeding up the deployment process and pushing code even more often.
In a perfect world you have a automated QA test that runs in 1 minute and has a 100% coverage….or maybe thats not the perfect world but fantasy island I’m thinking of.

FORCE YOURSELF TO PLAN LONG:

In a rapidly moving start-up with a burn rate it seems like a waste of time to plan long but when it comes to infrastructure its absolutely necessary.
I can go to most web companies and when they talk about their infrastructure there are always one off services that they considered “legacy”.
Legacy is code word for we rolled it out without thinking more than a month out.
Once a month take the key people offsite for a few hours to white board your current infrastructure and what it would look like you got to design it from scratch….that is your long term plan. Now when choosing a new technology you already know how it will fit in long term.

MySQL replication and failover with tungsten-replicator

Having administered MySQL in production for the last 7 years I know how painful it is when a master goes down or when replication breaks.Its also annoying when you rebuild a slave and it takes a really long time for replication to catch up.I decided to give tungsten-replication a look as it claims to be high performance and handles promoting a slave to master.

My setup:

—Two rackspace cloud servers running Ubuntu 12.04

—2GB RAM

—4 cores

—Percona server 5.5

—tungsten-replicator 2.0.5 (free version)

Installation

It took me a while( ~4 hours) to satisfy all of the prerequisites for the OS and MySQL.This wasn’t helped by the mostly horrible documentation. All of the information was there but it was laid out all over the place and took several attempts to find all the pieces.It would have been a huge help to have a script that you can run to check your prerequisites such as ruby version, java version etc….Once I satisfied all the prerequisites it was time to move on to the config

Configuration

The good news here is that tungsten-replication comes with a scripted installation designed to install and configure all of your nodes with one command.The bad news once again is that the documentation is scattered all over the place and really incomplete.Also a really annoying problem is getting to the help output with the commands.Some require —help, some help and others have nothing at all.In the open source version there are 2 types of configuration.1. master-slave2. direct which supports multi-master

master-slave

To get master slave up I downloaded the release:

wget http://code.google.com/p/tungsten-replicator/downloads/detail?name=tungsten-replicator-2.0.5.tar.gz&can=2&q=

untarred it:

tar xzvf tungsten-replicator-2.0.5.tar.gz

cd to tungsten-replicator-2.0.5 and ran the following command:

./tools/tungsten-installer -v       --master-slave       --master-host=198.201.208.173       --datasource-user=tungsten       --datasource-password=tungsten       --datasource-mysql-conf=/etc/mysql/my.cnf       --service-name=gabriel       --home-directory=/opt/tungsten       --cluster-hosts=198.201.208.173,198.201.206.221       --start-and-report

This should install tungsten-replicator to /opt/tungsten start the service on both nodes and report back with its status.To check create a database on the master, it should show up on the slave.

Replication Performance

Now that I have my master-slave up and running I decided to test the replication speed.I took a 40MB dump file and loaded it onto the master.It took 6 seconds to load.What surprised me was how long it took to complete the load on the slave.It took 43 seconds for the slave to get the complete dump, that was 43 seconds after the master load completed.To eliminate the network as a bottle neck I scp’d the file from master to slave and that took 6 seconds.My guess is the process on the master that reformates the statements with global id’s is really inefficient.

failover

I still haven’t figured out how to automatically failover( good luck looking in the docs), but I did figure out how to manually promote the slave to master.First thing you need to do it take replication offline, that doesn’t mean stopping the daemon but just pausing the replication process:

/opt/tungsten/tungsten/tungsten-replicator/bin/trepctl -service gabriel offline

This needs to be run on both hosts.

To verify that both hosts have replication offline run the following:

/opt/tungsten/tungsten/tungsten-replicator/bin/trepctl status

Now your ready, in my environment 198.201.208.173 is the current master and 198.201.206.221 the slave( you can see this in the output of the status command).The first step is to change the master to a slave:

/opt/tungsten/tungsten/tungsten-replicator/bin/trepctl -service gabriel setrole  -role slave -uri thl://198.201.206.221/

run that on the current master

Then turn the old slave on to a master:/opt/tungsten/tungsten/tungsten-replicator/bin/trepctl -service gabriel setrole -role master -uri thl://198.201.206.221/Run this on the current slave.

Now check your work with the status command.When it looks good run this command on both hosts:

/opt/tungsten/tungsten/tungsten-replicator/bin/trepctl -service gabriel online

Again check your work with the status command.

protip

To run commands on both hosts is easy as in the host setup process you copied over ssh keys and authorized_keys files.I just used a bash for loop:

for H in 198.201.208.173 198.201.206.221;  do ssh root@$H "/root/tungsten-replicator-2.0.5/tungsten-replicator/bin/trepctl status"; done

conclusion

Given the replication lag, poor documentation, long setup, and the fact that its really early I consider tungsten-replicator to have a lot of potential but mostly a science experiment for now.Of course this is with the free version.Like I said in the beginning I know how much it sucks when a master fails of when replication breaks.Tungsten-replicator has all of the features needed to solve all of these problems it just doesn’t give me enough confidence to run this in my production environment…..yet

How to capture all queries on a very busy MySQL server without adding further strain.

We recently had capacity problems where too large a percentage of read queries where going to our master MySQL server instead of the read-only slaves.What I needed to do was capture the queries on the very busy server without consuming more resources to the disk.I started at first using tcpdump to capture the inbound queries.

sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/'

This worked really well but I needed to run this for an hour or so to get a decent sample size and couldn’t use the local disks on because they were already at capacity. What I ended up doing was piping the output from tcpdump through ssh.

ssh $TO_HOST cat -  ">" $OUT_FILE

The whole process looks like this.

sudo tcpdump -s0 -A dst port 3306 and src host app11| strings | grep SELECT| sed 's/^.*SELECT/SELECT/' | ssh log11 cat -  ">" db11m_query_log.02AUG2011_3:30-4:00

This allowed my to capture ~100MB file without consuming more IO resources on the local disk.

create a simple graph with MySQL and munin

Munin is lacking many features that other graphing suites have but when it comes to creating custom graphs it excels.To create a custom graph all you need is a script written in any language that outputs the value you are trying to graph and when you supply the config argument it should return its configuration.In this example I’ll create a graph from a MySQL query.The query is returning the total number delayed jobs in our queue.run script with no argument to get the value:

$ ./delayed_jobs_totaltotaljobs.value 228964

run the script with the config argument:

$ ./delayed_jobs_total configgraph_title Total Delayed Jobstotaljobs.type GAUGEtotaljobs.label TotalJobs graph_category delayed_jobsgraph_args --base 1000 -l 0graph_scale no

The code:

#!/usr/local/bin/ruby  require 'rubygems'   require 'mysql'  hostname = '127.0.0.1'    username = 'REMOVED'    password = 'REMOVED'    databasename = 'delayed_job'  my = Mysql.new(hostname, username, password, databasename)     def total_count(my)    rs = my.query('select count(*) from delayed_jobs')      row = rs.fetch_row    return row    end     def config()     puts 'graph_title Total Delayed Jobs'    puts 'totaljobs.type GAUGE'   puts 'totaljobs.label TotalJobs'      puts 'graph_category delayed_jobs'   puts 'graph_args --base 1000 -l 0'    puts 'graph_scale no'  end    argu =  ARGV[0]     if argu == 'config'     config()     else     total = total_count(my)    puts "totaljobs.value " + total[0].to_s     end    my.close