I’ve been put in charge of two 64-bit servers with 8GB of RAM running mysql 5.0.67. These are configured in a sort-of ‘mixed replication’ setup - server 1 is ‘master’ for one set of databases, which are replicated to server 2, while server 2 is ‘master’ for another set of databases, which are replicated to server 1.
So a set of clients (app servers) is querying server 1 and another set of clients is querying server 2 - but in case of a failure, each server takes over the role of the other and clients still work.
There have been some problems with running out of connections and/or memory. I tried to tweak the configuration with the help of MySQLTuner 1.2.0 script (http://mysqltuner.com/) with limited success.
Now I’ve been given an ‘OK’ for memory upgrade on both servers. I’d be more then happy to get 64GB for each server (which is the max. hardware supports), but the budget is not unlimited and I need to make a good case.
So the question basically is - how can I determine how much memory would be ‘enough’ for my given case?
You say you run out of connections, but the MySQLTuner output shows that this has not happened in the last 50 days.
Depending on data growth and the nature of the data (table lay-out and what part of your data is frequently accessed), you may get a much larger gain from optimizing your queries than from a memory upgrade. For instance, every two seconds a tmp table is created on disk. Depending on the size of this table, this could result in a large performance loss when you are already running low on memory and need to read much data from disk.