•  

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL - Determining memory requirements?

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL - Determining memory requirements?

    #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:
    -------- 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.
    Tags: None

  • #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.

    Comment


    • #3
      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/ 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.

      Comment


      • #4
        danci1973 wrote on Thu, 02 February 2012 13:42
        I don't know why but occasionally one of the applications (Magento) that talks to 'server 2' spawns a lot of connections.
        And what happens on 'server 2' at that time?
        High CPU? High IO? High memory utilization which _results_in_swapping_? Because you can have high mem utilization as a normal state since Linux normally like to use all left over RAM as OS file cache (cached in top)? Any queries that has been running for a long time? Etc..

        Unless you know what happens at that critical moment you have no idea if it is the app-server that is going crazy and just issuing a lot of new connections or if that is actually normal connection tempo and the db server is dragging behind at which point you should see it in

        Either way you need to get monitoring on this machine up so that you have a clue about what goes on at that moment.

        danci1973 wrote on Thu, 02 February 2012 13:42
        I tweaked caching parameters to make more room for connections, so I could increased those to 350 (on server 2).
        This can also hurt you if your server is under load at the time since if the growing rate of connections is due to that MySQL aren't able to service questions fast enough then allowing more connections in is usually a bad idea.

        danci1973 wrote on Thu, 02 February 2012 13:42
        Quote:Isn't that where more RAM should help?
        Yes and No, yes more RAM might reduce the amount of tmp tables but only if you increase the memory that you allow for tmp tables and at the same time if you have _one_ badly written query on large tables that performs a cross join you could end up using so much memory that additional RAM won't really help you (since it will also still use a lot of CPU to handle the join).
        And you have some queries that always creates tmp tables at a join usually due to using BLOB or CLOB data types in the query.
        [/quote]

        danci1973 wrote on Thu, 02 February 2012 13:42
        Quote:So if I get another 8GB (16GB), then I'm really gonna be OK.
        I concur with danci1973 that I don't think that you really have a memory problem, since the 11% overbooking sounds very small and I would say not too uncommon.

        I would much more want to know if your system swaps during these peaks and/or if you have CPU.

        And if the DB server doesn't look more stressed than normal and you still get a lot of dropped connections then I would place my money on that the application server database connection pooling is doing something fishy instead.

        So no I don't feel that it's likely that buying 8GB more would solve this problem for you (although getting more can of course not hurt but I would start with placing the machine under monitoring so that you know what's going on before getting more memory since I think that you would benefit more from it).

        Good Luck!

        Comment

        Working...
        X