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!
Best Answer
Are these temporary tables created by the application (i.e. 'CREATE TEMPORARY TABLE....') or are they created by the database (used when recreating indexes / sorting large datasets).
Tweaking the config to allow more sorting in memory / more threads should help (how you do this depends on which engine the tables are implemented in). mysqltuner.pl should make sensible suggestions.
But you'll get a lot more benefit from tuning your queries.
1) make sure that none of your code is disabling/enabling indexes on very large tables (this improves performance when initially populating a table, and can improve turnaround on adding rows to a table - but at the expense of a loss of concurrency).
2) Use this script to strip out literal values from queries in the slow query log then prioritize based on the queries with the highest cumulative processing time.