mysql监测工具tuning-primer.sh

﹏ヽ暗。殇╰゛Y 2024-02-18 17:22 39阅读 0赞

mysql的运算情况，我们可以一步一步的去查看，分析。这样需要一定的时间，今天推荐给大家一个不错的脚本tuning-primer.sh，可以帮助我们去查看一下msyql的运行情况，产生报告和给出一些建议，我们可以根据这些建议，结合mysql服务器的实际情况，对mysql进行优化。  
  
  
  
  
一，下载以及使用  
  
  
1，wget http://www.day32.com/MySQL/tuning-primer.sh  
https://launchpad.net/mysql-tuning-primer/+milestone/1.6-r1  
  
2，将tuning-primer.sh拷贝到my.cnf的同级目录  
  
  
3，sh tuning-primer.sh 或者 chmod +x tuning-primer.sh 然后在 ./tuning-primer.sh 二者没什么区别  
  
  
\[root@BlackGhost mysql\]\# sh 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: /tmp/mysql.sock //找到一个mysql.sock  
Using: /tmp/mysql.sock //使用这个.sock文件  
Would you like to provide a different socket?: \[y/N\] n //是否使用其他的socket  
Do you have your login handy ? \[y/N\] : y //是否手动输入用户名和密码，当然你可以在my.cnf里面设置  
User: root  
Password:  
  
  
Would you like me to create a ~/.my.cnf file for you? \[y/N\] : n //是不是要帮你在创建一个my.cnf  
  
  
你也可以把用户名和密码放到my.cnf里面，如下  
  
  
\[client\]  
user = 用户名  
password = 密码  
socket = /tmp/mysql.sock  
  
  
二，运行结果，以及部分分析和建议  
  
  
我是以本机的mysql为例来进行测试的。  
\-- MYSQL PERFORMANCE TUNING PRIMER --  
\- By: Matthew Montgomery -  
  
  
MySQL Version 5.1.26-rc-log i686  
  
  
Uptime = 0 days 0 hrs 0 min 28 sec  
Avg. qps = 0  
Total Questions = 15  
Threads Connected = 1  
  
  
Warning: Server has not been running for at least 48hrs.  
It may not be safe to use these recommendations  
  
  
告诉我服务运行还不超过48个小时，这样不安全  
  
  
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 enabled.  
Current long\_query\_time = 2.000000 sec.  
You have 0 out of 36 that take longer than 2.000000 sec. to complete  
Your long\_query\_time seems to be fine  
  
  
BINARY UPDATE LOG （增加备份时的log文件）  
The binary update log is enabled  
The expire\_logs\_days is not set.  
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually  
Setting expire\_logs\_days will allow you to remove old binary logs automatically  
See http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html  
Binlog sync is not enabled, you could loose binlog records during a server crash  
  
  
告诉我binary log没有设置生存时间，会一直保存下来，只到主服务器重起，并且binlog的同步并没有激活，如果服务器崩溃binlog数据将丢失  
  
  
WORKER THREADS （工作线程）  
Current thread\_cache\_size = 0  
Current threads\_cached = 0  
Current threads\_per\_sec = 1  
Historic threads\_per\_sec = 0  
Your thread\_cache\_size is fine  
  
  
MAX CONNECTIONS （最大连接数）  
Current max\_connections = 500  
Current threads\_connected = 1  
Historic max\_used\_connections = 1  
The number of used connections is 0% of the configured maximum.  
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  
  
  
告诉我，目前已连接的最大连接和设置的最大连接的比率差不多是0％，建议我把最大连接设置小一点，可以节省一点内存  
  
  
No InnoDB Support Enabled!  
  
  
MEMORY USAGE （内存使用）  
Max Memory Ever Allocated : 17 M  
Configured Max Per-thread Buffers : 796 M  
Configured Max Global Buffers : 16 M  
Configured Max Memory Limit : 812 M  
Physical Memory : 1003 M  
Max memory limit seem to be within acceptable norms  
  
  
KEY BUFFER （KEY缓存）  
No key reads?!  
Seriously look into using some indexes  
Current MyISAM index space = 16 M  
Current key\_buffer\_size = 16 M  
Key cache miss rate is 1 : 0  
Key buffer free ratio = 88 %  
Your key\_buffer\_size seems to be fine  
  
  
QUERY CACHE （查询缓存）  
Query cache is supported but not enabled 查询缓存是支持的，但是没有激活  
Perhaps you should set the query\_cache\_size 建议我设置一下query\_cache\_size的大小  
  
  
SORT OPERATIONS （排序操作）  
Current sort\_buffer\_size = 512 K  
Current read\_rnd\_buffer\_size = 512 K  
No sort operations have been performed  
Sort buffer seems to be fine  
  
  
JOINS （连接）  
Current join\_buffer\_size = 132.00 K  
You have had 0 queries where a join could not use an index properly  
Your joins seem to be using indexes properly  
  
  
OPEN FILES LIMIT （最大打开文件数）  
Current open\_files\_limit = 2500 files  
The open\_files\_limit should typically be set to at least 2x-3x  
that of table\_cache if you have heavy MyISAM usage.  
  
  
告诉我，如果大量使用myisam的话，把open\_files\_limit的大小设置成table\_cache大小的2倍－3倍  
  
  
Your open\_files\_limit value seems to be fine  
  
  
TABLE CACHE （表缓存）  
Current table\_open\_cache = 64 tables  
Current table\_definition\_cache = 256 tables  
You have a total of 506 tables  
You have 64 open tables.  
Current table\_cache hit rate is 12%  
, while 100% of your table cache is in use  
You should probably increase your table\_cache  
You should probably increase your table\_definition\_cache value.  
  
  
告诉我打开了多少文件，table\_cache缓存的比率是多少，并建议我增加table\_cache和table\_definition\_cache的值  
TEMP TABLES （临时表）  
Current max\_heap\_table\_size = 16 M  
Current tmp\_table\_size = 16 M  
Of 141 temp tables, 10% were created on disk  
Created disk tmp tables ratio seems fine  
  
  
TABLE SCANS （表扫描）  
Current read\_buffer\_size = 256 K  
Current table scan ratio = 12 : 1  
read\_buffer\_size seems to be fine  
  
  
TABLE LOCKING （表锁定）  
Current Lock Wait ratio = 0 : 225  
Your table locking seems to be fine