The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Any optimization tips for this machine?

Discussion in 'Workarounds and Optimization' started by johnburk, Jul 22, 2011.

  1. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    It is a quad-core with 8gb ram and mysqltuner gives:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 377M (Tables: 1881)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 2
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15d 9h 11m 5s (92M q [69.794 qps], 1M conn, TX: 409B, RX: 19B)
    [--] Reads / Writes: 80% / 20%
    [--] Total buffers: 422.0M global + 16.8M per thread (320 max threads)
    [OK] Maximum possible memory usage: 5.6G (72% of installed RAM)
    [OK] Slow queries: 0% (4/92M)
    [OK] Highest usage of available connections: 38% (123/320)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/111.5M
    [OK] Key buffer hit rate: 99.9% (1B cached / 2M reads)
    [OK] Query cache efficiency: 81.9% (66M cached / 81M selects)
    [!!] Query cache prunes per day: 237415
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 7283
    [OK] Temporary tables created on disk: 24% (598K on disk / 2M total)
    [OK] Thread cache hit rate: 99% (123 created / 1M connections)
    [!!] Table cache hit rate: 0% (283 open / 44K opened)
    [OK] Open file limit used: 0% (523/65K)
    [OK] Table locks acquired immediately: 98% (18M immediate / 19M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/4.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 96M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        table_cache (> 81920)
    

    Even if I set the query_cache to 512M and table_cache to 256k, it still wants more!

    This is my.cnf

    Code:
    [mysqld]
    bulk_insert_buffer_size=8M
    innodb_buffer_pool_size=4M
    interactive_timeout=600
    join_buffer_size=2M
    key_buffer_size=256M
    local-infile=0
    log-slow-queries
    max_allowed_packet=32M
    max_heap_table_size=64M
    myisam_sort_buffer_size=64M
    open_files_limit=32K
    query_alloc_block_size=131072
    query_cache_limit=32M
    query_cache_min_res_unit=512
    query_cache_size=96M
    query_cache_type=1
    query_prealloc_size=65536
    read_buffer_size=6M
    read_rnd_buffer_size=512K
    safe-show-database
    set-variable = max_connections=320
    sort_buffer_size=8M
    table_cache=80k
    thread_cache_size=256
    tmp_table_size=64M
    wait_timeout=600
    log-error=/var/log/mysql/error.log
    long_query_time=1
    log-slow-queries=/var/log/mysql/mysql-slow.log
    
    
    [isamchk]
    key_buffer_size = 96M
    sort_buffer_size = 32M
    read_buffer_size = 12M
    write_buffer_size = 12M
    
    [myisamchk]
    key_buffer_size = 96M
    sort_buffer_size = 32M
    read_buffer_size = 12M
    write_buffer_size = 12M
    
    Any suggestions?
     
Loading...

Share This Page