Help me to optimize MySQL

T

theway007

Member
Nov 20, 2010
5
0
51
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 <[email protected]>
 >>  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: 414M (Tables: 3217)
[--] Data in InnoDB tables: 25M (Tables: 297)
[--] Data in MEMORY tables: 248K (Tables: 18)
[!!] Total fragmented tables: 335

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 59m 46s (60K q [16.982 qps], 1K conn, TX: 97M, RX: 9M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 1.8G global + 4.6M per thread (150 max threads)
[OK] Maximum possible memory usage: 2.4G (60% of installed RAM)
[OK] Slow queries: 0% (78/60K)
[OK] Highest usage of available connections: 27% (41/150)
[OK] Key buffer size / total MyISAM indexes: 256.0M/221.3M
[!!] Key buffer hit rate: 83.3% (170K cached / 28K reads)
[!!] Query cache efficiency: 18.7% (7K cached / 37K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 943
[!!] Temporary tables created on disk: 29% (1K on disk / 4K total)
[!!] Thread cache hit rate: 43% (1K created / 1K connections)
[!!] Table cache hit rate: 0% (1K open / 225K opened)
[OK] Open file limit used: 7% (1K/21K)
[OK] Table locks acquired immediately: 99% (55K immediate / 55K locks)
[OK] InnoDB data size / buffer pool: 25.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)
 
T

theway007

Member
Nov 20, 2010
5
0
51
I have reduce the max connection to 80 and it gives a better value:

PHP: 
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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: 409M (Tables: 3217)
[--] Data in InnoDB tables: 25M (Tables: 297)
[--] Data in MEMORY tables: 372K (Tables: 18)
[!!] Total fragmented tables: 303

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 9m 23s (73K q [17.549 qps], 2K conn, TX: 108M, RX: 11M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 1.8G global + 10.6M per thread (80 max threads)
[OK] Maximum possible memory usage: 2.6G (64% of installed RAM)
[OK] Slow queries: 0% (78/73K)
[OK] Highest usage of available connections: 51% (41/80)
[OK] Key buffer size / total MyISAM indexes: 256.0M/219.5M
[!!] Key buffer hit rate: 80.9% (239K cached / 45K reads)
[OK] Query cache efficiency: 26.6% (12K cached / 45K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 951
[!!] Temporary tables created on disk: 27% (1K on disk / 4K total)
[OK] Thread cache hit rate: 55% (1K created / 2K connections)
[!!] Table cache hit rate: 0% (4 open / 296K opened)
[OK] Open file limit used: 0% (9/21K)
[OK] Table locks acquired immediately: 99% (61K immediate / 61K locks)
[OK] InnoDB data size / buffer pool: 25.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)
 
T

theway007

Member
Nov 20, 2010
5
0
51
it ran for 24 hours.

PHP: 
-------- 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: 506M (Tables: 3280)
[--] Data in InnoDB tables: 31M (Tables: 667)
[--] Data in MEMORY tables: 248K (Tables: 18)
[!!] Total fragmented tables: 708

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 2m 17s (782K q [9.046 qps], 21K conn, TX: 1B, RX: 137M)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 538.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.9G (46% of installed RAM)
[OK] Slow queries: 0% (2/782K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/248.8M
[OK] Key buffer hit rate: 95.3% (6M cached / 290K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (25 temp sorts / 81K sorts)
[!!] Joins performed without indexes: 4246
[!!] Temporary tables created on disk: 28% (19K on disk / 69K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 2M opened)
[OK] Open file limit used: 0% (123/21K)
[OK] Table locks acquired immediately: 99% (781K immediate / 781K locks)
[OK] InnoDB data size / buffer pool: 31.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)
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
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
 
T

theway007

Member
Nov 20, 2010
5
0
51
This is the reason it ask for more and more:

PHP: 
-------- 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: 509M (Tables: 3294)
[--] Data in InnoDB tables: 31M (Tables: 667)
[--] Data in MEMORY tables: 372K (Tables: 18)
[!!] Total fragmented tables: 708

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 13m 2s (426K q [5.107 qps], 17K conn, TX: 811M, RX: 62M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 900.0M global + 2.7M per thread (200 max threads)
[OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
[OK] Slow queries: 0% (0/426K)
[OK] Highest usage of available connections: 3% (6/200)
[OK] Key buffer size / total MyISAM indexes: 350.0M/251.5M
[!!] Key buffer hit rate: 78.6% (1K cached / 428 reads)
[OK] Query cache efficiency: 65.9% (194K cached / 295K selects)
[!!] Query cache prunes per day: 50869
[OK] Sorts requiring temporary tables: 0% (19 temp sorts / 12K sorts)
[!!] Joins performed without indexes: 477
[OK] Temporary tables created on disk: 23% (10K on disk / 42K total)
[!!] Thread cache hit rate: 1% (16K created / 17K connections)
[!!] Table cache hit rate: 0% (2K open / 2M opened)
[OK] Open file limit used: 16% (3K/21K)
[OK] Table locks acquired immediately: 99% (161K immediate / 161K locks)
[OK] InnoDB data size / buffer pool: 31.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
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
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
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
S How to optimize mysql - help Workarounds and Optimization 2
U Help - optimize mysqltunner to reduce high cpu usage Workarounds and Optimization 5
F Optimize MySQL Help Workarounds and Optimization 2
PPNSteve MySQL 5.6 Optimize help Workarounds and Optimization 4
T Please help me to optimize mysql (my.cnf) Workarounds and Optimization 2
Similar threads
How to optimize mysql - help
Help - optimize mysqltunner to reduce high cpu usage
Optimize MySQL Help
MySQL 5.6 Optimize help
Please help me to optimize mysql (my.cnf)
Top Bottom