Kontrollbase Reporter CLI version
MySQL server tuning and analytics report for server: demo01.kontrollbase.com
Report generated on date: 2010-01-13 15:46:16
Filename: kontroll-reporter-cli.pl
Copyright 2010-present Matt Reid
http://kontrollsoft.com | contact@kontrollsoft.com
License: New-BSD license.
See LICENSE.txt that is bundled with this application.
Kontrollbase package version: 2.1
Alert: sync_binlog not set
Category: Logging
Description: Binary log is not being synced to disk at each write. Please enable.
Solution: Enable sync_binlog by adding the entry \"sync_binlog=1\" in mysql configuration file.
Links http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_sync_binlog
Alert: query cache size too large
Category: Caches
Description: The query_cache_size value is set too high.
Solution: Decrease the size of the query_cache. This can be set at runtime as well as the server configuration file.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_query_cache_size
Current Qcache_lowmem_prunes = 0
Current Qcache_free_memory = 1M
Current query_cache size = 2M
Current query cache usage ratio = 6%
query cache size recommended size = 141K
Alert: innodb buffer too large
Category: Innodb
Description: The InnoDB buffer size is set to too high, we can make use this memory elsewhere for better performance.
Solution: Decrease the value of Innodb_buffer_pool size by adding the entry in my.cnf as Innodb_buffer_pool_size=[desired value] and restart the MySQL for changes to affect.
Links http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_buffer_pool_size
Current innodb aggregate index space: 1M
Current innodb aggregate data space: 268M
Current innodb_buffer_pool_size = 512M.
Total needed for innodb index+data space: 297M
Allowable MAX for innodb_buffer_pool_size (85% of OS mem total): 1G
Recommended size of innodb_buffer_pool size for 85% fill: 349M
Innodb_buffer_pool_pages_free: 17460
Innodb_buffer_pool_pages_total: 32768
Current Innodb_buffer_pool_pages_ratio = 0.5328369140625 : 1
You may want to consider decreasing the innodb_buffer_pool_size based on the Innodb_buffer_pool_pages_ratio.
Alert: key buffer too large
Category: Connection Buffers
Description: Key_buffer_size for caching MyISAM indexes is set too large, the ratio of key blocks used to key blocks total is low.
Solution: Decrease the value of key_buffer_size by adding the entry in my.cnf as key_buffer_size=[desired value] , since it is a dynamic variable the changes can be done in runtime.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_key_buffer_size
Current Key_reads = 1207
Current Key_read_requests = 76742
Current Key_blocks_used = 25
Current Key_blocks_unused = 396
Current key_blocks_total: 421
Current buffer fill ratio = 6%
Current cache miss rate is 1:64
Current key_buffer_size = 512K
Recommended key_buffer_size for 95% fill = 32K
Your key_buffer_size is too large, less than 50% utilized,
Recommended key_buffer_size = 32K
Alert: sort buffer too large
Category: Connection Buffers
Description: The sort buffer is allocated more than the required size , use the resource elsewhere to gain performance
Solution: Decrease the value of sort_buffer_size by adding the entry in my.cnf as sort_buffer_size=[desired value] , since it is a dynamic variable the changes can be done in runtime.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_sort_buffer_size
Current passes_per_sort = 0.00373571837591689
Current sort_buffer_size = 128K
Current read_rnd_buffer_size = 128K
# Decrease sort_buffer_size, Current size is 128K
# Recommend: sort_buffer_size = 64K
# Decrease read_rnd_buffer_size, Current size is 128K
# Recommend: read_rnd_buffer_size = 64K
Alert: join buffer too small
Category: Connection Buffers
Description: Join buffer size is set to too small.
Solution: Increase the value to get a faster full join when adding indexes is not possible.
Increase the value of join_buffer_size by adding the entry in my.cnf as join_buffer_size=[desired value] , since it is a dynamic variable the changes can be done in runtime.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_join_buffer_size
You have had 0 joins without keys that check for key usage after each row.
Current join_buffer_size = 132K
Current Select_full_join = 66
Current Select_range_check = 0
You have had 66 queries where a join could not use an index properly.
# Recommend a starting point of 264K
Alert: table cache too large
Category: Caches
Description: table_cache size is too large. Use the resource elsewhere to gain the performance.
Solution: Decrease the value of table_cache_size by adding the entry in my.cnf as table_cache_size=[desired value] , since it is a dynamic variable the changes can be done in runtime.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_table_cache
Current table_cache value = 128 tables
Current Open_tables = 38 tables.
Current table_cache_fill_ratio is: 30 %
Current table_cache_hit_rate is: 1 %
# Decrease the table_cache, Current size is 128. Recommend table_cache=61
Alert: thread cache too small
Category: Caches
Description: thread_cache size is too small given the number of incoming connections seeking new threads.
Solution: Increase the value of thread_cache_size by adding the entry in my.cnf as thread_cache_size=[desired value] , since it is a dynamic variable the changes can be done in runtime.
Links http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_thread_cache_size
Current thread_cache_size: 150
Current Threads_cached: 10
Current Threads_connected: 12
Current Threads_created: 22
Current Global connections: 12
Historic Max_used_connections: 22
Historic_threads_per_second: 0
Thread_hit_ratio: 98%
You need to change the thread_cache_size.
# Recommend thread_cache_size = 23
Alert: innodb transaction isolation level not properly set for ACID
Category: Innodb
Description: InnoDB transaction-isolation is not set to REPEATABLE-READ or SERIALIZABLE. In regard to the SQL:1992 transaction isolation levels, the default InnoDB level is REPEATABLE READ.
Solution: Set transaction-isolation=REPEATABLE-READ or transaction-isolation=SERIALIZABLE to ensure ACID compliance for transactions.
Links http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html
Current transaction-isolation (tx-isolation) = READ-COMMITTED
Alert: innodb_lock_wait_timeout too high
Category: Innodb
Description: innodb_lock_wait_timeout too high, over the default of 50 seconds.
Solution: The default value is 50 seconds, to change the value add the entry innodb_lock_wait_timeout=50 in the config file.
Links http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_lock_wait_timeout
Current innodb_lock_wait_timeout = 60
Alert: concurrent queries too high
Category: General Memory
Description: queries_per_second is over threshold.
Solution: The important action to take is to look at the queries and schema and trying to optimize them.The number of concurrent queries varies for each system, so you need to do benchmarking to identify your system limits.
Current queries_per_second = 0.161632571844457/sec
Configured threshold = 0/sec
Analytics Report
QueryTrafficStats
totalqueries,790
totalreadqueries,169
totalwritequeries,68
totalinsertqueries,68
insertqueriestototal,0%
totalselectqueries,169
selectqueriestototal,0%
totalupdatequeries,0
updatequeriestototal,0%
totaldeletequeries,0
deletequeriestototal,0%
percentageorreadstototal,0.01%
percentageofwritestototal,0.01%
ratioofreadstowrites,1
ratioofwritestoreads,1
readqueriesper/sec,0
writequeriesper/sec,0
transactioncommits,0.01
transactioncommitsper/sec,0
QueryCacheStats
Enabled,YES
Cachesize,2
BlockSize,4096
TotalBlocks,124
Maxquerysizeincache,128
Freecachememory,1
Cacheutilization,1%
Cachetotalhits,76
Cachehitrate,1%
Parsingbuffer,8
CacheFragmentation,0%
Questionsincache,0
Questionsabletobecached,17
Questionsnotabletobecached,176
Questionsservedfromcachepercentage,14%
Questionsprunedfromcache,9
Percentagequestionspurgedfromcache,0%
totalconnectionsmade,158
ConnectionStats
successfulconnectionstotal,158
successfulconnections,100%
abortedconnectionstotal,0
abortedconnections,0%
averageconnectionspersecond,0/sec
maxallowedconnections,100
currentopenconnections,12
currentconnectionsusage,12%
maxconnectionusage,22%
maxconnectionerrorsallowable,100
connectiontimeoutvalue,30
ThreadStats
Threadcachesize,150
Threadsconnected,12
Threadscreated,22
Threadsrunning,1
Threadscached,10
Delayedinsertthreads,0.01
Slowlaunchthreads,0.01
IndexUsageStats
queriesutilizingindexes,22%
selectqueriesusingfulltablescan,150
joinsqueriesusingfulltablescan,0
TableLockingStats
Tableswithnon-waitinglocks,159
Tableswaitingforlocks,0
Tablesthathadlockingcontention,0
TableCacheStats
Tablecachesize,128
Currentopentables,38
Tablecacheutilization,337%
Averagetablesopenper/sec,0
Percentagecachemisses,1%
TempTableStats
Sizeofconnectionbasedtmptable,32
Sizeofmemorybasedtmptable,64
Temptablescreatedtotal,1
Temptablescreatedondisk,303
Percentoftemptablescreatedondisk,21%
Sort Buffer Stats
Sort buffer size, 128
Percentage of sort range scan, 0%
Percentage of sort scan, 5%
Percentage of sort merge passes, 0%
Rows sorted, 5658117
Maximum RAM consumable by sort buffer, 12
Join Buffer Stats
Join buffer size, 128
Max memory available to JOINs, 12
Select Full Joins, 66
Select Full Range Joins, 0
Percentage of join scan, 0%
MyISAM Stats
allocated cache memory, 512
block size, 1
current blocks, 0
used blocks, 0
used blocks percent, 23%
cache hit rate, 0
blocks written to disk, 0
cache writes to disk, 2
cache writes to disks percent, 13%
index delay update, ON
InnoDB Stats
allocated memory buffer pool size, 512
allocated innodb-mem to os-mem, 25%
free innodb memory, 17460
blocks served from cache, 357
blocks served from disk, 0
cache hit rate, 0
cache write wait required, 0
additional memory allowed, 16
free page waits, 0.01
log buffer size, 16
log waits required, 0%
number of free page waits, 0.01