tuning-primer.sh

作者: admin 分类: linux 发布时间: 2011-07-26 12:21 ė 6 没有评论

CU上看了个非常不错的MYSQL调优工具tuning-primer.sh  

下载地址:http://www.linuxqq.com/downloads/tuning-primer.sh  

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

  1. [client]
  2. user=test
  3. password=123qwe
  4. socket=/opt/mysql/mysql.sock

./tuning-primer.sh

  1. Using login values from ~/.my.cnf
  2. – INITIAL LOGIN ATTEMPT FAILED –
  3. Testing for stored webmin passwords:
  4.  None Found
  5. Could not auto detect login info!
  6. Found Sockets: /opt/mysql/mysql.sock
  7. Using: /opt/mysql/mysql.sock
  8. Would you like to provide a different socket?: [y/N] n
  9. Do you have your login handy ? [y/N] : y
  10. User: test
  11. Password: 123qwe
  12.  
  13. Would you like me to create a ~/.my.cnf file for you? [y/N] : y
  14.  
  15. ~/.my.cnf already exists!
  16.  
  17. Replace ? [y/N] : y
  18. – FINAL LOGIN ATTEMPT FAILED –
  19. Unable to log into socket: /opt/mysql/mysql.sock

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

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

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

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

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

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

  1. — MYSQL PERFORMANCE TUNING PRIMER —
  2.              – By: Matthew Montgomery –
  3.  
  4. MySQL Version 5.1.26-rc x86_64
  5.  
  6. Uptime = 0 days 7 hrs 17 min 18 sec
  7. Avg. qps = 286
  8. Total Questions = 7527430
  9. Threads Connected = 6
  10.  
  11. Warning: Server has not been running for at least 48hrs.
  12. It may not be safe to use these recommendations
  13.  
  14. To find out more information on how each of these
  15. runtime variables effects performance visit:
  16. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
  17. Visit http://www.mysql.com/products/enterprise/advisors.html
  18. for info about MySQL’s Enterprise Monitoring and Advisory Service
  19.  
  20. SLOW QUERIES
  21. The slow query log is NOT enabled.
  22. Current long_query_time = 10.000000 sec.
  23. You have 29 out of 7527516 that take longer than 10.000000 sec. to complete
  24. Your long_query_time seems to be fine
  25.  
  26. BINARY UPDATE LOG
  27. The binary update log is NOT enabled.
  28. You will not be able to do point in time recovery
  29. See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
  30.  
  31. WORKER THREADS
  32. Current thread_cache_size = 80
  33. Current threads_cached = 75
  34. Current threads_per_sec = 0
  35. Historic threads_per_sec = 0
  36. Your thread_cache_size is fine
  37.  
  38. MAX CONNECTIONS
  39. Current max_connections = 350
  40. Current threads_connected = 7
  41. Historic max_used_connections = 208
  42. The number of used connections is 59% of the configured maximum.
  43. Your max_connections variable seems to be fine.
  44.  
  45. No InnoDB Support Enabled!
  46.  
  47. MEMORY USAGE
  48. Max Memory Ever Allocated : 68.78 G
  49. Configured Max Per-thread Buffers : 112.17 G
  50. Configured Max Global Buffers : 2.12 G
  51. Configured Max Memory Limit : 114.29 G
  52. Physical Memory : 15.66 G
  53.  
  54. nMax memory limit exceeds 90% of physical memory
  55.  
  56. KEY BUFFER
  57. Current MyISAM index space = 3.00 G
  58. Current key_buffer_size = 2.00 G
  59. Key cache miss rate is 1 : 3262
  60. Key buffer free ratio = 62 %
  61. Your key_buffer_size seems to be fine
  62.  
  63. QUERY CACHE
  64. Query cache is enabled
  65. Current query_cache_size = 128 M
  66. Current query_cache_used = 61 M
  67. Current query_cache_limit = 2 M
  68. Current Query cache Memory fill ratio = 47.81 %
  69. Current query_cache_min_res_unit = 2 K
  70. Query Cache is 21 % fragmented
  71. Run “FLUSH QUERY CACHE” periodically to defragment the query cache memory
  72. If you have many small queries lower ‘query_cache_min_res_unit’ to reduce fragmentation.
  73. MySQL won’t cache query results that are larger than query_cache_limit in size
  74.  
  75. SORT OPERATIONS
  76. Current sort_buffer_size = 256 M
  77. Current read_rnd_buffer_size = 32 M
  78. Sort buffer seems to be fine
  79.  
  80. JOINS
  81. Current join_buffer_size = 32.00 M
  82. You have had 15 queries where a join could not use an index properly
  83. join_buffer_size >= 4 M
  84. This is not advised
  85. You should enable “log-queries-not-using-indexes”
  86. Then look for non indexed joins in the slow query log.
  87.  
  88. OPEN FILES LIMIT
  89. Current open_files_limit = 51200 files
  90. The open_files_limit should typically be set to at least 2x-3x
  91. that of table_cache if you have heavy MyISAM usage.
  92. Your open_files_limit value seems to be fine
  93.  
  94. TABLE CACHE
  95. Current table_open_cache = 3072 tables
  96. Current table_definition_cache = 256 tables
  97. You have a total of 658 tables
  98. You have 1814 open tables.
  99. The table_cache value seems to be fine
  100. You should probably increase your table_definition_cache value.
  101.  
  102. TEMP TABLES
  103. Current max_heap_table_size = 384 M
  104. Current tmp_table_size = 1.00 G
  105. Of 142450 temp tables, 18% were created on disk
  106. Effective in-memory tmp_table_size is limited to max_heap_table_size.
  107. Created disk tmp tables ratio seems fine
  108.  
  109. TABLE SCANS
  110. Current read_buffer_size = 8 M
  111. Current table scan ratio = 5065 : 1
  112. read_buffer_size seems to be fine
  113.  
  114. TABLE LOCKING
  115. Current Lock Wait ratio = 1 : 25
  116. You may benefit from selective use of InnoDB.
  117. If you have long running SELECT’s against MyISAM tables and perform
  118. frequent updates consider setting ‘low_priority_updates=1’
  119. If you have a high concurrency of inserts on Dynamic row-length tables
  120. consider setting ‘concurrent_insert=2’.

我的my.cnf

  1. [mysqld]
  2. port = 3306
  3. socket = /opt/mysql/mysql.sock
  4. skip-locking
  5. skip-name-resolve
  6. back_log=100
  7. key_buffer_size = 2048M
  8. query_cache_size = 128M
  9. query_cache_limit = 2M #default=1M
  10. query_cache_min_res_unit = 2k #default=4K
  11.  
  12. max_allowed_packet = 16M
  13. table_cache = 3072
  14.  
  15. tmp_table_size = 1024M #default=16M
  16. max_heap_table_size = 384M #default=16M
  17.  
  18. read_buffer_size = 8M
  19. read_rnd_buffer_size = 32M
  20.  
  21. sort_buffer_size = 256M
  22. join_buffer_size = 32M
  23. myisam_sort_buffer_size = 256M
  24. thread_cache_size = 80 #default=0
  25. thread_stack = 192K #default=192K
  26.  
  27. # Try number of CPU’s*2 for thread_concurrency
  28. thread_concurrency = 16 #default=10
  29. connect_timeout = 30
  30. #interactive timeout = 600
  31. max_connection = 350
  32. max_connect_errors = 30
  33. wait_timeout = 30

本文出自 小Q,转载时请注明出处及相应链接。

本文永久链接: http://www.linuxqq.com/archives/746.html

0
更多
Ɣ回顶部