It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not a DBA and have no frame of reference. It seems to be doing garbage collection once every 2-3 days now whenever there's only 100mb left.
The server itself has 1.7GB of RAM.
Before you switch server models, you need to know what differences there in configuration
I sat down and carefully looked over all default MySQL settings that would normally be written in a my.cnf. All are values are the same with the exception of two
Amazon set the following values based on server model
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)
For more information see my past posts on MySQL in Amazon RDS
Sep 21, 2012 : MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many DatabasesSep 18, 2012 : How do I reduce the database size on AWS RDS MySQL?Sep 10, 2012 : Does the Amazon RDS backup/snapshot service lock tables?Aug 02, 2012 : Local database vs Amazon RDSJul 25, 2012 : Scaling Percona datacenters: setup and replication
Just ran across this question, and can give you a couple of suggestions.
If you're trying to gauge when to move to a bigger instances (vs. increasing the size of the databases) when you're getting close to hitting some sort of resource limit: Memory, I/O and CPU all have the ability to limit your performance.
The symptom you mention in the question may be a symptom that you're using more and more memory - memory used for cache & buffers are being reclaimed for other uses. Upgrading to a large instance type will increase the amount of available memory.
The chart that Rolando posted should give you a good guideline as to the number of available connections - if you're hitting those limits, then it's time to upgrade. Available connections are directly related to available memory, so hitting connection limits means you should upgrade.
If your CPU is averaging > 50% or so, you might want to start planning on an upgrade.
Finally, if you see consistently high I/O you may want to consider a larger instance (generally, the more powerful the instance the better the I/O) or using provisioned IOPS.
