MySQL调优脚本tuning-primer.sh使用说明

SLOW QUERIES 慢查询检查
SLOW QUERIESThe slow query log is enabled. 说明我已经启用了慢查询记录功能。也就是参数
slow_query_log = 1
Current long_query_time = 5.000000 sec. 慢查询的阀值时间。也就是参数
long_query_time = 5
You have 17 out of 638844 that take longer than 5.000000 sec. to complete 说明慢查询日志中记录了17条查询时间超过5秒的语句。
slow_query_log_file=/data/ats_db/mysql-slow.log设置慢查询日志路径。使用

mysqldumpslow命令查询慢日志

Your long_query_time seems to be fine 慢查询阀值时间设置得在推荐的范围内
BINARY UPDATE LOG 更新二进制日志文件
The binary update log is enabled 这项说明启用了bin-log日志功能。参数
log-bin = /data/ats_db/mysql-bin
Binlog sync is not enabled, you could loose binlog records during a server crash 没有启用 sync_binlog 选项。也即是将二进制日志实时写入到磁盘通过 sync_binlog=1来指定
WORKER THREADS 工作线程
Current thread_cache_size = 8 当前线程缓存大小。
thread_concurrency = 8
Current threads_cached = 7 Show status like ‘threads_cached’
Current threads_per_sec = 0 脚本先执行Show status like ‘Threads_cached’查看当前的线程创建情况,然后sleep 1后在执行相同的命令,最终后者减去前者的数就是每秒线程创建数。
Historic threads_per_sec = 0 该值是使用Threads_cached /uptime获得的。
Your thread_cache_size is fine
MAX CONNECTIONS 最大连接数
Current max_connections = 1024 当前配置文件中设置的并发连接数
Current threads_connected = 2 当前线程连接诶数。
show status like ‘Threads_connected’
Historic max_used_connections = 4 show status like ‘Max_used_connections’;
The number of used connections is 0% of the configured maximum. 这个值使用 Max_used_connections*100/ max_connections得出。
You are using less than 10% of your configured max_connections.Lowering max_connections could help to avoid an over-allocation of memory

See “MEMORY USAGE” section to make sure you are not over-allocating

Max_used_connections的值不足max_connections值的10%。设置合适的max_connections值有助于节省内存。
MEMORY USAGE 内存使用
Max Memory Ever Allocated : 841 M Max Memory Ever Allocated = max_memory
Configured Max Per-thread Buffers : 28.40 G Configured Max Per-thread Buffers per_thread_buffers
Configured Max Global Buffers : 586 M Configured Max Global Buffers = global_buffers
Configured Max Memory Limit : 28.97 G Configured Max Memory Limit = total_memory
这一项很重要,他是将各个缓存的大小累加,然后同max_connections相乘,从而得出当达到max_connections后需要分配的内存有多少。我这里由于max_connections写得很大,造成了最大内存限制超过了真实内存很多,所以建议不要随意增大max_connections的值。减小 max_connections的值,最终保证最大内存限制在真实内存的90%以下。
Physical Memory : 7.79 G 实际物理内存
Max memory limit exceeds 90% of physical memory
per_thread_buffers
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack+
join_buffer_size+binlog_cache_size)*max_connections

per_thread_max_buffers
(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack
+join_buffer_size+binlog_cache_size)*max_used_connections

max_used_connections=$(mysql -Bse "show status like 'Max_used_connections'" | awk '{ print $2 }')


global_buffers
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+
key_buffer_size+query_cache_size


max_memory
=global_buffers+per_thread_max_buffers


total_memory
=global_buffers+per_thread_buffers

KEY BUFFER Key 缓冲
Current MyISAM index space = 222 K 当前数据库MyISAM表中索引占用磁盘空间
Current key_buffer_size = 512 M MySQL配置文件中key_buffer_size 设置的大小
Key cache miss rate is 1 : 3316 Key_read_requests/ Key_reads 这里说明3316次读取请求中有1次丢失(也就是说1次读取磁盘)
Key buffer free ratio = 81 % key_blocks_unused * key_cache_block_size / key_buffer_size * 100
Your key_buffer_size seems to be fine
QUERY CACHE Query 缓存
Query cache is enabled 该项说明 我们指定了query_cache_size 的值。如果query_cache_size=0的话这里给出的提示是:
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
Current query_cache_size = 64 M 当前系统query_cache_size 值大小 [F]
Current query_cache_used = 1 M query_cache_used =query_cache_size-qcache_free_memory
Current query_cache_limit = 128 M 变量 query_cache_limit 大小
Current Query cache Memory fill ratio = 1.79 % query_cache_used/query_cache_size *100%
Current query_cache_min_res_unit = 4 K show variables like ‘query_cache_min_res_unit’;
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
这项给出的结论是query_cache_size的值设置的有些过高。其比对标准是 “Query cache Memory fill ratio”的值如果小于<25%就会给出这个提示。可以将这些资源应用到其他的地方
MySQL won’t cache query results that are larger than query_cache_limit in size MySQL不会将大于query_cache_limit的查询结果进行缓存
show status like ‘Qcache%’;Qcache_free_blocks        10
Qcache_free_memory        65891984
Qcache_hits            14437
Qcache_inserts            707
Qcache_lowmem_prunes    0
Qcache_not_cached        216
Qcache_queries_in_cache    540
Qcache_total_blocks        1191
SORT OPERATIONS SORT 选项
Current sort_buffer_size = 6 M show variables like ’sort_buffer%’;
Current read_rnd_buffer_size = 16 M show variables like ‘read_rnd_buffer_size%’;
Sort buffer seems to be fine
JOINS JOINS
Current join_buffer_size = 132.00 K show variables like ‘join_buffer_size%’;join_buffer_size=join_buffer_size+4kb
You have had 6 queries where a join could not use an index properly 这里的6是通过 show status like ‘Select_full_join’; 获得的
You 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 your
join_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
你需要启用 “log-queries-not-using-indexes” 然后在慢查询日志中看是否有取消索引的joins语句。如果不优化查询语句的话,则需要增大join_buffer_size
OPEN FILES LIMIT 文件打开数限制
Current open_files_limit = 1234 files show variables like ‘open_files_limit%’;
The open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage. 如果系统中有很多的MyISAM类型的表,则建议将open_files_limit 设置为2X~3X的table_open_cache
show status like ‘Open_files’;open_files_ratio= open_files*100/open_files_limit
如果open_files_ratio 超过75% 则需要加大open_files_limit
Your open_files_limit value seems to be fine
TABLE CACHE TABLE 缓存
Current table_open_cache = 512 tables show variables like ‘table_open_cache’;
Current table_definition_cache = 256 tables show variables like ‘ table_definition_cache ‘;
You have a total of 368 tables SELECTCOUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’
You have 371 open tables. show status like ‘Open_tables’;
The table_cache value seems to be fine Open_tables /table_open_cache*100% < 95%
You should probably increase your table_definition_cache value. table_cache_hit_rate =open_tables*100/opened_tables
TEMP TABLES 临时表
Current max_heap_table_size = 16 M show variables like ‘max_heap_table_size’;
Current tmp_table_size = 16 M show variables like ‘tmp_table_size’;
Of 285 temp tables, 11% were created on disk Created_tmp_tables=285created_tmp_disk_tables*100/
(created_tmp_tables+created_tmp_disk_tables)=11%
Created disk tmp tables ratio seems fine
TABLE SCANS 扫描表
Current read_buffer_size = 6 M show variables like ‘read_buffer_size’;
Current table scan ratio = 9 : 1 read_rnd_next =show global status like ‘Handler_read_rnd_next’;
com_select= show global status like ‘Com_select’;
full_table_scans=read_rnd_next/com_select
Current table scan ratio = full_table_scans : 1″
如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好,增加read_buffer_size值会有一些好处,但最好不要超过8MB。
read_buffer_size seems to be fine
TABLE LOCKING TABLE LOCKING
Current Lock Wait ratio = 0 : 5617 show global status like’Table_locks_waited’;
show global status like

‘Questions’;
如果 Table_locks_waited=0
Current Lock Wait ratio = 0: Questions

Your table locking seems to be fine


获取脚本的路径为:

https://code.launchpad.net/mysql-tuning-primer

脚本内容如下:

#!/bin/sh


# vim: ts=8
#########################################################################
# #
# MySQL performance tuning primer script#
# Writen by: Matthew Montgomery#
# Report bugs to: https://bugs.launchpad.net/mysql-tuning-primer#
# Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/)#
# Version: 1.6-r1Released: 2011-08-06#
# Licenced under GPLv2                                            #
# #
#########################################################################


#########################################################################
# #
# Usage: ./tuning-primer.sh [ mode ] #
# #
# Available Modes: #
# all : perform all checks (default)#
# prompt : prompt for login credintials and socket#
# and execution mode#
mem, memory : run checks for tunable options which  #
# effect memory usage#
# disk, file :run checks for options which effect#
# i/o performance or file handle limits#
# innodb :  run InnoDB checks /* to be improved */
# misc : run checks for that don't categorise#
# well Slow Queries, Binary logs,#
# Used Connections and Worker Threads#
#########################################################################
# #
# Set this socket variable ONLY if you have multiple instances running
# or we are unable to find your socket, and you don't want to to be#
# prompted for input each time you run this script.#
# #
#########################################################################
socket=


export black='\033[0m'
export boldblack='\033[1;0m'
export red='\033[31m'
export boldred='\033[1;31m'
export green='\033[32m'
export boldgreen='\033[1;32m'
export yellow='\033[33m'
export boldyellow='\033[1;33m'
export blue='\033[34m'
export boldblue='\033[1;34m'
export magenta='\033[35m'
export boldmagenta='\033[1;35m'
export cyan='\033[36m'
export boldcyan='\033[1;36m'
export white='\033[37m'
export boldwhite='\033[1;37m'




cecho ()


## -- Function to easliy print colored text -- ##

# Color-echo.
# Argument $1 = message
# Argument $2 = color
{
local default_msg="No message passed."


message=${1:-$default_msg} # Defaults to default message.


#change it for fun
#We use pure names
color=${2:-black} # Defaults to black, if not specified.


case $color in
black)
printf "$black" ;;
boldblack)
printf "$boldblack" ;;
red)
printf "$red" ;;
boldred)
printf "$boldred" ;;
green)
printf "$green" ;;
boldgreen)
printf "$boldgreen" ;;
yellow)
printf "$yellow" ;;
boldyellow)
printf "$boldyellow" ;;
blue)
printf "$blue" ;;
boldblue)
printf "$boldblue" ;;
magenta)
printf "$magenta" ;;
boldmagenta)
printf "$boldmagenta" ;;
cyan)
printf "$cyan" ;;
boldcyan)
printf "$boldcyan" ;;
white)
printf "$white" ;;
boldwhite)
printf "$boldwhite" ;;
esac
  printf "%s\n"  "$message"
  tput sgr0 # Reset to normal.
  printf "$black"


return
}




cechon ()


## -- Function to easliy print colored text -- ##


# Color-echo.
# Argument $1 = message
# Argument $2 = color
{
local default_msg="No message passed."
# Doesn't really need to be a local variable.


message=${1:-$default_msg} # Defaults to default message.


#

点赞

发表评论