MySQL - Determining memory requirements?

danci1973danci1973 EntrantCurrent User Role Participant
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:

<cite>SERVER 1:</cite>

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)

<cite>SERVER 2:</cite>

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.

Comments

  • gmousegmouse Mod Squad Inactive User Role Leader
    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.
  • danci1973danci1973 Entrant Current User Role Participant
    <cite>gmouse wrote on Thu, 02 February 2012 12:39</cite>
    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).

    <cite>Quote:</cite>
    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).

    <cite>Quote:</cite>
    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?

    <cite>Quote:</cite>
    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.
    So if I get another 8GB (16GB), then I'm really gonna be OK.

    Thanks, D.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.