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 is greedy - need some help

Discussion in 'Workarounds and Optimization' started by nobodyk, Apr 15, 2011.

  1. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    mysqltuner keeps asking me to increase the size of my veriables. I need help optimizing it.

    Server:
    i7 950 3GHz (8 cores)
    12GB DDR3
    RAID 10

    Also, is there things I can remove?
    Code:
    [mysqld]
    local-infile=0
    max_connections = 500
    max_user_connections = 50
    log_slow_queries = /var/log/mysql-slow.log
    long_query_time = 5
    max_allowed_packet = 32M
    query_cache_type=1
    key_buffer_size = 128M
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 68M
    thread_cache_size = 32
    query_cache_limit = 8M
    query_cache_size = 64M
    thread_concurrency = 8
    wait_timeout = 300
    interactive_timeout=300
    max_heap_table_size = 128M
    tmp_table_size = 128M
    table_cache = 4096
    join_buffer_size = 12M
    innodb_buffer_pool_size = 512M
    
    [mysqldump]
    quick
    max_allowed_packet = 32M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    
    Here's my my.cnf file
    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 13d 1h 46m 56s (19M q [17.329 qps], 647K conn, TX: 405B, RX: 4B)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 834.0M global + 24.2M per thread (500 max threads)
    [!!] Maximum possible memory usage: 12.7G (107% of installed RAM)
    [OK] Slow queries: 0% (15/19M)
    [OK] Highest usage of available connections: 3% (17/500)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/144.9M
    [OK] Key buffer hit rate: 100.0% (3B cached / 240K reads)
    [OK] Query cache efficiency: 67.3% (10M cached / 16M selects)
    [!!] Query cache prunes per day: 2880
    [OK] Sorts requiring temporary tables: 0% (34 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 40878
    [OK] Temporary tables created on disk: 7% (61K on disk / 799K total)
    [OK] Thread cache hit rate: 99% (17 created / 647K connections)
    [!!] Table cache hit rate: 18% (1K open / 8K opened)
    [OK] Open file limit used: 25% (2K/8K)
    [OK] Table locks acquired immediately: 99% (13M immediate / 13M locks)
    [OK] InnoDB data size / buffer pool: 496.0K/512.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
        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 ***
        query_cache_size (> 64M)
        join_buffer_size (> 12.0M, or always use indexes with joins)
        table_cache (> 4096)
    
     
  2. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    I made some adjustments. Need a second opinion.

    mysqltuner:
    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 298M (Tables: 564)
    [--] Data in MEMORY tables: 7M (Tables: 3)
    [!!] Total fragmented tables: 27
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9h 35m 59s (723K q [20.931 qps], 24K conn, TX: 13B, RX: 174M)
    [--] Reads / Writes: 80% / 20%
    [--] Total buffers: 330.0M global + 16.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 8.3G (70% of installed RAM)
    [OK] Slow queries: 0% (0/723K)
    [OK] Highest usage of available connections: 5% (25/500)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/138.1M
    [OK] Key buffer hit rate: 99.9% (218M cached / 190K reads)
    [OK] Query cache efficiency: 65.3% (395K cached / 604K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 49K sorts)
    [!!] Joins performed without indexes: 2517
    [OK] Temporary tables created on disk: 9% (3K on disk / 34K total)
    [OK] Thread cache hit rate: 99% (25 created / 24K connections)
    [OK] Table cache hit rate: 45% (675 open / 1K opened)
    [OK] Open file limit used: 14% (1K/8K)
    [OK] Table locks acquired immediately: 99% (500K immediate / 501K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 4.0M, or always use indexes with joins)
    
    my.cnf
    Code:
    [mysqld]
    skip-innodb
    max_connections = 500
    max_user_connections = 50
    log_slow_queries = /var/log/mysql-slow.log
    long_query_time = 5
    max_allowed_packet = 32M
    query_cache_type=1
    key_buffer_size = 128M
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 128
    query_cache_limit = 8M
    query_cache_size = 64M
    wait_timeout = 60
    interactive_timeout= 60
    max_heap_table_size = 128M
    tmp_table_size = 128M
    table_cache = 4096
    join_buffer_size = 4M
    
    [mysqldump]
    max_allowed_packet = 32M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    
     
  3. garrettp

    garrettp Well-Known Member
    PartnerNOC

    Joined:
    Jun 18, 2004
    Messages:
    312
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    Index your tables where possible, decrease your max_connections, and let MySQL run for at least 24 hours before running another mysqltuner check.
     
  4. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    24 hours later:
    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.91-community-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 303M (Tables: 564)
    [--] Data in MEMORY tables: 7M (Tables: 3)
    [!!] Total fragmented tables: 36
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 21h 47m 20s (3M q [22.027 qps], 120K conn, TX: 70B, RX: 830M)
    [--] Reads / Writes: 79% / 21%
    [--] Total buffers: 330.0M global + 16.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 8.3G (70% of installed RAM)
    [OK] Slow queries: 0% (0/3M)
    [OK] Highest usage of available connections: 5% (25/500)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/139.1M
    [OK] Key buffer hit rate: 100.0% (892M cached / 233K reads)
    [OK] Query cache efficiency: 66.6% (2M cached / 3M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234K sorts)
    [!!] Joins performed without indexes: 9496
    [OK] Temporary tables created on disk: 8% (14K on disk / 163K total)
    [OK] Thread cache hit rate: 99% (25 created / 120K connections)
    [OK] Table cache hit rate: 41% (1K open / 2K opened)
    [OK] Open file limit used: 19% (1K/8K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 4.0M, or always use indexes with joins)
    my.cnf
    Code:
    [mysqld]
    skip-innodb
    max_connections = 500
    max_user_connections = 50
    log_slow_queries = /var/log/mysql-slow.log
    long_query_time = 5
    max_allowed_packet = 32M
    query_cache_type=1
    key_buffer_size = 128M
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 128
    query_cache_limit = 8M
    query_cache_size = 64M
    wait_timeout = 60
    interactive_timeout= 60
    max_heap_table_size = 128M
    tmp_table_size = 128M
    table_cache = 4096
    join_buffer_size = 4M
    
    [mysqldump]
    max_allowed_packet = 32M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer_size = 4M
    read_buffer = 2M
    write_buffer = 2M
    The server is going to be db intensive, so I want to keep those 500 connections. I just think that I'm over compensating on some of the variables.
     
  5. garrettp

    garrettp Well-Known Member
    PartnerNOC

    Joined:
    Jun 18, 2004
    Messages:
    312
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    Looks sane for a box with a good amount of memory. Your queries aren't making very good use of the Qcache, but there's not much you can do if your clients are running 3rd party software.

    You may also want to periodically defrag your tables for a *slight* performance increase.
     
Loading...

Share This Page