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.

MySQLTuner Recommendations Help plzz

Discussion in 'Workarounds and Optimization' started by bobazrai, Dec 31, 2011.

  1. bobazrai

    bobazrai Registered

    Joined:
    Dec 31, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi everyone, I need help optimizing MySQL.

    I have a

    Code:
    [COLOR=#000000][FONT=verdana]Total processors: 4[/FONT][/COLOR]Processor #1VendorGenuineIntelNameIntel(R) Core(TM) i5 CPU 760 @ 2.80GHzSpeed2801.000 MHzCache8192 KBProcessor #2VendorGenuineIntelNameIntel(R) Core(TM) i5 CPU 760 @ 2.80GHzSpeed2801.000 MHzCache8192 KBProcessor #3VendorGenuineIntelNameIntel(R) Core(TM) i5 CPU 760 @ 2.80GHzSpeed2801.000 MHzCache8192 KBProcessor #4VendorGenuineIntelNameIntel(R) Core(TM) i5 CPU 760 @ 2.80GHzSpeed2801.000 MHzCache8192 KB
    
    
    Memory: 16454408k/17825792k available (11041k kernel code, 1057676k absent, 313708k reserved, 4307k data, 608k init)


    The contents of my my.conf file are the following:

    Code:
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    bind-address = 127.0.0.1
    

    MySQLTuner Output:

    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: 92M (Tables: 297)
    [--] Data in InnoDB tables: 7M (Tables: 28)
    [!!] Total fragmented tables: 8
    
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 11h 28m 29s (47M q [371.766 qps], 5M conn, TX: 29B, RX: 9B)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (8% of installed RAM)
    [OK] Slow queries: 0% (0/47M)
    [OK] Highest usage of available connections: 55% (277/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/54.4M
    [OK] Key buffer hit rate: 99.1% (1B cached / 13M reads)
    [!!] Query cache is disabled
    [!!] Sorts requiring temporary tables: 24% (839K temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 193714
    [OK] Temporary tables created on disk: 1% (16K on disk / 1M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 5M opened)
    [OK] Open file limit used: 4% (101/2K)
    [!!] Table locks acquired immediately: 94%
    [OK] InnoDB data size / buffer pool: 7.3M/8.0M
    
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
        Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
        query_cache_size (>= 8M)
        sort_buffer_size (> 1M)
        read_rnd_buffer_size (> 256K)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_cache (> 64)
    
    My top - c

    Code:
    20   0 1297m 134m 4580 S 104.5  0.8   1343:14 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid
    

    I'd like to know how to edit my my.conf file to reflect the recommended changes.

    If you need any other info, please let me know.

    Thanks in advanced, happy new year!
     
Loading...

Share This Page