Skip to main content

Output from MySQL Tuner

morteza3245
Hello, i get below output at Mysqltuner [QUOTE]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 Increase table_cache gradually to avoid file descriptor limits
anyone can explain about each line? and how Resolve them? any help will appreciated. Thanks.

Comments

9 comments

Date Votes
  • morteza3245
    some question about mysqltuner Hello in my my.cnf value is below [QUOTE]query_cache_size=50M but mysqltune tell me below query_cache_size (> 50M) it means i increase it how much?
    [QUOTE]query_cache_limit=1M but mysqltune tell me below query_cache_limit (> 1M, or use smaller result sets) how much i decrease it?
    [QUOTE]join_buffer_size=1M but mysqltune tell me below: join_buffer_size (> 1.0M, or always use indexes with joins) how much increase and what is means of "or always use indexes with joins"
    [QUOTE]tmp_table_size=50M but mysqltune tell me below: tmp_table_size (> 50M) how much i increase
    [QUOTE]max_heap_table_size=50M but mysqltune tell me below max_heap_table_size (> 50M) how much increase?
    [QUOTE]i has not any value in my.cnf about "table_cache" but mysql tuner tell me below table_cache (> 5000) what value i put in my.cnf ?
    [QUOTE]i has not any value at my.cnf for "innodb_buffer_pool_size" but mysqltune tell me below innodb_buffer_pool_size (>= 255M) what value i should put?
    if possible, please explain me a little about each value. any help will appreciated. Thank you.
    0
  • cPanelMichael
    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.
    0
  • cPanelMichael
    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.
    0
  • morteza3245
    Re: output at MYsql Tuner Hello below attached please explain me root@host [~]# /usr/local/bin/mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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)
    0
  • morteza3245
    Re: some question about mysqltune Hello below attached please explain me root@host [~]# /usr/local/bin/mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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)
    0
  • cPanelMichael
    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.
    0
  • morteza3245
    Re: output at MYsql Tuner after 24H, please take a look. [QUOTE]root@host [~]# /usr/local/bin/mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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: 13855) [--] Data in InnoDB tables: 255M (Tables: 1196) [--] Data in CSV tables: 0B (Tables: 2) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 872K (Tables: 50) [!!] Total fragmented tables: 1433 -------- 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: 11h 7m 41s (17M q [435.769 qps], 175K conn, TX: 179B, RX: 1B) [--] Reads / Writes: 80% / 20% [--] 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/17M) [OK] Highest usage of available connections: 19% (30/151) [OK] Key buffer size / total MyISAM indexes: 5.0G/868.4M [OK] Key buffer hit rate: 100.0% (380M cached / 130K reads) [OK] Query cache efficiency: 49.1% (7M cached / 14M selects) [!!] Query cache prunes per day: 556116 [OK] Sorts requiring temporary tables: 0% (353 temp sorts / 477K sorts) [!!] Joins performed without indexes: 2128 [!!] Temporary tables created on disk: 33% (41K on disk / 122K total) [OK] Thread cache hit rate: 99% (30 created / 175K connections) [!!] Table cache hit rate: 5% (5K open / 94K opened) [OK] Open file limit used: 9% (9K/100K) [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks) [!!] InnoDB data size / buffer pool: 255.3M/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 Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 50M) join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 50M) max_heap_table_size (> 50M) table_cache (> 5000) innodb_buffer_pool_size (>= 255M) root@host [~]#
    0
  • morteza3245
    Re: output at MYsql Tuner any update ?:(
    0
  • cPanelMichael
    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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post