Migrating to RDS: Converting MyISAM to InnoDB

If you want to leverage the RDS service on AWS, you’ll receive maximum benefit by converting MyISAM tables to InnoDB. Here’s a distillation of a useful approach outlined Another woblag on the Interweb


// Create a backup of your database
mysqldump -u USER -p MYSQLDB | gzip -c > /mnt/backups/mysqldb.sql.gz


// Log in to your mysql instance and dump a .sql to convert tables in batch
mysql> select concat('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;') from information_schema.tables where table_schema='mydb' and ENGINE='MyISAM' into outfile '/tmp/InnoBatchConvert.sql'
mysql> quit
shell> mysql -u root -p < /tmp/InnoBatchConvert.sql


// Confirm tables have been converted to InnoDB
mysql> select table_name, engine from information_schema.tables where table_schema = 'mydb';

An introduction to cloud computing for biologists (aka the 10-minute model organism database installation)

This tutorial will explain the basic concepts of cloud computing and get you up and running in minutes. No knowledge of system administration or programming is necessary. As an example, it describes how to launch your own instance of the model organism database WormBase.

Introduction to cloud computing

If you aren’t familiar with cloud computing here’s all you need to know. At its simplest, cloud computing refers to using remote compute resources over the network as if they were a computer sitting on your desktop. These services are typically virtualized and used in an on-demand fashion.

Several vendors provide cloud computing options. Here, we’ll focus on
Amazon’s Elastic Compute Cloud (EC2).

On EC2, developers can create Amazon Machine Images (AMIs) which are essentially snapshots of a full computer system. For example, the WormBase AMI contains everything necessary to run WormBase — all software and databases with the operating system preconfigured.

Booting up an image is referred to launching an “instance”. When you do so, you choose the size of the server to allocate (for example, how many cores and how much RAM) to run the instance with. You can start, stop, or reboot the instance at any time. Terminating the instance completely removes it from your account. The original reference AMI remains; you can launch a new instance from it any time. This is what Amazon means by elastic. You can provision and decommission new servers with custom capacity in minutes mitigating overhead costs like data centers, surly IT departments, and draconian firewall regulations.

Amazon’s EC2 service is a “pay-for-what-you-use” service; running an instance is not free. You are charged nominal rates for 1) the size of the instance allocated; 2) the amount of disk space the instance requires even if it isn’t running; 3) the amount of bandwidth the instance consumes; 4) how long the instance is running.

A complicated model organism database like WormBase typically require a “large” instance (see below). Running 24/7, the estimated cost would be approximately $2700/year. Costs can be mitigated by starting and stopping the instance when needed, pausing the instance in its current state. This is conceptually similar to puting a desktop computer to sleep. Alternatively, if you aren’t modifying the data on the server, you can safely terminate it when you are done, avoiding disk use charges, too. Simply launch a new instance from the original WormBase AMI. Launching from an AMI requires slightly more time (several minutes) than restarting a stopped instance (< minute). Requesting a dedicated instance in advance from Amazon further reduces the cost by approximately 30%. caveat emptor: these are back-of-the-napkin calculations. Costs can vary dramatically especially if you start making many, many requests to the website. Bandwidth charges for accessing the website are nominal.

Example: Personal Instances of WormBase through Amazon’s EC2

In the past running a private instance of WormBase has been a time-consuming process requiring substantive computer science acumen.

Today I’m happy to announce WormBase Amazon Machine Images (wAMIs, pronounced “whammys”) for Amazon’s Elastic Compute Cloud (EC2). The WormBase AMI makes it absolutely trivial to run your own private version of WormBase.

Running your own instance gives you:
* Dedicated resources
* A feature-rich data mining platform
* Privacy

Contents of the WormBase AMI

* The WS226 (and beyond) version of the database
* The (mostly) full WormBase website
* The Genome Browser with 10 species
* A wealth of pre-installed libraries for data mining (to be covered in a subsequent post)

The first WormBase AMI is missing a few features:
* WormMart
* BLAST

Launching your own instance of WormBase

Here’s a really bad screen cast. You might want to read through the rest of the tutorials for details.

View the screencast in full size.

The general steps for launching an instance of a new AMI are as follows. Note that in the management console it is possible to execute many of these steps during the process of launching any one specific instance, too.

1. Sign up for an Amazon Web Services account

See up for an account at aws.amazon.com. You’ll need a credit card.

2. Create a keypair

Note: You can also complete this step when you launch your instance if you prefer.

When you launch an instance Amazon needs to ensure that you are who you say you are (read: that you have the ability to pay for the resources that you consume), as well as give you a mechanism for logging into the server. This authentication process is handled through the use of secret keys. Even if you only intend to use the web interface of WormBase and not log in directly to the server, you will still need to generate a keypair.

To do this, log in to your Amazon AWS account and click on the EC2 tab. In the left hand pane, click on “Keypairs”. You’ll see a small button labeled “Create Keypair”. Click, and create a new kaypair. You can name it whatever you like. When you click continue a file will be downloaded to your computer. You will need this file if you intend to log on to the server. Store it in a safe place as others can launch services using your account if they get access to this file!

3. Configure a new security group

Note: You can also complete this step when you launch your instance if you prefer.

Security groups are a list of firewall rules for what types of requests your instances respond to. They can be standard services on standard ports (HTTP on port 80) or custom, and they can range from allowing the entire internet to a single IP address. They are a quick way to lock down who gets to use your instance. For now, we’ll create a security group that is very permissive.

Click “Create new group”, give the group a name and description. From the dropdown, select “HTTP”. Click Add Rule. Repeat, this time selecting SSH. Although not required, enabling SSH will allow us to actually log into the server to perform administrative or diagnostic tasks. Click Add Rule, then Save.

4. Find and launch an instance of the WormBase (WS226) AMI

Now we’re ready to launch our own instance. See the video tutorial for description.

5. Get the public DNS entry for your new instance

Your new instance is elastic; it gets a new IP address every time it is launched (although Amazon has services that let it retain a static address, too). You need to get the hostname so that you can connect to the server. Click on “Instances”, select the running instance, and in the bottom pane, find the “Public DNS” entry. Copy this entry, open a new tab in your browser and paste in the URI. It will look something like this:

ec2-50-17-41-111.compute-1.amazonaws.com

6. Stopping your instance

When you are done with your instance, shut it down by going to the EC2 tab > Instances. Select the instance and from other the “Instance Action” drop down or by right clicking, select “Stop”. You’re instance will be paused where you are. Repeat these steps selecting “start” to restart it. Note: you will continue to accumulate charges associated with disk storage while the instance is stopped, but will not incur compute charges. Alternatively, you can choose to “terminate” the instance. Once you do so, be sure to visit the “Volumes” and select the EBS volume that had been attached to the instance — it will be 150GB in size. It will cost about $7/month to save this volume.

In a subsequent tutorial, I’ll show you how to go beyond the web browser to use the powerful command line data mining tools packaged with every WormBase AMI.

Questions? Contact me at todd@wormbase.org.