Download and change execution permissions:
wget http://www.day32.com/MySQL/tuning-primer.sh
chmod +x tuning-primer.sh
./tuning-primer.sh
Results Report:
Can be labeled with several colors:
Blue: General indicators
Green: Indicates that this parameter can also
Red: Indicates a serious problem with this parameter
Dark red: Indicates a problem parameter
Yellow: Some informational tips
And there's a warning:
note! This script would still suggest raising the join_buffer_size when
Any joins not using indexes are found.
here is the result of a report
xx@xxxxxx:~$. ./tuning-primer.sh
Mysqld is alive
-- MYSQL Performance Tuning PRIMER --
-By:matthew Montgomery-
MySQL Version 5.1.32-enterprise-gpl-log x86_64
Uptime = 4 days 0 hrs min 3 sec
Avg. QPS = 255
Total Questions = 88956118
Threads Connected = 4
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information in how each of these
Runtime variables effects performance visit:
Http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
For info about MySQL ' s Enterprise monitoring and advisory Service
Slow QUERIES
The slow query log is not Enabled.
Current Long_query_time = 10.000000 sec.
You have 9589 Out of 88956228 That's take longer than 10.000000 sec. to complete
Your Long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld'll retain the entire binary log until RESET master or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days'll allow to the remove old binary logs automatically
Http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
WORKER THREADS
Current Thread_cache_size = 32
Current threads_cached = 29
Current THREADS_PER_SEC = 0
Historic Threads_per_sec = 0
Your thread_cache_size is fine
MAX Connections
Current max_connections = 151
Current threads_connected = 4
Historic Max_used_connections = 84
The number of used connections is 55% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever allocated:11.87 G
Configured Max Per-thread buffers:10.38 G
Configured Max Global buffers:6.10 G
Configured Max Memory limit:16.48 G
Physical memory:8.00 G
Max memory limit exceeds 90% of physical memory
(Memory settings are seriously problematic!) --by Me)
KEY BUFFER
1714734 * 1024/2147483648 * 100
Current MyISAM Index space = 8 K
Current key_buffer_size = 2.00 G
Key Cache Miss Rate is 1:585,673
Key Buffer free ratio = 0%
Your Key_buffer_size seems is too high.
Perhaps can use this elsewhere
(Error parameter settings: query_cache_size > Query_cache_limit--by Me)
SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = M
Sort Buffer seems to is fine
joins
Current join_buffer_size = 132.00 K
Your have had 1 queries where a join could not use a index properly
You should enable "Log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are are unable to optimize your queries your may want to increase your
Join_buffer_size to accommodate larger joins at one pass.
note! This script would still suggest raising the join_buffer_size when
Any joins not using indexes are found.
(Join_buffer_size Set too small, there is also a query using join but no walk index--by Me )
OPEN FILES LIMIT
Current Open_files_limit = 1185 files
The open_files_limit should typically is set to at least 2x-3x
That's Table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current Table_open_cache = Tables
Current Table_definition_cache = 256 tables
Have a total of 0 tables
You have the Open tables.
TABLE scanscurrent read_buffer_size = 2 M
Current TABLE scan ratio = 4650:1
you ha ve a ratio of sequential access requests to selects
your may benefit from raising read_buffer_size and/or G Your use of indexes.
table locking
Current Lock wait ratio = 1:5833374
your table locking seems to is fine