List:General Discussion« Previous MessageNext Message »
From:uYe Date:December 4 2009 1:25am
Subject:MySQLTuner Result
View as plain text  
Hi list,
I have problem with mysqltuner result. My server has 1 2.2G quadcore  
proccessor, 16GB RAM and with this my.cnf configuration:
# * Fine Tuning
#
key_buffer              = 8G
max_allowed_packet      = 2G
thread_stack            = 4M
thread_cache_size       = 256
key_buffer_size         = 4G
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
max_connections        = 555
table_cache            = 2048
thread_concurrency     = 100
#
# * Query Cache Configuration
#
query_cache_limit       = 512M
query_cache_size        = 512M
#query_cache_type       = 1
join_buffer_size        = 128M
#tmp_table_size         = 32M
#max_heap_table_size    = 8M
#sort_buffer_size       = 2M
#read_rnd_buffer_size   = 1M

And get this result with mysqltuner:

root@db1:~# mysqltuner

  >>  MySQLTuner 0.9.0 - Major Hayden <major@stripped>
  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
  >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics  
--------------------------------------------------
[!!] There is a new version of MySQLTuner available
[OK] Currently running supported MySQL version 5.0.75-0ubuntu10.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics  
-------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10G (Tables: 107)

-------- Performance Metrics  
-------------------------------------------------
[--] Up for: 36m 47s (334K q [151.410 qps], 902 conn, TX: 304M, RX:  
582M)
[--] Reads / Writes: 32% / 68%
[--] Total buffers: 134.4M per thread and 4.5G global
[!!] Maximum possible memory usage: 77.4G (499% of installed RAM)
[!!] Slow queries: 30% (102K/334K)
[OK] Highest usage of available connections: 11% (64/555)
[!!] Key buffer size / total MyISAM indexes: 4.0G/6.5G
[!!] Key buffer hit rate: 79.8%
[!!] Query cache efficiency: 1.8%
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0%
[OK] Temporary tables created on disk: 5%
[OK] Thread cache hit rate: 92%
[!!] Table cache hit rate: 12%
[OK] Open file limit used: 5%
[OK] Table locks acquired immediately: 99%

-------- Recommendations  
-----------------------------------------------------
General recommendations:
     MySQL started within last 24 hours - recommendations may be  
inaccurate
     Reduce your overall MySQL memory footprint for system stability
     Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
   *** MySQL's maximum memory usage exceeds your installed memory ***
   *** Add more RAM before increasing any MySQL buffer variables  ***
     key_buffer_size (> 6.5G)
     query_cache_limit (> 512M, or use smaller result sets)
     table_cache (> 2048)


Is that mean I miss configure my server? Please help me to configure  
my mysql so It can has maximum configuration. TIA



Willy
sangprabv@stripped



Thread
MySQLTuner ResultuYe4 Dec