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.

Help with optimizing mySQL

Discussion in 'Workarounds and Optimization' started by afs2, Jul 13, 2012.

  1. afs2

    afs2 Registered

    Joined:
    Jul 13, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    MySQL settings
    Code:
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_user_connections=25
    max_connections=500
    interactive_timeout=10
    connect_timeout=10
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    server-id=1
    
    #[mysql.server]
    #user=mysql
    #basedir=/var/lib 
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192 
    
    #[mysqldump]
    #quick
    #max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M 
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld]
    ft_min_word_len = 2
    
    
    wait_timeout = 600
    MySQLTuner report
    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.1.63-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 760M (Tables: 527)
    [--] Data in MEMORY tables: 0B (Tables: 1)
    [!!] Total fragmented tables: 25
    
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3d 2h 29m 51s (3M q [14.827 qps], 164K conn, TX: 112B, RX: 1B)
    [--] Reads / Writes: 64% / 36%
    [--] Total buffers: 64.0M global + 5.4M per thread (500 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.7G (68% of installed RAM)
    [OK] Slow queries: 0% (80/3M)
    [OK] Highest usage of available connections: 5% (29/500)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/166.2M
    [OK] Key buffer hit rate: 99.6% (210M cached / 910K reads)
    [OK] Query cache efficiency: 61.8% (1M cached / 2M selects)
    [!!] Query cache prunes per day: 44448
    [OK] Sorts requiring temporary tables: 7% (3K temp sorts / 50K sorts)
    [OK] Temporary tables created on disk: 4% (7K on disk / 165K total)
    [OK] Thread cache hit rate: 99% (29 created / 164K connections)
    [OK] Table cache hit rate: 47% (864 open / 1K opened)
    [OK] Open file limit used: 17% (1K/8K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
     Run OPTIMIZE TABLE to defragment tables for better performance
     Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
     query_cache_size (> 32M)
    
    Thank you in advance
     
Loading...

Share This Page