we have a current dedicated box with 8 GBs of RAM ( dual quad core CPU) and our DB has grown over 17 GBs ( over 3 DBs for our application ). Im assuming this is reason enough to move to a machine that has more memory ( putting sharding aside ).
The new box has 48 GBs of RAM. WE have a second dedicated box which we replicate (master slave) our DB to as well. I was considering setting up two instances of our DB (running of their own filesystem) running in parallel on the same box but different ports and sticking mysql proxy in front of it to load balance my read traffic.
My reasoning was i dont see each of our DBs needing over 20-22 GBs of RAM before running into other bottle necks. WOuld running these in parallel and load balancing read traffic to them provide comparable performance to using two dedicate boxes?
What is the basis of your reasoning? What bottlenecks do you anticipate?
The size of the data stored within your database should not be the basis of the amount of RAM you make available to the system. If it were loaded to the point where it was unable to efficiently serve requests, that is when you should consider it. However, improperly tuned queries, lack of indexes, and a poorly configured MySQL instance will not be addressed in full by hardware upgrades.
On a properly tuned instance, if you encounter a bottleneck that prevents all your memory from being utilized under load, multiple instances will not address that. It will likely be a CPU bottleneck or potentially disk I/O. For one example, check out InnoDB Memory Usage.
There are certain limitations. Such as with MyISAM on MySQL 5.0, each key buffer will be limited to 4GB even on 64-bit platforms, as they are referenced by 32-bit integers.
MySQL creates one thread per connection. As such, if you were not running a 64-bit platform, each thread would be limited to 4GB even with Physical Address Extension.
If you wanted to scale out read requests, you could use multiple database servers load balanced with LVS. I would not use multiple instances.
High Performance MySQL is a fantastic book. I suggest you read it.