New --mysql-stop option for ec2-consistent-snapshot

| 11 Comments

The ec2-consistent-snapshot software tries its best to flush and lock a MySQL database on an EC2 instance while it initiates the EBS snapshot, and for many environments it does a pretty good job.

However, there are situations where the database may spend time performing crash recovery from the log file when it is started from a copy of the snapshot. We are seeing this behavior at CampusExplorer.com where the database is constantly active and we have innodb_log_file_size set (probably too) high. The delay is doubtless exacerbated by the fact that the blocks on the new EBS volume are being recovered from S3 as it is being built from the snapshot.

Google has created an innodb_disallow_writes MySQL patch which I think points out the problem we may be hitting.

“Note that it is not sufficient to run FLUSH TABLES WITH READ LOCK as there are background IO threads used by InnoDB that may still do IO.”

It would be very nice to have this patch incorporated in MySQL on Ubuntu. It looks like the OurDelta folks have already incorporated the patch. [Update: See rsimmons’ comment below which explains why this particular patch might not be the answer.]

In any case, when we bring up a database using an EBS volume created from an EBS snapshot of an active database, it can take up to 45 minutes recovering before it lets normal clients connect. This is too long for us so we’re trying a new approach.

The ec2-consistent-snapshot now has a --mysql-stop option which shuts down the MySQL server, initiates the snapshot, and then restarts the database. Our hope is that this will get us a snapshot which can be restored and run without delay. If any MySQL experts can point out the potential flaws in this, please do.

Since we obviously can’t stop and start our production database every hour, we are performing this snapshot activity on a replication slave that is dedicated to snapshots and backups.

We continue to perform occasional snapshots on the production database EBS volume just to help keep it reliable per Amazon’s instructions, but we don’t expect to be able to restore it without crash recovery.

If you’d like to test the new --mysql-stop option, please upgrade your ec2-consistent-snapshot package from the Alestic PPA and let me know how it goes.

11 Comments

It would be great to get an option for mysql_socket. I have a non-standard socket location in my setup and I've had to modify ec2-consistent-snapshot so that mysql_host was "localhost:mysql_socket=/path/to/mysql.sock". I don't know enough Perl, or I'd do it myself.

Thanks for all the great tools and articles
-David

I don't think there's a way for you to avoid doing a recovery or clean shutdown. If I understand correctly, the innodb log is basically a way of borrowing against the future, by doing fast sequential writes to the end of a log file instead of random writes to the data files. But eventually the writes need to be applied to the data files. A typical busy mysql server will have plenty of dirty blocks in the buffer pool, which means they have been written to the log but not the data files. At some point you will need to apply these changes to the data files, whether through clean shutdown or recovery. As far as I know the standard options for a _hot_ consistent backup are:

1) filesystem level snapshot and then recovery. you can do the recovery on a spare host immediately after the snapshot so you can quickly restore later in case of disaster
2) percona xtrabackup. this cleverly avoids needs a FS level snapshot, but requires doing a "log apply" step (basically recovery) before being able to use the backup.

You should definitely experiment with reducing innodb_log_file_size as far as you can before it has a negative performance impact. Your recovery time should be directly proportional to it.

As far as I understand, the innodb_disallow_writes patch is just an alternative when you can't do a filesystem level snapshot. It allows you to get a clean copy of the data and logs without shutting down the server, but since it doesn't flush dirty pages you would still need to do a recovery after copying the files.

Performing backups from a slave is fine, but there are some bugs I have encountered with replication that can cause the slave to accumulate small differences from the master over time. So I would recommend checking for that (mk-table-checksum) and/or periodically resyncing the slave from the master.

David: Can you just set the complete value using the --mysql-host option? Please add comments to this ticket: https://bugs.launchpad.net/ec2-consistent-snapshot/+bug/481477

rsimmons: Thanks. A lot of useful information! We'll try reducing innodb_log_file_size and will keep an eye on the replication issues which we've also heard about.

Without the work Eric has done, a lot of us would not be using AWS as much as we do because Eric has made it so much easier.

One thing I am still struggling with is how to set up some form of MySQL replication, Multi-Master and/or Master-Slave configuration on EC2.

It would be wonderful if you could share some of the techniques / best practices you have discovered if not some code!

In any case, thanks for all the stuff you do to help all of us.
Rob

Rob: I learned most of what I know about setting up master-slave replication here:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Eric,

I got the latest version of the ec2-consistent-snapshot to use with the --mysql-stop option.

