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

Discussion in 'Workarounds and Optimization' started by Gibby, Jul 25, 2013.

  1. Gibby

    Gibby Member

    Joined:
    Feb 24, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    I have been using mysqltuner.pl and reading through the forums and other blogs trying to get mysql tweaked just right for our servers.

    The server I am testing on has 4G RAM, 4G Swap and 4x 2.1Ghz CPU's.

    my.cnf
    Code:
    [mysqld]
    local-infile=0                  # security tweak
    
    max_connections         = 250   # cPanel default is 500
    max_allowed_packet      = 16M   # Default 1M
    
    slow-query-log                  # because enquiring minds want to know.
    log-slow-queries        = /var/lib/mysql/slow.log # Where to log slow queries
    long-query-time         = 10     # Default 10
    
    ######### Performance Tweaks ##########
    
    thread_cache_size       = 4     # Default 0
    table_cache             = 1024  # Default 64
    
    key_buffer_size         = 1024M # Default 8M
    sort_buffer_size        = 256K  # Default 2M
    join_buffer_size        = 512K  # Default 128K
    read_buffer_size        = 256K  # Default 128K.
    
    query_cache_size        = 16M   # Default 0
    
    ######### End Performance Tweaks #######
    open_files_limit=8030
    
    mysqltuner.pl
    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.70-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 408M (Tables: 1190)
    [--] Data in InnoDB tables: 7M (Tables: 331)
    [--] Data in MEMORY tables: 0B (Tables: 12)
    [!!] Total fragmented tables: 369
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11d 1h 21m 8s (1M q [1.080 qps], 93K conn, TX: 1B, RX: 189M)
    [--] Reads / Writes: 49% / 51%
    [--] Total buffers: 1.0G global + 1.5M per thread (250 max threads)
    [OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
    [OK] Slow queries: 0% (1/1M)
    [OK] Highest usage of available connections: 3% (8/250)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/176.1M
    [OK] Key buffer hit rate: 96.2% (9M cached / 371K reads)
    [OK] Query cache efficiency: 70.1% (370K cached / 528K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (111 temp sorts / 41K sorts)
    [!!] Joins performed without indexes: 15222
    [OK] Temporary tables created on disk: 16% (12K on disk / 73K total)
    [OK] Thread cache hit rate: 99% (33 created / 93K connections)
    [!!] Table cache hit rate: 0% (1K open / 535K opened)
    [OK] Open file limit used: 21% (1K/8K)
    [OK] Table locks acquired immediately: 99% (407K immediate / 407K locks)
    [OK] InnoDB data size / buffer pool: 7.0M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 1024)
    


    Any input would be appreciated!

    Thanks.
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    its quite ok, you have not many queries

    just add some code to track slow queries, those not using indexes and those using temp tables
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
     
  3. Gibby

    Gibby Member

    Joined:
    Feb 24, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Is that correct? 0.1 seconds?
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    yes, to track all queries running longer than 0.1s
    and the ones not using indexes
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,830
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  6. Gibby

    Gibby Member

    Joined:
    Feb 24, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    How many days worth of data should we wait for?
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    24h is great
     
  8. Gibby

    Gibby Member

    Joined:
    Feb 24, 2012
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    I know it has been longer than 24h, still going to post the output though. Thanks in advance!

    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.33-MariaDB-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 446M (Tables: 1328)
    [--] Data in MRG_MYISAM tables: 87M (Tables: 16)
    [--] Data in InnoDB tables: 7M (Tables: 341)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 12)
    [!!] Total fragmented tables: 85
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 24d 7h 28m 34s (38M q [18.168 qps], 1M conn, TX: 34B, RX: 4B)
    [--] Reads / Writes: 85% / 15%
    [--] Total buffers: 1.2G global + 1.5M per thread (250 max threads)
    [OK] Maximum possible memory usage: 1.5G (38% of installed RAM)
    [OK] Slow queries: 0% (276K/38M)
    [OK] Highest usage of available connections: 12% (32/250)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/108.6M
    [OK] Key buffer hit rate: 96.9% (41M cached / 1M reads)
    [OK] Query cache efficiency: 91.4% (28M cached / 31M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 653K sorts)
    [!!] Joins performed without indexes: 23734
    [OK] Temporary tables created on disk: 12% (103K on disk / 818K total)
    [OK] Thread cache hit rate: 99% (3K created / 1M connections)
    [!!] Table cache hit rate: 0% (1K open / 12M opened)
    [OK] Open file limit used: 21% (1K/8K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 7.6M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 512.0K, or always use indexes with joins)
        table_cache (> 1024)
    
     
Loading...

Share This Page