Our InnoDB/Mysql5.6 database is growing at a fairly steady pace of doubling in size every year, currently at 22Gb. This is sales data; we have a number of merchants each generating millions of rows, all stored in one logical instance. The report queries are served by a few read replicas for load balancing and redundancy with the queries distributed randomly.
The database is hosted in AWS/RDS. We outgrew the 4Gb instance, then 8gb and currently reside in a 16Gb node. We expect to last until the end of the year. While AWS offers "memory optimized" instances we feel this will just defer the problem but not solve the bigger picture. Instead of scaling up, we would like to use a number of smaller instances and serve merchant specific data. We can partition the data the "hard way" where instance A only holds merchant A data and instance B only holds merchant B data and so on. The biggest drawback of this is that in case of an instance downtime we cannot just route traffic to another instance since there is no cross-account data sharing.
So I want to ask if anyone can comment on "logical partitioning" which means that all servers still hold all the data (disk space is not a problem) but queries related to one account are "sticky" i.e. routed to the same server (or server pool) to maximize cache hits. That way if a DB instance, zone or region goes down any other instance elsewhere can pick up the work of the down-instance since the same data was distributed everywhere.
In your opinion, would logical partitioning based on repeated queries to the same set of instances reduce the need for memory, assuming all the queries use indexes where the account ID is the first field in every composite key?