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.

Help me to optimize MySQL

Discussion in 'Workarounds and Optimization' started by theway007, Aug 12, 2013.

  1. theway007

    theway007 Member

    Joined:
    Nov 20, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    I try to play around with the value but it ask for more and more. Could anyone help me to see the mysqltuner value below? It tooks me hour to install vtiger just inserting database.

    PHP:
     >>  MySQLTuner 1.2.0 Major Hayden <major@mhtx.net>
     >>  
    Bug reportsfeature requests, and downloads at http://mysqltuner.com/
     
    >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.70-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables414M (Tables3217)
    [--] 
    Data in InnoDB tables25M (Tables297)
    [--] 
    Data in MEMORY tables248K (Tables18)
    [!!] 
    Total fragmented tables335

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 59m 46s (60K q [16.982 qps], 1K connTX97MRX9M)
    [--] 
    Reads Writes77% / 23%
    [--] 
    Total buffers1.8G global + 4.6M per thread (150 max threads)
    [
    OKMaximum possible memory usage2.4G (60of installed RAM)
    [
    OKSlow queries0% (78/60K)
    [
    OKHighest usage of available connections27% (41/150)
    [
    OKKey buffer size total MyISAM indexes256.0M/221.3M
    [!!] Key buffer hit rate83.3% (170K cached 28K reads)
    [!!] 
    Query cache efficiency18.7% (7K cached 37K selects)
    [
    OKQuery cache prunes per day0
    [OKSorts requiring temporary tables0% (0 temp sorts 7K sorts)
    [!!] 
    Joins performed without indexes943
    [!!] Temporary tables created on disk29% (1K on disk 4K total)
    [!!] 
    Thread cache hit rate43% (1K created 1K connections)
    [!!] 
    Table cache hit rate0% (1K open 225K opened)
    [
    OKOpen file limit used7% (1K/21K)
    [
    OKTable locks acquired immediately99% (55K immediate 55K locks)
    [
    OKInnoDB data size buffer pool25.5M/512.0M

    -------- 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
        Temporary table size is already large 
    reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust
    :
        
    query_cache_limit (> 2M, or use smaller result sets)
        
    join_buffer_size (> 2.0M, or always use indexes with joins)
        
    thread_cache_size (> 128)
        
    table_cache (> 512)
     
  2. theway007

    theway007 Member

    Joined:
    Nov 20, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    I have reduce the max connection to 80 and it gives a better value:

    PHP:
     >>  MySQLTuner 1.2.0 Major Hayden <major@mhtx.net>
     >>  
    Bug reportsfeature requests, and downloads at http://mysqltuner.com/
     
    >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.70-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables409M (Tables3217)
    [--] 
    Data in InnoDB tables25M (Tables297)
    [--] 
    Data in MEMORY tables372K (Tables18)
    [!!] 
    Total fragmented tables303

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 1h 9m 23s (73K q [17.549 qps], 2K connTX108MRX11M)
    [--] 
    Reads Writes75% / 25%
    [--] 
    Total buffers1.8G global + 10.6M per thread (80 max threads)
    [
    OKMaximum possible memory usage2.6G (64of installed RAM)
    [
    OKSlow queries0% (78/73K)
    [
    OKHighest usage of available connections51% (41/80)
    [
    OKKey buffer size total MyISAM indexes256.0M/219.5M
    [!!] Key buffer hit rate80.9% (239K cached 45K reads)
    [
    OKQuery cache efficiency26.6% (12K cached 45K selects)
    [
    OKQuery cache prunes per day0
    [OKSorts requiring temporary tables0% (0 temp sorts 7K sorts)
    [!!] 
    Joins performed without indexes951
    [!!] Temporary tables created on disk27% (1K on disk 4K total)
    [
    OKThread cache hit rate55% (1K created 2K connections)
    [!!] 
    Table cache hit rate0% (4 open 296K opened)
    [
    OKOpen file limit used0% (9/21K)
    [
    OKTable locks acquired immediately99% (61K immediate 61K locks)
    [
    OKInnoDB data size buffer pool25.5M/512.0M

    -------- 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
        Temporary table size is already large 
    reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust
    :
        
    join_buffer_size (> 8.0M, or always use indexes with joins)
        
    table_cache (> 4)
     
  3. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You should let it run for 24 hours to get a better idea.
     
  4. theway007

    theway007 Member

    Joined:
    Nov 20, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    it ran for 24 hours.

    PHP:
    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.70-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables506M (Tables3280)
    [--] 
    Data in InnoDB tables31M (Tables667)
    [--] 
    Data in MEMORY tables248K (Tables18)
    [!!] 
    Total fragmented tables708

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 1d 0h 2m 17s (782K q [9.046 qps], 21K connTX1BRX137M)
    [--] 
    Reads Writes74% / 26%
    [--] 
    Total buffers538.0M global + 2.7M per thread (500 max threads)
    [
    OKMaximum possible memory usage1.9G (46of installed RAM)
    [
    OKSlow queries0% (2/782K)
    [
    OKHighest usage of available connections1% (8/500)
    [
    OKKey buffer size total MyISAM indexes8.0M/248.8M
    [OKKey buffer hit rate95.3% (6M cached 290K reads)
    [!!] 
    Query cache is disabled
    [OKSorts requiring temporary tables0% (25 temp sorts 81K sorts)
    [!!] 
    Joins performed without indexes4246
    [!!] Temporary tables created on disk28% (19K on disk 69K total)
    [!!] 
    Thread cache is disabled
    [!!] Table cache hit rate0% (64 open 2M opened)
    [
    OKOpen file limit used0% (123/21K)
    [
    OKTable locks acquired immediately99% (781K immediate 781K locks)
    [
    OKInnoDB data size buffer pool31.7M/512.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        
    Run OPTIMIZE TABLE to defragment tables for better performance
        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
        Set thread_cache_size to 4 
    as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust
    :
        
    query_cache_size (>= 8M)
        
    join_buffer_size (> 128.0K, or always use indexes with joins)
        
    tmp_table_size (> 16M)
        
    max_heap_table_size (> 16M)
        
    thread_cache_size (start at 4)
        
    table_cache (> 64)
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    add to my.cnf (btw. before posting the mysqltuner you should also post your current my.cnf)

    query_cache_type = 1
    query_cache_size = 20M
    query_cache_limit = 1M

    thread_cache_size = 50

    key_buffer_size = 350M
    table_open_cache = 2048
     
  6. theway007

    theway007 Member

    Joined:
    Nov 20, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    This is the reason it ask for more and more:

    PHP:
    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.1.70-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables509M (Tables3294)
    [--] 
    Data in InnoDB tables31M (Tables667)
    [--] 
    Data in MEMORY tables372K (Tables18)
    [!!] 
    Total fragmented tables708

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 23h 13m 2s (426K q [5.107 qps], 17K connTX811MRX62M)
    [--] 
    Reads Writes75% / 25%
    [--] 
    Total buffers900.0M global + 2.7M per thread (200 max threads)
    [
    OKMaximum possible memory usage1.4G (35of installed RAM)
    [
    OKSlow queries0% (0/426K)
    [
    OKHighest usage of available connections3% (6/200)
    [
    OKKey buffer size total MyISAM indexes350.0M/251.5M
    [!!] Key buffer hit rate78.6% (1K cached 428 reads)
    [
    OKQuery cache efficiency65.9% (194K cached 295K selects)
    [!!] 
    Query cache prunes per day50869
    [OKSorts requiring temporary tables0% (19 temp sorts 12K sorts)
    [!!] 
    Joins performed without indexes477
    [OKTemporary tables created on disk23% (10K on disk 42K total)
    [!!] 
    Thread cache hit rate1% (16K created 17K connections)
    [!!] 
    Table cache hit rate0% (2K open 2M opened)
    [
    OKOpen file limit used16% (3K/21K)
    [
    OKTable locks acquired immediately99% (161K immediate 161K locks)
    [
    OKInnoDB data size buffer pool31.7M/512.0M

    -------- 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
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust
    :
        
    query_cache_size (> 20M)
        
    join_buffer_size (> 128.0K, or always use indexes with joins)
        
    thread_cache_size (> 50)
        
    table_cache (> 2048)
    This is the latest my.cnf:

    PHP:
    [mysqld]
    set-variable max_connections=200
    log
    -slow-queries
    safe
    -show-database
    open_files_limit
    =21554
    innodb_buffer_pool_size
    =512M
    query_cache_size
    =8M
    query_cache_limit
    =2M
    thread_cache_size
    =4
    key_buffer_size
    =350M
    query_cache_size
    =20M
    thread_cache_size
    =50
    table_cache
    =2048
    query_cache_size
    =1M
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you can remove those
    query_cache_size=8M
    query_cache_limit=2M
    thread_cache_size=4

    since they are doubling

    and you can add slow queries tracking
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes


    after that restart mysql

    the rest is good
     
Loading...

Share This Page