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.

Mysql tuning help

Discussion in 'Workarounds and Optimization' started by AlexCl, Feb 19, 2013.

  1. AlexCl

    AlexCl Active Member

    Joined:
    Feb 19, 2013
    Messages:
    40
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello everyone,

    I know this has been discussed over and over again, but having reached a problem, here I am posting :)

    I've tried to tune my.cnf as good as I could, but for over 2 weeks I've been having a lot of problems due to high %wa, which creates spikes in load. It's happening 2-3 times / day and takes around 20 minutes (the load goes up to 5.00) and then it goes back to normal (0.60-0.90). I'm assuming that mysql is the culprit, as I've checked for anything else (disk failure, DDOS attack, websites having too many visitors etc. ).

    So to start with server specs:
    AMD Opteron 8 physical cores
    16 GB RAM
    2x2.000 GB HDD's, 7.200 RPM, Software Raid 1
    Cloudlinux + Cpanel
    Around 60 websites hosted, maybe half of them on Joomla; Average bandwidth usage per month for the server: 200 GB (both incoming and outgoing)

    Current my.cnf:

    Code:
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    #skip-innodb
    query_cache_limit=64M
    query_cache_size=64M
    query_cache_type=1
    max_user_connections=100
    max_connections=150
    interactive_timeout=10
    wait_timeout=20
    connect_timeout=20
    thread_cache_size=128
    key_buffer=256M
    join_buffer=4M
    max_allowed_packet=128M
    table_cache=163840
    table_definition_cache=163840
    #record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=8M
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    server-id=1
    innodb_buffer_pool_size=1800M 
    innodb_file_per_table=1
    tmp_table_size=3G
    max_heap_table_size=3G
    low_priority_updates=1
    concurrent_insert=ALWAYS
    log-slow-queries=/var/lib/mysql/slow.log
    log-queries-not-using-indexes = /var/log/mysql-indexes.log
    
    #[mysql.server]
    #user=mysql
    #basedir=/var/lib 
    
    open_files_limit=50000
    [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
    
    Current mysqltuner results:

    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.5.30-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 3186)
    [--] Data in InnoDB tables: 250M (Tables: 8508)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 266)
    [!!] Total fragmented tables: 40
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 5h 15m 11s (6M q [34.844 qps], 157K conn, TX: 43B, RX: 1B)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 5.1G global + 14.5M per thread (150 max threads)
    [OK] Maximum possible memory usage: 7.2G (46% of installed RAM)
    [OK] Slow queries: 0% (169/6M)
    [OK] Highest usage of available connections: 26% (39/150)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/187.6M
    [OK] Key buffer hit rate: 99.9% (357M cached / 236K reads)
    [OK] Query cache efficiency: 82.0% (4M cached / 4M selects)
    [!!] Query cache prunes per day: 95307
    [OK] Sorts requiring temporary tables: 1% (2K temp sorts / 233K sorts)
    [!!] Joins performed without indexes: 3349
    [!!] Temporary tables created on disk: 38% (186K on disk / 479K total)
    [OK] Thread cache hit rate: 99% (39 created / 157K connections)
    [OK] Table cache hit rate: 32% (24K open / 74K opened)
    [OK] Open file limit used: 2% (9K/327K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 250.3M/1.8G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
    I've also noticed in WHM -> Server status that 82 % of swap is used, while over 8 GB of RAM memory was free (only 47 % used).


    If anyone can help me out I will be forever in your debt :)

    Thank you.
     
  2. AlexCl

    AlexCl Active Member

    Joined:
    Feb 19, 2013
    Messages:
    40
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I've identified the problem that was using the swap. It was fcgi and the tutorial on cloudlinux helped me fix it.

    Nevertheless I'd like a second opinion on my current my.cnf if anyone cares to have a look.

    Thanks.
     
Loading...

Share This Page