Tunning MySQL (mysqltuner.pl)

Discussion in 'Server Operation' started by Dimass, Feb 15, 2011.

  1. Dimass

    Dimass New Member

    Hi,

    I want to optimize a MySQL server with 2CPUs and 4Gb of RAM, shared with a Apache service.

    The MySQL have abut 1500 tables (10% InnoDB).

    The my.cnf settings:
    Code:
    innodb_buffer_pool_size = 64M
    join_buffer_size        = 512K
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    myisam-recover         = BACKUP
    #max_connections        = 100
    table_cache            = 200
    #thread_concurrency     = 10
    query_cache_limit       = 1M
    query_cache_size        = 32M
    query_cache_type        = 1


    The mysqltuner.pl script output this:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.49-1ubuntu8.1-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 200M (Tables: 1294)
    [--] Data in InnoDB tables: 35M (Tables: 194)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 207
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4m 27s (30K q [114.536 qps], 304 conn, TX: 232M, RX: 5M)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 130.0M global + 3.1M per thread (151 max threads)
    [OK] Maximum possible memory usage: 592.4M (14% of installed RAM)
    [OK] Slow queries: 0% (169/30K)
    [OK] Highest usage of available connections: 7% (12/151)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/62.4M
    [OK] Key buffer hit rate: 98.7% (765K cached / 9K reads)
    [OK] Query cache efficiency: 93.6% (26K cached / 28K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 214 sorts)
    [!!] Joins performed without indexes: 2
    [OK] Temporary tables created on disk: 23% (67 on disk / 283 total)
    [OK] Thread cache hit rate: 96% (12 created / 304 connections)
    [!!] Table cache hit rate: 2% (200 open / 9K opened)
    [OK] Open file limit used: 37% (385/1K)
    [OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
    [OK] InnoDB data size / buffer pool: 35.5M/64.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 200)
    I've incremented join_buffer_size and table_cache but keep throwing these advices. I don't like the "Table cache hit rate: 2% (200 open / 9K opened)".

    Any advice to optimize the MySQl config?

    Thx
     
  2. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Did you restart MySQL after your changes?
     
  3. Dimass

    Dimass New Member

    Every time... :(
     
: mysql tuning

Share This Page