MySQL - Determining memory requirements?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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?

Here’s output of from MySQLTUner:

SERVER 1:

SERVER 2:

Thanks, D.

2

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.

Idea’s on determining how much is enough for InnoDB can be found at http://www.mysqlperformanceblog.com/2008/09/16/when-is-it-a- time-to-upgrade-memory/ With your current data, having more than 12 GB does not seem beneficial at all, and it is well possible that 8 GB is sufficient.

3

gmouse wrote on Thu, 02 February 2012 12:39

True, it’s been pretty calm the last 50 days.

I don’t know why but occasionally one of the applications (Magento) that talks to ‘server 2’ spawns a lot of connections.

I tweaked caching parameters to make more room for connections, so I could increased those to 350 (on server 2).

Quote:

Probably true, but I have no control over queries - a different team manages app servers (2x Typo3 servers, 1x Magento). They have been made aware of the problem(s).

Quote:

Isn’t that where more RAM should help?

Quote:

So if I get another 8GB (16GB), then I’m really gonna be OK.

Thanks, D.