Your Mysql on AWS may be running 20x slower!

speeduppc

No, this is not a Spam Ad trying to get you to click on something to speed up your machine. You should take this seriously and look into it if you have a mysql instance on AWS.

Recently, I created a regular ubuntu12 EC2 m1.large instance on amazon. Then I used the standard apt-get tool to install mysql. This installed mysql 5.5 with all the defaults.

I ran a small import of about 5k records into an innodb table. This took about 10s on my macbook pro (with a 7200rpm HD, no solid state HD). I ran the same test on my EC2 instance and noticed it took 100s!!!

I tried adding and modifying a bunch of parameters in the my.cnf (mysql’s config file, probably located in /etc/mysql). Nothing really made much of a difference except this:

innodb_flush_log_at_trx_commit

The default value is 1, which guarantees ACID compliance. But if you can live with a possible 1 second lost of data in case of a catastrophic event, then setting it to 0 or 2 will speed it up quite a bit. I set mine to 2.
You can read more about it here

Now, when I import the 5k records, it takes about 5s. That’s a 20X increase in speed.

So to review, this will affect you if the following apply:

  1. You launched an Ubuntu EC2 instance from AWS
  2. You installed mysql using apt-get
  3. You are using InnoDB tables
  4. You want greater WRITE performance (the change above does not affect READs)

 

Advertisements
Tagged ,

2 thoughts on “Your Mysql on AWS may be running 20x slower!

  1. Sreeram says:

    Hi Kanesee,

    Sorry for contacting you this way. I was wondering if you would be interested in reviewing http://ubiq.co for your readers. It is a new web based MySQL reporting tool I’ve built. Happy to provide extended free usage to your readers.

    Regards,
    Sreeram

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: