mysql监测工具tuning-primer.sh

下载地址:https://launchpad.net/mysql-tuning-primer

将tuning-primer.sh 放在mysql所在的server,

若出现如下错误

[root@hbase1 ~]# sh tuning-primer.sh allwhich: no bc in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/jdk1.6.0_31/bin:/usr/local/apache-ant-1.8.3/bin:/root/bin)Error: Command line calculator 'bc' not found!

请安装bc,

[root@hbase1 ~]# yum install bc 

再次运行，生成报告:

  -- MYSQL PERFORMANCE TUNING PRIMER --             - By: Matthew Montgomery -MySQL Version 5.1.67 x86_64Uptime = 55 days 22 hrs 20 min 50 secAvg. qps = 48Total Questions = 234566845Threads Connected = 205Server has been running for over 48hrs.It should be safe to follow these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.htmlVisit http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL's Enterprise Monitoring and Advisory ServiceSLOW QUERIESThe slow query log is NOT enabled.Current long_query_time = 10.000000 sec.You have 1345 out of 234566882 that take longer than 10.000000 sec. to completeYour long_query_time seems to be fineBINARY UPDATE LOGThe binary update log is NOT enabled.You will not be able to do point in time recoverySee http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.htmlWORKER THREADSCurrent thread_cache_size = 8Current threads_cached = 5Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = 1000Current threads_connected = 205Historic max_used_connections = 410The number of used connections is 41% of the configured maximum.Your max_connections variable seems to be fine.INNODB STATUSCurrent InnoDB index space = 3.64 GCurrent InnoDB data space = 17.16 GCurrent InnoDB buffer pool free = 0 %Current innodb_buffer_pool_size = 8 MDepending on how much space your innodb indexes take up it may be safeto increase this value to up to 2 / 3 of total system memoryMEMORY USAGEMax Memory Ever Allocated : 1.11 GConfigured Max Per-thread Buffers : 2.68 GConfigured Max Global Buffers : 17 MConfigured Max Memory Limit : 2.70 GPhysical Memory : 7.67 GMax memory limit seem to be within acceptable normsKEY BUFFERCurrent MyISAM index space = 1.28 GCurrent key_buffer_size = 7 MKey cache miss rate is 1 : 814Key buffer free ratio = 81 %Your key_buffer_size seems to be fineQUERY CACHEQuery cache is supported but not enabledPerhaps you should set the query_cache_sizeSORT OPERATIONSCurrent sort_buffer_size = 2 MCurrent read_rnd_buffer_size = 256 KSort buffer seems to be fineJOINSCurrent join_buffer_size = 132.00 KYou have had 62754 queries where a join could not use an index properlyYou should enable "log-queries-not-using-indexes"Then look for non indexed joins in the slow query log.If you are unable to optimize your queries you may want to increase yourjoin_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size whenANY joins not using indexes are found.OPEN FILES LIMITCurrent open_files_limit = 65535 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHECurrent table_open_cache = 1024 tablesCurrent table_definition_cache = 256 tablesYou have a total of 6130 tablesYou have 1024 open tables.Current table_cache hit rate is 0%, while 100% of your table cache is in useYou should probably increase your table_cacheYou should probably increase your table_definition_cache value.TEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 16 MOf 12719708 temp tables, 1% were created on diskCreated disk tmp tables ratio seems fineTABLE SCANSCurrent read_buffer_size = 128 KCurrent table scan ratio = 836 : 1read_buffer_size seems to be fineTABLE LOCKINGCurrent Lock Wait ratio = 1 : 1605886Your table locking seems to be fine

根据上面提到的指标慢慢优化。