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 zontrakulla, Apr 15, 2011.

  1. zontrakulla

    zontrakulla Active Member

    Joined:
    Jan 16, 2008
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6
    Hey guys, i'll need some help please.

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 552M (Tables: 12515)
    [--] Data in ARCHIVE tables: 19K (Tables: 4)
    [--] Data in MEMORY tables: 2M (Tables: 34)
    [!!] Total fragmented tables: 1259
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 1h 25m 28s (1M q [16.424 qps], 39K conn, TX: 1B, RX: 266M)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 298.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.9G (149% of installed RAM)
    [OK] Slow queries: 0% (17/1M)
    [OK] Highest usage of available connections: 2% (12/500)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/149.2M
    [OK] Key buffer hit rate: 99.8% (50M cached / 78K reads)
    [OK] Query cache efficiency: 55.7% (682K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (25 temp sorts / 88K sorts)
    [!!] Joins performed without indexes: 361
    [!!] Temporary tables created on disk: 33% (9K on disk / 29K total)
    [OK] Thread cache hit rate: 99% (12 created / 39K connections)
    [!!] Table cache hit rate: 0% (1K open / 162K opened)
    [OK] Open file limit used: 77% (1K/2K)
    [OK] Table locks acquired immediately: 99% (764K immediate / 764K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        table_cache (> 1024)
    Code:
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=8M
    query_cache_size=256M
    query_cache_type=1
    max_connections=500
    max_user_connections=100
    interactive_timeout=60
    wait_timeout=60
    connect_timeout=30
    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=100
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    #log-bin
    server-id=1
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    Running Intel CPU 2.40GHz with 2GB ram

    Thanks heaps!
     
  2. LaceHost-Ishan

    LaceHost-Ishan Active Member

    Joined:
    Dec 6, 2008
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Pune, India
    cPanel Access Level:
    DataCenter Provider
    I would reduce the connect, wait and interactive timeout to around 10 seconds.

    Also run mysqlcheck -o -A to optimize all databases.

    My last recommendation will be adding RAM to the server or reducing the variables. As long as mysql is performing fine, you do not need to over-tune it.
     
  3. 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
    There are some deprecated or inapplicable settings in your /etc/my.cnf file. I've removed those that shouldn't be in there:

    Code:
    [mysqld]
    skip-innodb
    query_cache_limit=8M
    query_cache_size=256M
    query_cache_type=1
    max_connections=500
    max_user_connections=100
    interactive_timeout=60
    wait_timeout=60
    connect_timeout=30
    thread_cache_size=128
    key_buffer_size=16M
    join_buffer_size=1M
    max_allowed_packet=16M
    table_cache=1024
    record_buffer_size=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=100
    myisam_sort_buffer_size=64M
    server-id=1
    
    [mysqldump]
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    Here is a synopsis of what I changed or removed. I removed skip-locking as it's default and called skip-external-locking now. If something is the default, it doesn't need set in /etc/my.cnf file. I removed thread_concurrency as it doesn't exist on Linux or FreeBSD systems. It only works for Solaris. I removed quick for [mysqldump] section as that is the default setting. I changed the key_buffer, join_buffer and record_buffer entries to be the correct key_buffer_size, join_buffer_size and record_buffer_size entries.

    I would suggest trying to not use a template for the settings going forward and starting with only those you actually need. By using a template such as appears to have been the case, you end up putting variables that aren't valid into the configuration.

    Of note, I didn't change any of the variable sizes, since you need to have the correct ones to start out with before proceeding. I'd now suggest re-running mysqltuner in 24 hours and then using what it directs to change. I would not change the join_buffer_size from the default regardless of what it recommends, and you need to ensure to keep tmp_table_size and max_heap_table_size at the same size.

    If you want to simply yank out all the variables you have and then start from scratch, that might be a better idea given you are hitting max memory constraints due to increasing the variables beyond what your system can handle, which is causing the alert mysqltuner is indicating.
     
Loading...

Share This Page