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

    Did you restart MySQL after your changes?
     
  3. Dimass

    Dimass New Member

    Every time... :(
     
: mysql tuning

Share This Page