Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
Mark Atwood
Some basic advice for running MySQL on Amazon AWS EC2
I've been recently asked for advice on running MySQL on EC2.

  • Run only a one MySQL server process on the instance.

  • This bears repeating. Run only the MySQL server on the instance. Not your app server, or your dev environment, or your...

  • Use the large or xlarge size, not the small size.
  • Dedicate the additional partitions to be InnoDB tablespaces.
  • "Pre-heat" the tablespace partition with dd. That is, dd if=/dev/urandom of=/path/to/part bs=8K before using the partition.
  • This is one of the times where it's worth it to run tablespace on raw devices. Don't bother to put a filesystem down on those partitions.
  • Tune your database server. Get someone who knows MySQL tuning to do it for you.
  • Backup and Replicate with rigor. When an instance gets shut down, it Goes Away, including the filesystem.
  • Control access. Setup ec2 access rules so that only your known clients and relication peers can connect to the MySQL server port, and severely limit any other access. At most the SSH port.
  • Automate everything. Be able to build, setup, and start new MySQL server instances, and new application server instances, by simply running a script or pushing a button.

This advice is not complete, your milage may vary, etc etc


1 comment or Leave a comment
awfief From: awfief Date: January 6th, 2009 06:29 am (UTC) (Link)
Most of these run true for regular stuff too. Add to that list "mount partitions noatime and nodiratime" and "use a 64-bit OS".

You also give very few REASONS, so people can't make informed decisions, they just say "well, smart LJ told me to do that....". ie, you want to use large or xlarge because it has more RAM, but you don't say that.

Why do you say not to run dev or test w/multiple instances? We have a client doing just that, and there are 13 instances on one machine (all are replicating production instances for backup to S3). The machine runs just fine. Is it a cost thing maybe?

As for tablespaces on raw devices -- yes and no. The same caveats apply, such as backing up is way more of a pain as you can't do a cold backup (stop the server, copy files, start the server). Again, you don't give reasons why it's good, just that it's good. It's hard to make an informed decision like that.

And you are so way behind the times -- Yes, when an instance shuts down it goes away, but Amazon now has EBS. Our clients use EBS for the database partitions. As for Automate Everything, that's something everyone should do anyway, but having an EC2 instance means you're more inclined to actually DO it.

1 comment or Leave a comment