Saturday, January 4, 2014

AWS EC2 + MySQL with Complex, Highly-Concurrent Queries

I recently had the challenge of migrating one of our MySQL servers from a dedicated host to an AWS EC2 instance. Based on my research, the general consensus is that Amazon Web Services (AWS) Elastic Compute Cloud (EC2) is unable to power MySQL except for very light workloads.

Several newer developments gave me hope that acceptable MySQL performance could be attained:
  1. 4,000 Provisioned IOPs - With the May 2013 introduction of 4k pIOPs EBS volumes, EC2 instances could finally be configured with persistent storage (as opposed to ephemeral storage) fast enough for RDBMS performance. 
  2. cc2.8xlarge - this monstrosity provides 60GB of RAM, 32 virtual CPUs, and 10 gigabit networking. Since implementing this, AWS added the c3.8xlarge for the same price- running the next generation of Intel Xeon with a similar configuration but with more overall compute power; but adding 2 320GB SSDs. 
  3. MySQL threadpool - I wrote about this in my previous post. By pre-allocating a pool of threads available for handling connections, Percona Server is able to prevent the CPU from spending the majority of its cycles performing context switching
When testing our implementation on a cc2.8xlarge with a 4k PIOPs EBS volume, I found that the MySQL Server was still unable to handle the query load necessary to migrate to AWS. In fact, queries began queueing up in the processlist and all CPU threads would spike to 100% within minutes. 

Adding the threadpool support from Percona Server 5.5, I was able to successfully provide more than 600 qps of complex aggregate queries, all while CPU load remained within healthy thresholds. I did not perform extensive A / B benchmarking for this problem, but it would seem that the overhead added to CPU scheduling by the EC2 hypervisor makes it much more likely to overload under MySQL's workload.

No comments:

Post a Comment