# MySQL - Determining memory requirements?

**URL:** <https://forums.percona.com/t/mysql-determining-memory-requirements/1749>\
**Category:** Other MySQL® Questions\
**Created:** [February 2, 2012, 3:35am UTC](https://forums.percona.com/t/mysql-determining-memory-requirements/1749 "2012-02-02T03:35:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![danci1973](https://avatars.discourse-cdn.com/v4/letter/d/b19c9b/32.png) [@danci1973](https://forums.percona.com/u/danci1973)\
**Post date:** [February 2, 2012, 3:35am UTC](https://forums.percona.com/t/mysql-determining-memory-requirements/1749/1 "2012-02-02T03:35:47Z")

</div>

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/](https://http)) 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:

> [@](#):
>
> -------- General Statistics --------------------------------------------------  
> [–] Unable to check for the latest MySQLTuner version  
> [OK] Currently running supported MySQL version 5.0.67-log  
> [OK] Operating on 64-bit architecture
> 
> -------- Storage Engine Statistics -------------------------------------------  
> [–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster  
> [–] Data in MyISAM tables: 2G (Tables: 630)  
> [–] Data in InnoDB tables: 6G (Tables: 413)  
> [!!] Total fragmented tables: 43
> 
> -------- Security Recommendations -------------------------------------------  
> [OK] All database users have passwords assigned
> 
> -------- Performance Metrics -------------------------------------------------  
> [–] Up for: 51d 8h 42m 55s (787M q [177.470 qps], 5M conn, TX: 2571B, RX: 210B)  
> [–] Reads / Writes: 46% / 54%  
> [–] Total buffers: 4.5G global + 11.5M per thread (200 max threads)  
> [OK] Maximum possible memory usage: 6.7G (85% of installed RAM)  
> [OK] Slow queries: 0% (6K/787M)  
> [OK] Highest usage of available connections: 44% (88/200)  
> [OK] Key buffer size / total MyISAM indexes: 512.0M/542.6M  
> [OK] Key buffer hit rate: 99.9% (3B cached / 2M reads)  
> [OK] Query cache efficiency: 81.7% (487M cached / 597M selects)  
> [!!] Query cache prunes per day: 908155  
> [OK] Sorts requiring temporary tables: 0% (84 temp sorts / 4M sorts)  
> [!!] Temporary tables created on disk: 27% (2M on disk / 8M total)  
> [OK] Thread cache hit rate: 99% (1K created / 5M connections)  
> [OK] Table cache hit rate: 98% (2K open / 2K opened)  
> [OK] Open file limit used: 5% (2K/40K)  
> [OK] Table locks acquired immediately: 99% (249M immediate / 249M locks)  
> [!!] InnoDB data size / buffer pool: 6.7G/3.1G
> 
> -------- Recommendations -----------------------------------------------------  
> General recommendations:  
> Run OPTIMIZE TABLE to defragment tables for better performance  
> Increasing the query\_cache size over 128M may reduce performance  
> When making adjustments, make tmp\_table\_size/max\_heap\_table\_size equal  
> Reduce your SELECT DISTINCT queries without LIMIT clauses  
> Variables to adjust:  
> query\_cache\_size (\> 768M) [see warning above]  
> tmp\_table\_size (\> 64M)  
> max\_heap\_table\_size (\> 64M)  
> innodb\_buffer\_pool\_size (\>= 6G)

SERVER 2:

> [@](#):
>
> -------- General Statistics --------------------------------------------------  
> [–] Unable to check for the latest MySQLTuner version  
> [OK] Currently running supported MySQL version 5.0.67-log  
> [OK] Operating on 64-bit architecture
> 
> -------- Storage Engine Statistics -------------------------------------------  
> [–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster  
> [–] Data in MyISAM tables: 3G (Tables: 641)  
> [–] Data in InnoDB tables: 6G (Tables: 631)  
> [!!] Total fragmented tables: 41
> 
> -------- Security Recommendations -------------------------------------------  
> [OK] All database users have passwords assigned
> 
> -------- Performance Metrics -------------------------------------------------  
> [–] Up for: 51d 8h 43m 4s (1B q [307.867 qps], 7M conn, TX: 1609B, RX: 527B)  
> [–] Reads / Writes: 68% / 32%  
> [–] Total buffers: 5.4G global + 9.5M per thread (350 max threads)  
> [!!] Maximum possible memory usage: 8.7G (111% of installed RAM)  
> [OK] Slow queries: 0% (20K/1B)  
> [OK] Highest usage of available connections: 83% (293/350)  
> [OK] Key buffer size / total MyISAM indexes: 512.0M/549.3M  
> [OK] Key buffer hit rate: 99.9% (3B cached / 2M reads)  
> [OK] Query cache efficiency: 76.0% (855M cached / 1B selects)  
> [!!] Query cache prunes per day: 838139  
> [OK] Sorts requiring temporary tables: 0% (7K temp sorts / 58M sorts)  
> [OK] Temporary tables created on disk: 7% (6M on disk / 80M total)  
> [OK] Thread cache hit rate: 99% (12K created / 7M connections)  
> [OK] Table cache hit rate: 92% (3K open / 3K opened)  
> [OK] Open file limit used: 3% (1K/40K)  
> [OK] Table locks acquired immediately: 99% (708M immediate / 708M locks)  
> [!!] InnoDB data size / buffer pool: 6.8G/4.1G
> 
> -------- Recommendations -----------------------------------------------------  
> General recommendations:  
> Run OPTIMIZE TABLE to defragment tables for better performance  
> Reduce your overall MySQL memory footprint for system stability  
> Increasing the query\_cache size over 128M may reduce performance  
> Variables to adjust:  
> \*\*\* MySQL’s maximum memory usage is dangerously high \*\*\*  
> \*\*\* Add RAM before increasing MySQL buffer variables \*\*\*  
> query\_cache\_size (\> 768M) [see warning above]  
> innodb\_buffer\_pool\_size (\>= 6G)

Thanks, D.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 2, 2012, 6:09am UTC](https://forums.percona.com/t/mysql-determining-memory-requirements/1749/2 "2012-02-02T06:09:09Z")

</div>

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-](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.

---

<div class="post-metadata">

**Author:** ![danci1973](https://avatars.discourse-cdn.com/v4/letter/d/b19c9b/32.png) [@danci1973](https://forums.percona.com/u/danci1973)\
**Post date:** [February 2, 2012, 7:12am UTC](https://forums.percona.com/t/mysql-determining-memory-requirements/1749/3 "2012-02-02T07:12:43Z")

</div>

gmouse wrote on Thu, 02 February 2012 12:39

> [@](#):
>
> You say you run out of connections, but the MySQLTuner output shows that this has not happened in the last 50 days.

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:

> [@](#):
>
> 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.

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:

> [@](#):
>
> 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.

Isn’t that where more RAM should help?

Quote:

> [@](#):
>
> 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/](https://http) 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.

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

Thanks, D.