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.

Output from MySQL Tuner

Discussion in 'Workarounds and Optimization' started by morteza3245, Jul 26, 2014.

  1. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello,
    i get below output at Mysqltuner

    anyone can explain about each line?
    and how Resolve them?

    any help will appreciated.

    Thanks.
     
  2. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    some question about mysqltuner

    Hello

    in my my.cnf value is below


    if possible, please explain me a little about each value.
    any help will appreciated.


    Thank you.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    669
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: some question about mysqltune

    Hello :)

    You may want to post the full output from the tuner script, as that might help other users offer feedback.

    Thank you.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    669
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    Hello :)

    I suggest posting the full output from the MySQL tuner script that you used, as that will likely help other users offer more feedback.

    Thank you.
     
  5. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    Hello
    below attached

    please explain me

    Code:
    root@host [~]# /usr/local/bin/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/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.5.37-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 5G (Tables: 13837)
    [--] Data in InnoDB tables: 253M (Tables: 1196)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 498K (Tables: 50)
    [!!] Total fragmented tables: 1410
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'urflight_totnoi@188.40.89.145' has no password set.
    [!!] User 'urflight_totnoi@host.almashosting.com' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9m 0s (195K q [362.757 qps], 2K conn, TX: 5B, RX: 19M)
    [--] Reads / Writes: 63% / 37%
    [--] Total buffers: 5.2G global + 5.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 6.0G (12% of installed RAM)
    [OK] Slow queries: 0% (0/195K)
    [OK] Highest usage of available connections: 12% (19/151)
    [OK] Key buffer size / total MyISAM indexes: 5.0G/863.8M
    [OK] Key buffer hit rate: 99.9% (12M cached / 10K reads)
    [OK] Query cache efficiency: 76.7% (125K cached / 163K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 9K sorts)
    [!!] Joins performed without indexes: 40
    [!!] Temporary tables created on disk: 39% (1K on disk / 2K total)
    [OK] Thread cache hit rate: 99% (19 created / 2K connections)
    [OK] Table cache hit rate: 98% (812 open / 827 opened)
    [OK] Open file limit used: 1% (1K/100K)
    [OK] Table locks acquired immediately: 98% (48K immediate / 48K locks)
    [!!] InnoDB data size / buffer pool: 253.4M/128.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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
        innodb_buffer_pool_size (>= 253M)
    
     
  6. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: some question about mysqltune

    Hello
    below attached

    please explain me

    Code:
    root@host [~]# /usr/local/bin/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/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.5.37-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 5G (Tables: 13837)
    [--] Data in InnoDB tables: 253M (Tables: 1196)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 498K (Tables: 50)
    [!!] Total fragmented tables: 1410
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'urflight_totnoi@188.40.89.145' has no password set.
    [!!] User 'urflight_totnoi@host.almashosting.com' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9m 0s (195K q [362.757 qps], 2K conn, TX: 5B, RX: 19M)
    [--] Reads / Writes: 63% / 37%
    [--] Total buffers: 5.2G global + 5.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 6.0G (12% of installed RAM)
    [OK] Slow queries: 0% (0/195K)
    [OK] Highest usage of available connections: 12% (19/151)
    [OK] Key buffer size / total MyISAM indexes: 5.0G/863.8M
    [OK] Key buffer hit rate: 99.9% (12M cached / 10K reads)
    [OK] Query cache efficiency: 76.7% (125K cached / 163K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 9K sorts)
    [!!] Joins performed without indexes: 40
    [!!] Temporary tables created on disk: 39% (1K on disk / 2K total)
    [OK] Thread cache hit rate: 99% (19 created / 2K connections)
    [OK] Table cache hit rate: 98% (812 open / 827 opened)
    [OK] Open file limit used: 1% (1K/100K)
    [OK] Table locks acquired immediately: 98% (48K immediate / 48K locks)
    [!!] InnoDB data size / buffer pool: 253.4M/128.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
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
        innodb_buffer_pool_size (>= 253M)
    
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    669
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    You should let MySQL run for at least 24 hours before running the tuner to ensure accurate results. Note that your duplicate threads have been merged.

    Thank you.
     
  8. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    after 24H, please take a look.

     
  9. morteza3245

    morteza3245 Well-Known Member

    Joined:
    Sep 16, 2013
    Messages:
    104
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    any update ?:(
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    669
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Re: output at MYsql Tuner

    Please keep in mind that optimization advice is not something that cPanel officially supports. You may need to consult with a qualified system administrator if your thread does not receive user-feedback. Note the output you provided shows only 11 hours of uptime for MySQL, not 24 hours.

    Thank you.
     
Loading...

Share This Page