MySQL: Mem hog, MySQLtuner after 72hrs.

Discussion in 'General' started by indiadamjones, Aug 6, 2014.

  1. indiadamjones

    indiadamjones Member

    Hi all, I'm wondering if anyone has any tips for my MySQL settings. Here is the mysqltuner readout after 72hrs. of continuous uptime. Monit shows almost 20% or ~400mb of ram for MySQL, isn't that a little high? Please feel free to ask me for any further details, and please don't hesitate to point out anything that catches your eye. I'm still quite new to this, but learning quickly.

    Thank you,
    Adam

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.34-0ubuntu0.13.04.1-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 119M (Tables: 818)
    [--] Data in InnoDB tables: 219M (Tables: 1838)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 36)
    [!!] Total fragmented tables: 1850
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User '@Megabotix' has no password set.
    [!!] User '@localhost' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 2h 39m 12s (1M q [5.723 qps], 37K conn, TX: 5B, RX: 600M)
    [--] Reads / Writes: 41% / 59%
    [--] Total buffers: 332.0M global + 2.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 594.5M (29% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 17% (17/100)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/11.4M
    [OK] Key buffer hit rate: 99.4% (954K cached / 5K reads)
    [OK] Query cache efficiency: 79.6% (812K cached / 1M selects)
    [!!] Query cache prunes per day: 9798
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 61K sorts)
    [!!] Joins performed without indexes: 44607
    [!!] Temporary tables created on disk: 34% (58K on disk / 170K total)
    [OK] Thread cache hit rate: 99% (373 created / 37K connections)
    [!!] Table cache hit rate: 0% (140 open / 22K opened)
    [OK] Open file limit used: 4% (41/1K)
    [OK] Table locks acquired immediately: 100% (775K immediate / 775K locks)
    [OK] InnoDB data size / buffer pool: 219.9M/220.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 140)
    
     
  2. srijan

    srijan New Member HowtoForge Supporter

    Hi Adam,
    As you stated in your previous thread that you have increased your RAM, does it helped in stabilizing the RAM usage?
     
    Last edited: Aug 6, 2014
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    $00MB for mysql is not that high. Sppeding up mysql means to cache data in ram and this results in a higher ram consumption. the more memory you can give to mysql, the faster it will get. Off course there is a limit for that as well when all data is in ram, but 400MB are ok in my opinion.

    I think you should try t increase tmp_table-size to e.g. 32mb or 64mb.
     

Share This Page