I am using the following command:

ec2-consistent-snapshot
--aws-access-key-id key
--aws-secret-access-key secret
--region us-east-1
--mysql
--mysql-host ec2-xxx-yyy-zzz-www.compute-1.amazonaws.com
--mysql-username madmin
--mysql-password madminpassword
--xfs-filesystem /vol vol-123456789


However, I am getting the following error:

DBI connect(';host=localhost','',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at /usr/bin/ec2-consistent-snapshot line 179
Use of uninitialized value $mysql_username in concatenation (.) or string at /usr/bin/ec2-consistent-snapshot line 179.
ec2-consistent-snapshot: ERROR: Unable to connect to MySQL on localhost as at /usr/bin/ec2-consistent-snapshot line 179.

As you can see I am NOT using the root user to login to the db, so I am not sure where it is picking up the credentials.

Is this a known bug or am I doing something so obviously silly that it escapes me? Apologies if the latter...

- Bill

Eric,

I figured it out...The latest version of the ec2-consistent-snapshot seems to ignore the mysql command line options. If I provide the options in the $HOME/.my.cnf file, it seems to work.

Thanks,

- Bill

Bill: Please upgrade to the latest version of ec2-consistent-snapshot and see if that fixes things. Rod Vagg submitted a patch which fixes a bug when you don't have a .my.cnf file.

By the way, it is rare that mysqlhost should be anything but localhost as you are almost always snapshotting a local file system.

Eric, is there a good reason why snapshots should not be run from an external source? I have been thinking about the creation of a snapshot console I can run on my local system that lets me execute consistent snapshots, without having to install and maintain all of the perl, EC2 API, and keypair bits (as well as your awesome utility) on each EC2 instance. Obviously, if there is an exceptionally good reason why snapshots should only be performed locally, then it will override the convenience of keeping the snapshot functionality in one place. But from a configuration management point of view, a localized utility that manages remote instances is ideal.

Thanks,

Earl

Earl: It's ok to run a snapshot from outside the instance, except that this makes it difficult to flush/freeze the file system and lock a database while the snapshot is started. This means that the snapshot might not be a consistent representation of the file system and application data. If you really don't want credentials stored on the instance, you might be able to work out a separate process with an ssh to the instance to perform the locking steps, but it starts to get a little complicated to implement.

Leave a comment

Ubuntu AMIs

Ubuntu AMIs for EC2:


AWS Jobs

AWS Jobs

More Entries

Throw Away The Password To Your AWS Account
reduce the risk of losing control of your AWS account by not knowing the root account password As Amazon states, one of the best practices for using AWS is Don’t…
AWS Community Heroes Program
Amazon Web Services recently announced an AWS Community Heroes Program where they are starting to recognize publicly some of the many individuals around the world who contribute in so many…
EBS-SSD Boot AMIs For Ubuntu On Amazon EC2
With Amazon’s announcement that SSD is now available for EBS volumes, they have also declared this the recommended EBS volume type. The good folks at Canonical are now building Ubuntu…
EC2 create-image Does Not Fully "Stop" The Instance
The EC2 create-image API/command/console action is a convenient trigger to create an AMI from a running (or stopped) EBS boot instance. It takes a snapshot of the instance’s EBS volume(s)…
Finding the Region for an AWS Resource ID
use concurrent AWS command line requests to search the world for your instance, image, volume, snapshot, … Background Amazon EC2 and many other AWS services are divided up into various…
Changing The Default "ubuntu" Username On New EC2 Instances
configure your own ssh username in user-data The official Ubuntu AMIs create a default user with the username ubuntu which is used for the initial ssh access, i.e.: ssh ubuntu@<HOST>…
Default ssh Usernames For Connecting To EC2 Instances
Each AMI publisher on EC2 decides what user (or users) should have ssh access enabled by default and what ssh credentials should allow you to gain access as that user.…
New c3.* Instance Types on Amazon EC2 - Nice!
Worth switching. Amazon shared that the new c3.* instance types have been in high demand on EC2 since they were released. I finally had a minute to take a look…
Query EC2 Account Limits with AWS API
Here’s a useful tip mentioned in one of the sessions at AWS re:Invent this year. There is a little known API call that lets you query some of the EC2…
Using aws-cli --query Option To Simplify Output
My favorite session at AWS re:Invent was James Saryerwinnie’s clear, concise, and informative tour of the aws-cli (command line interface), which according to GitHub logs he is enhancing like crazy.…
Reset S3 Object Timestamp for Bucket Lifecycle Expiration
use aws-cli to extend expiration and restart the delete or archive countdown on objects in an S3 bucket Background S3 buckets allow you to specify lifecycle rules that tell AWS…
Installing aws-cli, the New AWS Command Line Tool
consistent control over more AWS services with aws-cli, a single, powerful command line tool from Amazon Readers of this tech blog know that I am a fan of the power…
Using An AWS CloudFormation Stack To Allow "-" Instead Of "+" In Gmail Email Addresses
Launch a CloudFormation template to set up a stack of AWS resources to fill a simple need: Supporting Gmail addresses with “-” instead of “+” separating the user name from…
New Options In ec2-expire-snapshots v0.11
The ec2-expire-snapshots program can be used to expire EBS snapshots in Amazon EC2 on a regular schedule that you define. It can be used as a companion to ec2-consistent-snapshot or…
Replacing a CloudFront Distribution to "Invalidate" All Objects
I was chatting with Kevin Boyd (aka Beryllium) on the ##aws Freenode IRC channel about the challenge of invalidating a large number of CloudFront objects (35,000) due to a problem…
Email Alerts for AWS Billing Alarms
using CloudWatch and SNS to send yourself email messages when AWS costs accrue past limits you define The Amazon documentation describes how to use the AWS console to monitor your…
Cost of Transitioning S3 Objects to Glacier
how I was surprised by a large AWS charge and how to calculate the break-even point Glacier Archival of S3 Objects Amazon recently introduced a fantastic new feature where S3…
Running Ubuntu on Amazon EC2 in Sydney, Australia
Amazon has announced a new AWS region in Sydney, Australia with the name ap-southeast-2. The official Ubuntu AMI lookup pages (1, 2) don’t seem to be showing the new location…
Save Money by Giving Away Unused Heavy Utilization Reserved Instances
You may be able to save on future EC2 expenses by selling an unused Reserved Instance for less than its true value or even $0.01, provided it is in the…
Installing AWS Command Line Tools from Amazon Downloads
This article describes how to install the old generation of AWS command line tools. For the most part, these have been replaced with the new AWS cli that is…
Convert Running EC2 Instance to EBS-Optimized Instance with Provisioned IOPS EBS Volumes
Amazon just announced two related features for getting super-fast, consistent performance with EBS volumes: (1) Provisioned IOPS EBS volumes, and (2) EBS-Optimized Instances. Starting new instances and EBS volumes with…
Which EC2 Availability Zone is Affected by an Outage?
Did you know that Amazon includes status messages about the health of availability zones in the output of the ec2-describe-availability-zones command, the associated API call, and the AWS console? Right…
Installing AWS Command Line Tools Using Ubuntu Packages
See also: Installing AWS Command Line Tools from Amazon Downloads Here are the steps for installing the AWS command line tools that are currently available as Ubuntu packages. These include:…
Ubuntu Developer Summit, May 2012 (Oakland)
I will be attending the Ubuntu Developer Summit (UDS) next week in Oakland, CA. ┬áThis event brings people from around the world together in one place every six months to…
Uploading Known ssh Host Key in EC2 user-data Script
The ssh protocol uses two different keys to keep you secure: The user ssh key is the one we normally think of. This authenticates us to the remote host, proving…
Seeding Torrents with Amazon S3 and s3cmd on Ubuntu
Amazon Web Services is such a huge, complex service with so many products and features that sometimes very simple but powerful features fall through the cracks when you’re reading the…
CloudCamp
There are a number of CloudCamp events coming up in cities around the world. These are free events, organized around the various concepts, technologies, and services that fall under the…
Use the Same Architecture (64-bit) on All EC2 Instance Types
A few hours ago, Amazon AWS announced that all EC2 instance types can now run 64-bit AMIs. Though t1.micro, m1.small, and c1.medium will continue to also support 32-bit AMIs, it…
ec2-consistent-snapshot on GitHub and v0.43 Released
The source for ec2-conssitent-snapshot has historically been available here: ec2-consistent-snapshot on Launchpad.net using Bazaar For your convenience, it is now also available here: ec2-consistent-snapshot on GitHub using Git You are…
You Should Use EBS Boot Instances on Amazon EC2
EBS boot vs. instance-store If you are just getting started with Amazon EC2, then use EBS boot instances and stop reading this article. Forget that you ever heard about instance-store…