Mysql – When to increase AWS RDS MySQL Server instance to larger CPU/RAM

amazon-rdsamazon-web-servicesMySQL

I'm wondering at what stage do I need to increase the image for the RDS MySQL server to a larger CPU/RAM instance.

The CPU utilization graph is near 0. The Avg Free Memory is around 150MB. The Avg Swap Usage is 420MB.

Read Latency is 0-20ms/op it spikes up randomly. Avg write latency is on average 5ms/op but spikes up to 10-20ms/op.

Are there some common rules here that I should follow?

Thanks!

Best Answer

There are no common rules

Performance rules need to be set by business & technical objectives - not arbitrary ones.

What performance issue are you trying to resolve or prevent?

Metrics do not equate performance and often have little to to do with business or even technical objectives.

Typically I look at two aspects of performance.

What metrics do I need to monitor that meet performance objectives?
How much resource overhead do I wish to maintain to assure I meet these performance objectives.

Focusing purely on database resource metrics in absence of other data is likely not very useful. These metrics need context - otherwise it is just premature optimization.

Metrics like these are useful for capacity planning if you know your utilization rates.

So maybe more important questions would be:

At peak usage levels is my application performing within acceptable limits?
At peak usage, what is my resource utilization.?
With the expected growth, what will be my resource utilization?

Too often I find people focus on what I call ping, power and pipe (CPU/Disk/RAM). Rarely are these the important things to focus on.

Getting data and setting goals to the questions above will help you make scalability decisions with both technical and business justifications.

Related Solutions

Mysql – Highest value of max_connections in AWS RDS micro instance

About 2 years ago, I was tasked with evaluating Amazon RDS for MySQL. I wrote some posts in the DBA StackExchange about my findings and observations:

Jul 25, 2012 : Scaling Percona datacenters: setup and replication
Aug 02, 2012 : Local database vs Amazon RDS
Sep 21, 2012 : MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

In short, there are three options you cannot alter

max_connections (per Server Model)
innodb_buffer_pool_size (per Server Model)
innodb_log_file_size (all Server Models, 128M)

Here is the Chart I made telling you those per-Server Model limits

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

As for your actual question, t1.micro has 34 as a max_connections setting. If you cannot surpass 32, that is quite understandable. Amazon AWS must be able to connect to and monitor things for the RDS Instance as a SUPER user. Not being able to go beyond 32 is reasonable for a t1.micro instance. In light of this, you will have no choice but to trust the management scheme administered by Amazon for apportioning max_connections and other options among all MySQL Instances in the AWS Cloud.

Mysql – AWS RDS MySQL vs Aurora

The technical differences are summarised nicely in this SlideShare - http://www.slideshare.net/AmazonWebServices/amazon-aurora-amazons-new-relational-database-engine

It's really quite a different architecture/implementation under the covers from standard MySQL, and one that is fundamentally closed.

Amazon are being coy about the extent to which the front end is a MySQL derivative or a complete rewrite that is protocol-compatible - see http://www.theregister.co.uk/2014/11/26/inside_aurora_how_disruptive_is_amazons_mysql_clone/?page=2 - but it looks likely it's at least a major fork with lots of new code. It will have different bugs from the main MySQL releases, which users will be reliant on Amazon to fix. A distributed transactional database backend is a complex thing to write, and while Amazon have some of the best engineers in the world for this sort of system, it's still quite new.

It relies on a completely new Amazon-specific multi-tenanted storage backend, and the updated software isn't freely available as open source, so you can't just download it and run it on your own servers. It may diverge over time (e.g. years) in terms of functional features supported, meaning that code developed against Aurora may no longer work against mainstream MySQL releases, providing a risk of increased lock-in to Amazon.

Regardless, especially if your application needs them, the performance, low replica lag, scalability and recovery time reductions over standard MySQL look pretty compelling in the short term. The lock-in and costs are certainly much lower than with, for example, Oracle's Exadata - which is really the class of solution that Amazon are targetting.

Best Answer

Related Solutions

Mysql – Highest value of max_connections in AWS RDS micro instance

Mysql – AWS RDS MySQL vs Aurora

Related Topic