tuning-primer.sh 调优MySQL的性能

安装运行

wget http://www.day32.com/MySQL/tuning-primer.sh
chmod +x tuning-primer.sh

运行时你可以选择在用户目录下生成一个保存mysql密码的.my.cnf文件方便下次调用
~/.my.cnf文件内容应该是这样的
cat ~/.my.cnf

[client]
user=test
password=123qwe
socket=/opt/mysql/mysql.sock

./tuning-primer.sh

Using login values from ~/.my.cnf
– INITIAL LOGIN ATTEMPT FAILED –
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /opt/mysql/mysql.sock
Using: /opt/mysql/mysql.sock
Would you like to provide a different socket?: [y/N] n
Do you have your login handy ? [y/N] : y
User: test
Password: 123qwe

Would you like me to create a ~/.my.cnf file for you? [y/N] : y

~/.my.cnf already exists!

Replace ? [y/N] : y
– FINAL LOGIN ATTEMPT FAILED –
Unable to log into socket: /opt/mysql/mysql.sock

因为我的mysql是编译安装在/opt/mysql/下,这里找不到执行文件,所以没有连接上.
编辑第219和220行,手动修改文件加上路径
vi tuning-primer.sh

215 if [ -f /etc/psa/.psa.shadow ] ; then
216 mysql=”mysql -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)”
217 mysqladmin=”mysqladmin -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)”
218 else
219 mysql=”/opt/mysql/bin/mysql”
220 mysqladmin=”/opt/mysql/bin/mysqladmin”
221 # mysql=”mysql -S $socket”
222 # mysqladmin=”mysqladmin -S $socket”
223 fi

顺带修复生成密码时的错误问题.
第313行”$pass\”后面需加个”n”

313 printf “[client]\nuser=$user\npassword=$pass\nsocket=$socket” > ~/.my.cnf

提示:vi中显示行号方法.在命令模式下输入”:set number”

保存后再次运行,在终端上按照问题重要程度分别用黄色/红色字符标记问题
./tuning-primer.sh

— MYSQL PERFORMANCE TUNING PRIMER —
– By: Matthew Montgomery –

MySQL Version 5.1.26-rc x86_64

Uptime = 0 days 7 hrs 17 min 18 sec
Avg. qps = 286
Total Questions = 7527430
Threads Connected = 6

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on 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 29 out of 7527516 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 80
Current threads_cached = 75
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 350
Current threads_connected = 7
Historic max_used_connections = 208
The number of used connections is 59% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 68.78 G
Configured Max Per-thread Buffers : 112.17 G
Configured Max Global Buffers : 2.12 G
Configured Max Memory Limit : 114.29 G
Physical Memory : 15.66 G

nMax memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 3.00 G
Current key_buffer_size = 2.00 G
Key cache miss rate is 1 : 3262
Key buffer free ratio = 62 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 61 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 47.81 %
Current query_cache_min_res_unit = 2 K
Query Cache is 21 % fragmented
Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory
If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.
MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 M
Current read_rnd_buffer_size = 32 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 32.00 M
You have had 15 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable “log-queries-not-using-indexes”
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 51200 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 3072 tables
Current table_definition_cache = 256 tables
You have a total of 658 tables
You have 1814 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 384 M
Current tmp_table_size = 1.00 G
Of 142450 temp tables, 18% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 8 M
Current table scan ratio = 5065 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 25
You may benefit from selective use of InnoDB.
If you have long running SELECT’s against MyISAM tables and perform
frequent updates consider setting ‘low_priority_updates=1’
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting ‘concurrent_insert=2’.

我的my.cnf

[mysqld]
port = 3306
socket = /opt/mysql/mysql.sock
skip-locking
skip-name-resolve
back_log=100
key_buffer_size = 2048M
query_cache_size = 128M
query_cache_limit = 2M #default=1M
query_cache_min_res_unit = 2k #default=4K

max_allowed_packet = 16M
table_cache = 3072

tmp_table_size = 1024M #default=16M
max_heap_table_size = 384M #default=16M

read_buffer_size = 8M
read_rnd_buffer_size = 32M

sort_buffer_size = 256M
join_buffer_size = 32M
myisam_sort_buffer_size = 256M
thread_cache_size = 80 #default=0
thread_stack = 192K #default=192K

# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 16 #default=10
connect_timeout = 30
#interactive timeout = 600
max_connection = 350
max_connect_errors = 30
wait_timeout = 30

服务配置为5504(4核) *2,16G内存,centos 5.3 64位
大大小小十几个数据库,几十G数据,主要的应用是discuz论坛

根据提示对my.cnf做一些优化,效率更好了点.
关键还是需要优化sql来减少慢查询及锁定的问题.

key_buffer_size = 3096M
table_definition_cache = 800
join_buffer_size = 4M
low_priority_updates =1

参考:http://www.chedong.com/blog/archives/001451.html