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 Help !

Discussion in 'Workarounds and Optimization' started by Bladecold, Aug 6, 2011.

  1. Bladecold

    Bladecold Registered

    Joined:
    Aug 7, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Hey guys, i'll need some help please.

    Code:
    >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at [URL]http://mysqltuner.com/[/URL]
    >> 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
    [!!] 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: 944M (Tables: 811)
    [--] Data in MEMORY tables: 3M (Tables: 19)
    [!!] Total fragmented tables: 41
    
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5h 30m 51s (852K q [42.923 qps], 26K conn, TX: 331M, RX: 304M)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 522.0M global + 15.2M per thread (500 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 7.9G (198% of installed RAM)
    [OK] Slow queries: 2% (20K/852K)
    [OK] Highest usage of available connections: 4% (20/500)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/364.9M
    [OK] Key buffer hit rate: 100.0% (2B cached / 88K reads)
    [OK] Query cache efficiency: 46.7% (332K cached / 711K selects)
    [!!] Query cache prunes per day: 597487
    [OK] Sorts requiring temporary tables: 0% (195 temp sorts / 98K sorts)
    [!!] Temporary tables created on disk: 43% (54K on disk / 126K total)
    [OK] Thread cache hit rate: 99% (20 created / 26K connections)
    [OK] Table cache hit rate: 89% (1K open / 1K opened)
    [OK] Open file limit used: 44% (1K/4K)
    [OK] Table locks acquired immediately: 99% (559K immediate / 560K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 64M)
    tmp_table_size (> 256M)
    max_heap_table_size (> 64M)
    
    
    [B]VPS my.cnf detail:[/B]
    
    #DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
    #Created with ELS from /http://www.servermonkeys.com
    #els-build=5.0
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    #skip-networking
    safe-show-database
    table_lock_wait_timeout=2
    query_cache_limit=2M
    query_cache_size=64M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=300
    max_connections=500
    interactive_timeout=10
    wait_timeout=30
    connect_timeout=20
    tmp_table_size=256M
    max_heap_table_size=64M
    thread_cache_size=64
    key_buffer=384M ## 128MB for every 1GB of RAM
    join_buffer=3M
    max_connect_errors=20
    max_allowed_packet=32M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M ## 1MB for every 1GB of RAM
    read_buffer_size=2M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=8M ## 1MB for every 1GB of RAM
    thread_concurrency=8 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    collation-server=latin1_general_ci
    set-variable=local-infile=0
    long_query_time=10
    log-queries-not-using-indexes
    log-slow-queries=/var/log/mysql-slow.log
    log-slow-queries=1
    
    [mysql.server]
    user=mysql
    
    [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
    #safe-updates
    
    [isamchk]
    key_buffer=256M
    sort_buffer_size=256M
    read_buffer=2M
    write_buffer=2M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    
    
    
    Running Xeon(R) CPU X3220 @ 2.40GHz with 4GB ram on VPS


    Total processors: 4
    Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
    Speed : 2400.086 MHz
    Cache: 4096 KB

    Top -c

    mysql cpu %82 or %60 or %50


    Thanks heaps!
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    What is it that you need?
     
  3. Bladecold

    Bladecold Registered

    Joined:
    Aug 7, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    top -c

    mysql cpu %82

    best config for my.cnf for that vps server please


    my.cnf detail

    Code:
    VPS my.cnf detail:
    
    #DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
    #Created with ELS from /http://www.servermonkeys.com
    #els-build=5.0
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    #skip-networking
    safe-show-database
    table_lock_wait_timeout=2
    query_cache_limit=2M
    query_cache_size=64M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=300
    max_connections=500
    interactive_timeout=10
    wait_timeout=30
    connect_timeout=20
    tmp_table_size=256M
    max_heap_table_size=64M
    thread_cache_size=64
    key_buffer=384M ## 128MB for every 1GB of RAM
    join_buffer=3M
    max_connect_errors=20
    max_allowed_packet=32M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M ## 1MB for every 1GB of RAM
    read_buffer_size=2M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=8M ## 1MB for every 1GB of RAM
    thread_concurrency=8 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    collation-server=latin1_general_ci
    set-variable=local-infile=0
    long_query_time=10
    log-queries-not-using-indexes
    log-slow-queries=/var/log/mysql-slow.log
    log-slow-queries=1
    
    [mysql.server]
    user=mysql
    
    [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
    #safe-updates
    
    [isamchk]
    key_buffer=256M
    sort_buffer_size=256M
    read_buffer=2M
    write_buffer=2M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
     
  4. Bladecold

    Bladecold Registered

    Joined:
    Aug 7, 2009
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    can any one help me
     
  5. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Have you tried viewing the MySQL Optimization presentation that was done last year?

    MySQL Optimization | cPanel Video Site

    You are welcome to view it and try to configure things on your own per the recommendations, then ask after you've attempted to reconfigure things.

    Basically, at this point in time, there are so many threads with people who have received help and recommendations that it is best to first check those threads, use the script recommendations, view the presentation and try to configure things. The point of the presentation was to teach people to do this on their own.
     
  6. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    The mysqltuner sample was only for 5 hrs... you need at least 24 hours, preferably 48hrs or more to make solid recommendations. However, these things stand out:

    • Highest usage of available connections: 4% (20/500) -- If, after allowing the server to run for 48hrs or so, this line remains small, you should reduce max_connections accordingly. max_connections=100 is probably plenty.
    • These lines:
      sort_buffer_size=2M ## 1MB for every 1GB of RAM
      read_buffer_size=2M ## 1MB for every 1GB of RAM
      read_rnd_buffer_size=8M ## 1MB for every 1GB of RAM

      Those are very much the wrong way to configure mysql, just assigning an arbitrary size based on RAM. I'd set these to 256K and work my way up while monitoring the server closely. Mysqltuner will tell you if any of these is too small... and sometimes it will even tell you they're too small when they're not ;)
    • thread_concurrency=8 ## Number of CPUs x 2 -- Unless you're using Solaris, this line does nothing.
    • query_cache_limit=2M -- This is probably too big since your query cache is only 64MB. Setting this to 1M might increase your cache hit rate.
    • thread_cache_size=64 -- Check what you max concurrent connections are after letting the server run for a few days. This doesn't need to be much larger than your max concurrent connections.
     
  7. syslint

    syslint Well-Known Member

    Joined:
    Oct 9, 2006
    Messages:
    249
    Likes Received:
    6
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    If you have a lot of search or sort options then sort_buffer_size =2 MB is very bad , you can decrease this to 256K or 512K .Also try to set mysql tmpdir to /dev/shm , which is ram
     
Loading...

Share This Page