Virtualizing MySQL

I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.

It really depends on what you’re looking to do. Many companies don’t have the money and staff to have an in-house data center with proper power and network redundancy; others don’t want the depreciation associated with owning computer hardware (even if they leased space in a data center, they’d have to buy equipment to put in it).

Some reasons to virtualize:
1) you need a fresh machine and cannot wait to order a new one or re-purpose an older one.
2) your need for machines/services fluctuates (and again, re-purposing takes time). This need can be as broad as employee desktops or as specialized as needing extra machines for a qa cycle.
3) you own resources that are not being utilized to the fullest extent — virtualization can sometimes make more or better use of these resources.
4) you need to easily re-create an exact environment and do not want to worry about hardware differences.

Here at The Pythian Group, we have clients using many different types of virtualization.

A few clients are using MySQL on Amazon’s EC2 platform. The biggest advantage is also one of the biggest disadvantages — before EBS (elastic block storage) was offered, the threat of a reboot wiping the filesystem clean meant that we really had to ensure that we had:

1) redundancy
2) a catalog of what was needed on the machine — everything from users to perl modules. This can be done either by using a machine image, documenting a setup and recreating the machine manually, or via automation. By using tools like CFEngine or Puppet to control machine configurations, our clients have the added benefit of more standardized installations and layouts. As well, pushing a change (say, adding a new hire’s public key into an authorized_keys file) is made much much more easy.

The downside is, of course, the work to set up tools such as this. However, they are fairly common best practices, and are almost always good to implement.

The clients that are using Amazon EC2 are happy with the service they get; One client I work with especially closely moved to EC2 because they had a bad experience with their hosting provider. I cannot speak to pricing, but I do know that being able to just *have* another machine up and running in minutes is very useful … periodic work such as load testing and qa cycles work really well.

We also have many clients who “virtualize” MySQL by running more than one instance on a server. A few clients have a replication slave that has 2 instances — one as a read-only reporting instance, and another as an instance to backup, doing a cold backup — stopping the instance, copying the files, starting the instance back up. Other clients have 4-5 MySQL instances running on one machine, for developers to have individual database instances to write code against and as backups for 4-5 different physical machines.

I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.

It really depends on what you’re looking to do. Many companies don’t have the money and staff to have an in-house data center with proper power and network redundancy; others don’t want the depreciation associated with owning computer hardware (even if they leased space in a data center, they’d have to buy equipment to put in it).

Some reasons to virtualize:
1) you need a fresh machine and cannot wait to order a new one or re-purpose an older one.
2) your need for machines/services fluctuates (and again, re-purposing takes time). This need can be as broad as employee desktops or as specialized as needing extra machines for a qa cycle.
3) you own resources that are not being utilized to the fullest extent — virtualization can sometimes make more or better use of these resources.
4) you need to easily re-create an exact environment and do not want to worry about hardware differences.

Here at The Pythian Group, we have clients using many different types of virtualization.

A few clients are using MySQL on Amazon’s EC2 platform. The biggest advantage is also one of the biggest disadvantages — before EBS (elastic block storage) was offered, the threat of a reboot wiping the filesystem clean meant that we really had to ensure that we had:

1) redundancy
2) a catalog of what was needed on the machine — everything from users to perl modules. This can be done either by using a machine image, documenting a setup and recreating the machine manually, or via automation. By using tools like CFEngine or Puppet to control machine configurations, our clients have the added benefit of more standardized installations and layouts. As well, pushing a change (say, adding a new hire’s public key into an authorized_keys file) is made much much more easy.

The downside is, of course, the work to set up tools such as this. However, they are fairly common best practices, and are almost always good to implement.

The clients that are using Amazon EC2 are happy with the service they get; One client I work with especially closely moved to EC2 because they had a bad experience with their hosting provider. I cannot speak to pricing, but I do know that being able to just *have* another machine up and running in minutes is very useful … periodic work such as load testing and qa cycles work really well.

We also have many clients who “virtualize” MySQL by running more than one instance on a server. A few clients have a replication slave that has 2 instances — one as a read-only reporting instance, and another as an instance to backup, doing a cold backup — stopping the instance, copying the files, starting the instance back up. Other clients have 4-5 MySQL instances running on one machine, for developers to have individual database instances to write code against and as backups for 4-5 different physical machines.