3

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?

Prem
  • 31
  • 2

1 Answers1

2

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.

Warner
  • 23,440
  • 2
  • 57
  • 69
  • Yeah I have spent quite a bit of time reading up on mysqlperformanceblog.com as well. I think on our current box its tuned as well as can be. The problem is that when our site gets crawled some days the number of concurrent requests for random pages goes through the roof defeating any caching we have setup. The 6 GBs of memory on that box gets consumed with over 250 db connections and the DB starts to crawl at a snails pace.. CPU is actually under 40% most of the time ( even during high load) Defintely want to setup a Load Balanced mysql setup but it seems like RAM is the bottle neck currently – Prem Jun 29 '10 at 17:00
  • One of my DB has a surplus of connections at all times and is tuned to support several thousand on 8GB of RAM. What are the db connections doing? Sounds more likely to be disk I/O if the connections are cranking. Are tables locked? – Warner Jun 29 '10 at 17:13
  • When its cranking its almost entirely read traffic. So there are not really any table locks in place. There is currently a write query as part of each page load ( recording view stats ) which we could off load.. but the queries to load our content is whats really going when this happens... – Prem Jun 29 '10 at 21:20
  • Use the sysstat (http://pagesperso-orange.fr/sebastien.godard/) utilities to check out disk io. explain your queries, do you need more indexes? Sounds like it could probably use some query optimization if MySQL is already tuned well. – Warner Jun 29 '10 at 21:35