Mysql – the Amazon RDS tenancy model

It's unclear what you mean by "wildcard" - there are two types of entries in the security group that you can make - a CIDR block and an EC2 security group:

With a CIDR block, you are specifying an IP address or IP block. The most popular CIDR blocks are /32 which is an individual address and /24 which is a block of 256 addresses (a "class c") - see http://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing for more details on how enter your IP blocks. To open to all IP addresses (i.e. "a wildcard IP address"), you could use the CIDR block 0.0.0.0/0 but Amazon strongly discourages allowing global access to your RDS instance http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html however it might be something you do temporarily to help debug the issue.

With an EC2 security group, it is effectively a wildcard - all EC2 instances that use that EC2 security group will have access to your RDS instance.

Documentation on updating RDS groups is here: http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.html

It it worth noting that it might take some time to actually be "authorized" after you create the RDS security group entry. You might want to check the "Recent Events" tab in the AWS console to see the progress of your security group settings.

It would help if you posted your my.cnf and whether you are using InnoDB or MyISAM tables and whether you are a read-heavy or write-heavy workload. Otherwise, we are just making guesses. Here are mine:

First, I would look and make sure that your queries are properly indexed. High I/O on MySQL databases is either caused by extremely high concurrency, a poorly tuned server, or by poorly performing queries that have to do full table or index scans. Some hints on how to find the poorly performing queries can be found in my post on Ideeli's technical blog.

Check your my.cnf. If you are using InnoDB, make sure innodb_buffer_pool_size and innodb_log_file_size are sufficiently large. Because EBS has such variable latency, maxing out innodb_log_file_size can have substantial performance benefits. If you are using MyISAM (and you shouldn't), make sure your key_buffer size is large enough.

If you are confident that your queries are well optimized, and your server is well-tuned, we can move on to the next item. ext3 is less than ideal for databases. One of the main reasons for this is that ext3 only allows a single thread to update an inode at a time (trying to find documentation for this). If you aren't running with innodb-file-per-table, this means that there is a ton of filesystem contention on the ibdata file. xfs does not have this limitation and has been shown to perform much better (need source) for database workloads.

If you can't change to xfs, make sure that you are using innodb-file-per-table and at a minimum, make sure that you have noatime,nodiratime on the mount.

Next, on to your instance size. A c1.medium is not an ideal instance size for most databases unless the data set is tiny. MySQL ordinarily will benefit from memory over compute power. c1.medium only has 1.7GB of RAM! How big is your data set? In general, a m1.large (with 7.5GB of RAM) will outperform a c1.medium except in very rare cases. It is also twice as expensive, at $0.34/hr.

Now on to RAID of EBS volumes. Yes, RAID will greatly increase your IOPS. (As will increasing your instance size). Do not RAID0...If you care about your data, at least. I have explained this in many places, including on my blog, as a speaker at Percona Live NYC in 2011, and here on serverfault. The short version is that EBS volumes fail in atypical ways and being able to remove a volume from the set has proven to be valuable on serveral occasions, most notably during the great EBS outage of 2011 where some sites were offline for several days...We were offline for 45 minutes at 4AM despite having dozens of instances affected by the EBS issue.

Here are some benchmarks for RAIDed EBS volumes using MySQL.

Finally, Percona Server has a huge number of scalability optimizations. Here is a white paper about my company's experience when switching from MySQL to Percona Server. We were experiencing database stalls and outages every day. Simply switching to Percona Server from MySQL resolved that issue literally overnight due to a number of scalability improvements.

So, in summary...

• Tune your queries
• Tune your server
• Get yourself better "hardware"
• Use xfs, not ext3
• RAID10, not RAID0
• Switch from MySQL to Percona Server

As for MySQL Cluster, it's a completely different animal than MySQL and generally not suitable for most OLTP applications. Galera/Percona XtraDB Cluster are new and interesting clustering products, as well. You have a lot of options before you get to any of this, however. We served 24k qps at peak from a single m2.4xlarge with RAID10 in EC2.

Good luck!